How to adjust the performance after the MySQL server is installed

My favorite question during interview for people to work as MySQL DBAs or is involved with MySQL performance in some I s to ask them what should is tuned in MySQL Server straight after installation, assuming it is installed with default set Tings.

When interviewing MySQL DBA or those planning to do MySQL performance optimization, my favorite question is: MySQL server in accordance with the default settings after installation, what should be done to adjust it?

I ' m surprised how many people fail to provide no reasonable answer to this question, and how many servers the Where in WI LD which are running with default settings.

To my surprise, how many people can not give a reasonable answer to this question, and how many servers are running under the default settings.

Even though can tune quite a lot of variables in MySQL Servers only few of them are really for important most W Orkload. After your get this settings right other changes'll most commonly offer only incremental performance improvements.

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

Key_buffer_size-very Important If you use MyISAM tables. Set up to 30-40% of available memory if your 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 n Eed to leave memory for it as, and data can is much larger than indexes in many cases. Check However if all of key_buffer are used over time-it isn't not rare to = Key_buffer being set to 4G while combined siz E of. Myi The files is just 1GB. This would is just a waste. If Use few MyISAM tables you'll want to keep it lower but still at least 16-32MB so it's large to enough indexes for temporary tables which are created on disk.

Key_buffer_size-This is very important for the MyISAM table. If you are using only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load--remember, the MyISAM table uses the operating system's cache to cache the data, so you need to set aside some of the memory for them, and in many cases the data peso is much more. However, it is always necessary to check whether all the Key_buffer are exploited--. Myi files are only 1GB, and Key_buffer is set to 4GB is very rare. It's too wasteful to do so. If you rarely use MyISAM tables, then keep key_buffer_size below 16-32MB to accommodate the temporary table indexes that are given to the disk.

The

Innodb_buffer_pool_size this are very important variable to tune if you ' re using innodb tables. Innodb tables are much more sensitive to the buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it would crawl with default Innodb_buf Fer_pool_size. Also Innodb Buffer pool caches both data and index pages so don't need to leave spaces for OS cache so values up to 70 -80% of memory often make sense to Innodb only installations. Same rules As for key_buffer apply-if you have small data set and it isn't going to grow dramatically does not oversize I Nnodb_buffer_pool_size you might find better use for memory available.

Innodb_buffer_pool_size-This is very important for the InnoDB table. InnoDB is more sensitive to buffering than MyISAM tables. MyISAM can be run under the default Key_buffer_size settings, however InnoDB is like a snail in the default innodb_buffer_pool_size settings. Because InnoDB caches data and indexes without leaving the operating system with too much memory, you can set it up to 70-80% usable memory if you only need to use InnoDB. Some of the rules that apply to Key_buffer are-if your data is small and not exploding, you don't have to set the innodb_buffer_pool_size too big.

Innodb_additional_pool_size This one does not really affect performance more much, in too on OS with least decent Tors. Still you might want to have it 20MB (sometimes larger) so you can have a much memory Innodb a for allocates misc.

Innodb_additional_pool_size-This option does not have much impact on performance, at least on an operating system with almost enough memory to allocate. But if you still want to set it to 20MB (or more), you need to look at the amount of memory other InnoDB need to allocate.

Innodb_log_file_size Very important for write intensive workloads especially for large

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .