mysql innodb_buffer_pool_size let me understand

Question

I couldn't find answer for my situation.

I have a windows server 2008 with 2 cores and 7 GB RAM (Azure D1 VM).

I'm running mariadb 10.0.14 on it. I have 2 databases (1.5GB and 9GB in size).

Statistics are in general:

AVG 1192590 SELECTs per hour (70% of all)
AVG 163555 UPDATEs per hour (10% of all)
AVG 87728 INSERTs per hour (5% of all)
AVG 5920 DELETEs per hour (0.3% of all)

I had innodb_buffer_pool_size set=4G and the whole server was running terribly slow. CPU was utilized avg 10-15% but queries was executed very slowly.

Now I have set innodb_buffer_pool_size=1G and it's running much much faster. The interesting this is that I have commited 1,5 GB RAM by mysqld.exe So 5 GB RAM are still free.

1. Why setting innodb_buffer_pool_size larger means slower queries?
2. What should I configure more to use this free RAM to gain more performance on mysql (mariadb)?

Answer 1

If your mysql is on dedicated server then you can set the buffer pool to the value 4GB. Initially the queries will run little slow because they require disk seeks. As your data set is very small so you can keep most of the data in memory. Once the data is present in memory performance will increase.

As your server is having huge number of reads and less number of writes good buffer pool will give better results. You can allocate around 70% memory to mysql in dedicated setup.

In crease you Key_buffer_size if you have any myisam tables. Increase the value for the below variable to avoid disk based temporary tables.

max_heap_table_size = 64M 
tmp_table_size = 64M

Dont change any on session variables they will consume more memory and may decrease the performance. Below are the session level variables for which defaults works better to most work loads.

sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size

If you data not change frequently then enable query cache.

query_cache_size = 256M
query_cache_type = ON
query_cache_wlock_invalidate = ON ( to get accurate data )
query_cache_limit = 10M

Increasing innodb_log_buffer_size and innodb_log_file_size may help to gain the performance. But be careful if you increase the file size, it will increase the recovery time.

Last but not lease if you are observing performance hit with 4GB buffer pool try setting innodb_buffer_pool_instances=2

give it a try!

Mysql has to fetch the data from the disk and keep it in memory for the first time you have accessed the data. As buffer pool contains both index and data inside it, your query performance will increase.

You can set the log file size to the best value as described HERE.

The best further description is given in rollando.