Tweet
We're currently being inundated with email messages with mysql errors reporting "too many connections" error messages for users on our vbulletin board. Right now we have around 120 users constantly online our boards 24/7. This can reach around 250-280 during peak times.
I've optimised our my.cnf file as follows (all recommendations taken from eva2000s MANY posts on this subject
)
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=1000
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=3M
set-variable = record_buffer=3M
set-variable = sort_buffer=4M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=3600
set-variable = connect_timeout=20
[safe_mysqld]
open-files-limit=8192
[myisamchk]
set-variable = key_buffer=384M
set-variable = sort_buffer=384M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
---
<end paste>
We've upped the maximum connections to 1000 because 500 wasn't apparently enough. Yesterday we had a major crash causing a corruption of our database - thankfully fixed once again from reading one of eva2000s earlier posts.
We're using perisistent connections to the database because this seems to reduce high load average from users who use our forums to obtain live scores (click refresh every 10 seconds ARGH I know!) When we wernt using persistent, we had a load average reach 100.0!
We have tried everything we can think of to fix this problem but.... some 3000 email floods later I'm throwing this out to you guys for suggestions.
The server is a 1GHz AMD with 1GB RAM. Its hosting both the database and the PHP files. It also houses a few other relatively low traffic sites.
I've optimised our my.cnf file as follows (all recommendations taken from eva2000s MANY posts on this subject
)[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=1000
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=3M
set-variable = record_buffer=3M
set-variable = sort_buffer=4M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=3600
set-variable = connect_timeout=20
[safe_mysqld]
open-files-limit=8192
[myisamchk]
set-variable = key_buffer=384M
set-variable = sort_buffer=384M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
---
<end paste>
We've upped the maximum connections to 1000 because 500 wasn't apparently enough. Yesterday we had a major crash causing a corruption of our database - thankfully fixed once again from reading one of eva2000s earlier posts.
We're using perisistent connections to the database because this seems to reduce high load average from users who use our forums to obtain live scores (click refresh every 10 seconds ARGH I know!) When we wernt using persistent, we had a load average reach 100.0!
We have tried everything we can think of to fix this problem but.... some 3000 email floods later I'm throwing this out to you guys for suggestions.
The server is a 1GHz AMD with 1GB RAM. Its hosting both the database and the PHP files. It also houses a few other relatively low traffic sites.
Comment