MySQL server running out of memory, needs optimizing

tomfra
tomfra used Ask the Experts™
on
I have a cPanel Linux server running on CentOS 5.x and recently received some errors that say:

Cannot select MySQL db: Out of memory

...or similar.

It's obvious I need to assign more memory to MySQL. Could you suggest how I should optimize the following /etc/my.cnf:

[mysqld]
max_connections = 2000
safe-show-database
#log-bin = /var/log/mysql/mysql-bin.log
#binlog-do-db = icloaker_maintenance
server-id=1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 1
key_buffer_size = 128M
table_cache = 4000
sort_buffer_size = 3M
read_buffer_size = 1M
max_allowed_packet = 16M
tmp_table_size = 128M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
thread_cache_size = 16
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
innodb_buffer_pool_size = 128M
#innodb_log_file_size = 64M
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit = 2


[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

--------------
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
check http://www.day32.com/MySQL/ it has a script called "tuning-primer.sh". It does a nice job of
looking at MySQL stats and making suggestions to optimize it.

Author

Commented:
OK, run the script and these seems to be the problems:

MEMORY USAGE
Max Memory Ever Allocated : 354 M
Configured Max Per-thread Buffers : 12.69 G
Configured Max Global Buffers : 309 M
Configured Max Memory Limit : 12.99 G
Physical Memory : 1.92 G

nMax memory limit exceeds 90% of physical memory

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 7 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 24.65 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

-----------

Could you suggest the exact switches I should modify? I am in a hurry right now so do not have much time to play with the db, unfortunately...

Author

Commented:
OK, I think I managed to optimize it somewhat, using the recommended "tuning-primer.sh" script as well as this useful application: http://www.ubuntugeek.com/mysqltuner-check-your-mysql-server-performance.html

Only time will tell if the optimization I did was enough, here is the new my.cnf:

---------
[mysqld]
max_connections = 250
safe-show-database
#log-bin = /var/log/mysql/mysql-bin.log
#binlog-do-db = icloaker_maintenance
server-id=1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 1
key_buffer_size = 128M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 1M
max_allowed_packet = 16M
tmp_table_size = 128M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
thread_cache_size = 16
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
innodb_buffer_pool_size = 128M
#innodb_log_file_size = 64M
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit = 2


[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
---------

Thanks for the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial