3

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.

  1. How can I avoid mysql using swap?
  2. Is 3.5GB mysql process memory usage for a 1.6GB database size normal?
  3. 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
shinmac
  • 73
  • 1
  • 2
  • 5
  • Best way to analyze your mysql config is to use tools like mysqltunerl.pl. It is simple perl script download it from web and run it will analyze your config and results you with changes. – Abhishek Anand Amralkar Feb 25 '14 at 18:17
  • possible duplicate of [How do you do load testing and capacity planning for databases?](http://serverfault.com/questions/350458/how-do-you-do-load-testing-and-capacity-planning-for-databases) – Jenny D Feb 27 '14 at 07:31

1 Answers1

1

Most likely culprit are these settings:

tmp_table_size=1768M
max_heap_table_size=1768M

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.

Tero Kilkanen
  • 34,499
  • 3
  • 38
  • 58