Previous Thread
Next Thread
Print Thread
Rate Thread
SQL advice would be appreciated. :) #285616
06/22/2005 6:08 AM
06/22/2005 6:08 AM
Joined: Feb 2002
Posts: 8
SKB Offline OP
Lurker
SKB  Offline OP
Lurker

Joined: Feb 2002
Posts: 8
We're having a bit of a nightmare. We have three ubb.threads installations on a server. Two are fine, but the big and most trafficed board has a lot of issues.

We get very long query times, sometimes like 10 minutes to generate a page. But mostly the hold ups are about 60 seconds. Very sporadic too. I disabled search and had a period of tranquility, no issues at all. Then bham it did it again.

Sometimes, probably related, we get a ton of sleeping proceses, like it goes up to 600. IO wait shoots up, server load goes up and then apache often wigs out.

I would appreciate it very much if anyone could advise on mySQL settings. I'd like to perfect them as much as possible.

It's a 4 CPU machine. 2GB of RAM. The main board is highly trafficed, generally about 400 - 800 users using it any one time. We do run other sites on the server, so SQL can't be too greedy. But as you can see, it is important in terms of where the server load is lying.

I realize I don't really post here, despite being a member for a long time. I'm concious of being one of those people that comes in, gets what they want then goes. But I'm hoping you guys can help me out none the less. (If I was more compentent, I'd contribute!)

These are the current numbers:

[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
skip-locking
set-variable = key_buffer=640M
set-variable = max_allowed_packet=16M
set-variable = table_cache=1024
set-variable = sort_buffer=16M
set-variable = read_buffer_size=2M
set-variable = record_buffer=6M
set-variable = myisam_sort_buffer_size=128M
set-variable = thread_cache=16
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
set-variable = max_connections=512
set-variable=query_cache_type=1
set-variable=query_cache_size=16M
set-variable=wait_timeout=180
#log-slow-queries = /var/log/slow_queries.log
#long_query_time=10

Sponsored Links

Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with, and recommend, the following Web Hosts:
· Stable Host
· Blue Host
· Interserver.net
Visit us on Facebook
Member Spotlight
badfrog
badfrog
somewhere on the coast of Maine
Posts: 94
Joined: March 2007
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,456
Posts293,497
Members13,800
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 25,587
JoshPet 11,330
Rick 8,373
LK 7,396
Lord Dexter 6,503
Gizmo 5,941
Greg Hard 5,533
Top Posters(30 Days)
isaac 8
Gizmo 3
driv 1
Today's Statistics
Currently Online 908
Topics Created 2
Posts Made 6
Users Online 3
Birthdays 24
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2018 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.6.3
(Snapshot build 20180924.dev)
PHP: 5.4.45 DB: MySQLi Page Time: 0.053s Queries: 14 (0.027s) Memory: 3.2302 MB (Peak: 3.4828 MB) Zlib enabled. Server Time: 2018-09-24 15:11:31 UTC