Mysql server The way to adjust performance after default installation _mysql

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 important for MyISAM tables. 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 too much, at least on OS with decent memory. 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 data sets. Larger sizes offer better performance but increase times and so is recovery. I normally use values 64m-512m depending on server size.

Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that recovery time may be increased. I often set it to 64-512MB, which varies according to the size of the server.

innodb_log_buffer_size Default to this one is kind the OK for many workloads with medium write load and shorter transactions. If you have update the activity spikes however or work with BLOBs a lot your might to want it. Don't set it too high however as it would being waste of memory-it is flushed every 1 sec anyway so don't need For more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

The

innodb_log_buffer_size default setting is available for server performance in the case of a medium-intensity write load and a shorter transaction. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, it may waste memory-it refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the smaller its value.

Innodb_flush_logs_at_trx_commit crying about InnoDB being times slower than? You are probably forgot to adjust this value. Default value of 1 'll mean each update transaction commit (or each statement outside of transaction) would need to flush Log to the disk which are rather expensive, especially if you don't have Battery up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which does not means log to the disk But only flush it to OS cache. The log is still flushed to the disk, each of which second so and you normally would not loose more than 1-2 sec worth of updates. The Value 0 is a bit faster but are a bit less secure as can lose transactions even in case MySQL Server crashes. Value 2 only cause the data loss with the full OS crash.

innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head is big? It seems that you have forgotten to revise this parameter. The default value is 1, which means that each submitted update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource-intensive, especially when there is no battery-standby cache. Many applications, especially those from MyISAM, set the value to 2, which means that the log is not flushed to disk, but only to the operating system's cache. Logs are still flushed to disk per second, so there is usually no loss of 1-2 updates per second. If set to 0 is much faster, but also relatively insecure-the MySQL server crashes will lose some transactions. Set to 2 command to lose that part of the transaction that is flushed to the operating system cache.

table_cache -Opening tables can be expensive. For example MyISAM tables mark Myi headers to mark table as currently. I/happen so frequently and it are typically best to size your cache so it are large enough to keep MOS T of your tables open. It uses some OS resources and some memory but to modern the it is hardware to the not the typically. 1024 is good value for applications with couple hundreds tables (remember each connection needs it own entry) if you have Many connections or many tables increase it larger. I ' ve seen values over 100.000 used.

Table_cache -the overhead of opening a table can be significant. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to be too frequent, so it's usually a lot more cache, so it's enough to cache open tables. It needs the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table), and if the number of connections is larger then increase its value. I have seen the case set to 100,000.

Thread_cache Thread creation/destructions can be expensive, which happen in each connect/disconnect. I normally set this value to at least 16. IF application has large jumps in amount to concurrent connections and I = fast growth of
threads_created variable I boost it higher. The goal is isn't to have threads created in normal operation.

Thread_cache -The cost of creating and destroying threads can be significant because each thread needs to be connected/disconnected. I'm usually at least set to 16. If there is a large number of jumps and concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is designed to not create a new thread in the usual operation.

Query_cache The If your application is read intensive and your do not have application level caches the can is great help. Do not set it too large as it could slow things down as its maintenance could get expensive. Values from 32M to 512M normally make sense. Check it however after the a while and the if it is the used. For certain workloads cache hit ratio is lower than has it enabled.

Query_cache -This is useful if your application has a large number of reads and no application-level caching. Don't set it too large, because it costs a lot to maintain it, which can cause MySQL to slow down. usually set to 32-512MB. After setting up, it's best to track for a while to see if it's working well. Under certain load pressure, if the cache hit rate is too low, enable it.

Note: As and you can be are global variables. These variables depend in hardware and mix of storage engines, while each session variables are typically workload . If you are have simple queries there are no reason to increase sort_buffer_size Even if your have 64GB of memory to was Te. Furthermore doing so may decrease performance.
I normally leave per sessions variable tuning to second step after I can analyze workload.

Note: as you can see, these global tables are different depending on the hardware configuration and different storage engines, but the session variables are usually set based on different workloads. If you have only a few simple queries, there is no need to increase the value of sort_buffer_size , although you have 64GB of memory. Bad luck may degrade performance.
I usually set the session variables after analyzing the system load.

P.S Note MySQL distribution contains bunch of sample my.cnf files which the May is great templates to use. Typically they would already is much better than defaults if you chose one.

The P.s,mysql distribution already contains a variety of my.cnf sample files, which can be used as configuration templates. This is usually a lot better than the default settings you use.