I have a 1.6GB database that adds about 200MB of size every month. I have a 12GB RAM(shared with apache, mysql and os), mysql always uses swap that i need to restart the service once it reached the swap space limit. The typical memory usage of mysql process is 3.5GB and increases to 4.5GB when there's alot of activity on my web server.
- How can I avoid mysql using swap?
- Is 3.5GB mysql process memory usage for a 1.6GB database size normal?
- My mysql config looks like this, are my configuration optimize?
[mysqld] innodb_file_per_table datadir=/var/lib/mysql tmpdir=/var/lib/mysqltmp socket=/var/lib/mysql/mysql.sock skip-locking skip-name-resolve table_cache=3072 thread_cache_size=16 back_log=100 max_connect_errors=10000 open-files-limit=20000 interactive_timeout=30 wait_timeout=40 max_connections=100 skip-name-resolve max_allowed_packet=16M tmp_table_size=1768M max_heap_table_size=1768M query_cache_size=64M sort_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=8M join_buffer_size=32M key_buffer_size=64M myisam_sort_buffer_size=64M innodb_log_file_size=100M innodb_buffer_pool_size=1512M
Best Answer
Most likely culprit are these settings:
These mean that every individual temporary table created by a query on your website could take up to 1768 megabytes of memory, or 1.7 GB.
I would decrease these to 64M. This will decrease memory usage, but it could also cause MySQL to write temporary table data for queries to disk, which would slow things down.
If MySQL uses disk a lot for temporary tables, you need to analyze your application and find out where the queries are that generate large temporary tables, and then refactor the code so that large temporary tables are not required.