How to adjust the performance after installing the MySQL server

MyfavoritequestionduringInterviewforpeopletoworkasMySQLDBAsorbeinvolvedwithMySQLPerformanceinsomewayistoaskthemwhatshouldbetune dinMySQLServerstraightafterinstallation, assumingitwasinstalledwithdefaul

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 shoshould be tuned in MySQL Server straight after installation, assuming it was installed with defaul

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 shoshould be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

When interviewing MySQL DBA or those who intend to optimize MySQL performance, my favorite question is: what adjustments should I make after the MySQL server is installed according to the 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.

I was surprised how many people could not give a reasonable answer to this question, and how many servers were running under the 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 other changes will most commonly offer only incremental performance improvements.

Although you can adjust the variables on many MySQL servers, only a few variables are really important under most common workloads. If you set these variables correctly, modifying other variables can only improve the system performance.

Key_buffer_size-Very important if you use MyISAM tables. set up to 30-40% of available memory if you use MyISAM tables exclusively. right size depends on amount of indexes, data size and workload-remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in container cases. check however if all of key_buffer is used over time-it is not rare to see key_buffer being set to 4G while combined size. MYI files is just 1 GB. this wocould be just a waste. if you use few MyISAM tables you'll want to keep it lower but still at least 16-32 Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

Key_buffer_size-this is very important for the MyISAM table. If you only use the MyISAM table, you can set it to 30-40% of the available memory. A reasonable value depends on the index size, data volume, and load. Remember, the MyISAM Table uses the operating system cache to cache data. Therefore, you need to leave some memory for them, in many cases, the data is much larger than the index. However, it is always necessary to check whether all key_buffer files are used -- The. MYI file only has 1 GB, while the key_buffer is set to 4 GB. This is a waste. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.

Innodb_buffer_pool_size This is very important variable to tune if you're using Innodb tables. innodb tables are much more sensitive to buffer size compared to MyISAM. myISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. same rules as for key_buffer apply-if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

Innodb_buffer_pool_size-this is very important for Innodb tables. Compared with MyISAM tables, Innodb is more sensitive to buffering. MyISAM can run in the default key_buffer_size setting. However, Innodb is similar to snail bait in the default innodb_buffer_pool_size setting. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory. Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.

Innodb_additional_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. still you might want to have it 20 MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

Innodb_additional_pool_size-this option has little impact on performance, at least on an operating system with almost enough memory to allocate. However, if you still want to set it to 20 MB (or larger), you need to check the other Innodb memory to be allocated.

Innodb_log_file_size Very important for write intensive workloads especially for large

Note: More exciting articles, please pay attention to SanlianProgramming TutorialTopic.