Recently I updated my VPS from 1GB to 4GB memory. I'd hoped that the queries (MYSQL/InnoDB) were running faster with more memory, but unfortunately that's not the case. Does mysql automatically takes more memory when a server has more memory or do I have to change some settings in my.cnf? And if so, what changes should I make?
1 Answer
MySQL will not automatically take the benefit of more memory installed.
In your case (given that you are using InnoDB) you can do at least these to improve the performance of mysql:
- increase
innodb_buffer_pool_size(default value for this option is 128MB). This defines how much memory is dedicated to mysql innodb to cache its data tables and idexes. Which means if you can allocate more memory mysql will cache more of its data resulting in faster queries (because mysql will look in memory instead of doing I/O operations for data lookup). Of course you should allocate reasonable amount of memory (not the whole 4G :)) may be not more than 2G. You should try and test it on the server for more accurate result. (read this for more info, before you change this option https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html) - increase
innodb_buffer_pool_instances. For you case may be 1 or 2 instances are more than enough. (you can read more here: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances)
But before starting with editing of my.ini do your calculations for your case. Consider your mysql server load, slow queries etc. for more accurate setup of the options in my.ini
EXPLAIN ANALYZEand some information about table size, index, current time performance, desire time, etc.Slowis a relative term and we need a real value to compare. MySQL