Installing a MySQL server is quite easy. However, after installing, configuring the server is an important task for a DBA.
Having 2GB of RAM and 250GB of disk space, how should I tune MySQL server so that it performs well?
There should also be a proper configuration of the InnoDB Engine.
Here are some of my recommendations for InnoDB. In my experience, the buffer pool size is the most important because the more data you can keep in cache, the less time your system will spend using disk IOs.
This is the buffer pool, where data and index are cached
innodb_buffer_pool_size=(In Mb, 80% of available RAM), for 2GB, I would put 1536.
Monitor your system carefully and be prepared to reduce this variable if you start to see swapping.
This is where metadata is stored. If you had a lot of tables, it would be useful to increase this.
Here you can define the location of the ibdata file. If you can put the ibdata file on a separate disk that uses a seperate controller, it will reduce contention. Be careful with this option. Make sure that you read up on this variable before you use it. If your ibdata file needs more than 29G, MySQL will stop whatever its doing and wait for you to add space.
innodb_data_file_path = ibdata1:128M:autoextend:max:29G
This manages in what increments the ibdata file is increased. So if you put a really small number like 10MB, that would mean that innodb would spend a lot of time increasing its ibdata file if you had a lot of inserts.
innodb_autoextend_increment = 128M
where to put the transaction logs. If you can put the transaction logs on a different disk with a separate controller, this will also reduce contention
innodb_log_group_home_dir = /tlog/
What is the size of the log files
innodb_log_file_size = 500M
25% of the buffer pool size, allows large transactions to run without the need to write to the disk
innodb_log_buffer_size = 384M
I also define where the temp directory should go. Defines where MySQL creates temporary files. Yet another way to reduce contention.
I typically leave enough space to allow reindexing of the largest table on the server.
And an error log is useful just in case something goes wrong and you need to trouble shoot.
Also, if you have more than 4 cpus, these variables can be increased
innodb_thread_concurrency = 8
This has nothing to do with innodb. There are other variables you can play with that have nothing to do with innodb. I added them at the bottom, but they were taken directly from a server I use for testing. Avoid just copying and pasting them. Reading MySQL’s documentation based on the version your using is the best way to make the right choices.
# Fine Tunning key_buffer = 512M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 join_buffer_size = 64M sort_buffer_size = 16M myisam_max_sort_file_size = 1024M tmp_table_size = 256M max_heap_table_size = 257M myisam_sort_buffer_size=1024M table_cache = 1000 thread_concurrency = 8 # * Query Cache Configuration # query_cache_limit = 16M query_cache_size = 16M skip-name-resolve