Making MySQL Use More Memory | Down Home Country Coding With Sco. Unlike a lot of database servers, MySQL is strangely conservative (by default) on how much memory it will allocate. If you’re not careful, you can have 16GB of RAM on your machine with MySQL only using 50MBs, leading to extremely poor performance under heavy load. I know firsthand that navigating MySQL configuration guides can be a daunting task, so I’ve prepared this post for those looking for a ‘quick fix’ to encourage MySQL to use a more healthy amount of memory.
Which database storage engine do you use primarily? Many beginner users may not understand this, but with MySQL you have a choice in which storage engine implementation your database runs on. This is where performance tuning begins to get complicated, as you have to set the configuration variables that correspond to the storage engine you are using! You can see what engine you are relying on by opening MySQL Administrator and viewing your schema under the Catalogs tab. Innodb_buffer_pool_size=200M Again, the default is 8M! Mysql-tools - Google Code. » 10 tips for optimizing mysql queries? - Jaslabs: High performa. MySQL Performance Blog » What to tune in MySQL Server after inst. My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.
I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings. Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right for your initial MySQL performance tuning, other changes will most commonly offer only incremental performance improvements. key_buffer_size – Very important if you use MyISAM tables.
Set up to 30-40% of available memory if you use MyISAM tables exclusively. Innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Table_cache – Opening tables can be expensive. Peter Zaitsev.