my.cnf and MySQL Tuning

You have an urgent performance case in a MySQL system and you have to get several low hanging fruits fast. What should you do?


MySQL Tuner is Your First Try!
MySQL Tuner is an open source Perl script that analyzes your system and let you know regarding hot spots. You can find a nice example of its results bellow:

-------- Performance Metrics -------------------------------------------------
[--] Total buffers: 34.0M global + 2.6M per thread (1000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.6G (16% of installed RAM)
[OK] Slow queries: 0% (141/99M)
[!!] Highest connection usage: 100%  (1001/1000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/5.0G

my.cnf is the First Answer!
my.cnf, MySQL configuration file is probably the first place to check. In this file you configure the small items that make the difference between a local store webpage to the next hit in the web application. The first place to take a look at is your my.cnf file that is located at /etc/my.cnf.


Where Can I Find my.cnf Examples?
Lucky us, MySQL provides out of the box 5 my.cnf best practices for different machine size. These files are located at /usr/share/doc/mysql-server-5.X.XX/ and in includes: my-small.cnf, my-medium.cnf, my.large.cnf, my-huge.cnf and my-innodb-heavy-4G.cnf. Yet, although the naming, these configurations are targeted to small systems and none will fits even a 16GB RAM machine (How large? take a look at the comment in the head of my-huge.cnf: "This is for a large system with memory of 1G-2G").


So What Should I Do?
Go over the sample configurations and analyze what you need to optimize to meet your actual needs. Don't forget to check your current system configuration using SHOW VARIABLES before getting into the cold water. Some of the following hints will help you meet your target:

1. key-buffer: 
1. Check using SHOW GLOBAL STATUS that the key_reads/key_read_requests ratio is less than 0.01 to avoid disk fetch.
2. If you fail to meet this ratio, enlarge the key-buffer parameter.
2. sort_buffer_size: 128-512MB, will be allocated for sort by each thread.
3. read_buffer_size: 8M, will be allocated for sequential scan by each thread.  
4. write_buffer_size: 8M, will be allocated for plain index scans by each thread.
5. thread_concurrency: should be equal to 2XAvailable Cores.

What Next?

Well it's time to scan the SQL statements that takes too long:

1. Enable the slow query log in the my.cnf: log-slow-queries=/home/mysqld-slow.log 
2. Set the minimum processing time to be included in the log: long_query_time = 1
3. Analyze the results: tail -n 1000 /home/mysqld-slow.log
4. Verify there is no "connection leakage": there are not too many open connections in the database. Check it using SHOW PROCESSLIST;
5. Analyze locking using SHOW STATUS LIKE 'Table%'; and SHOW ENGINE INNODB STATUS if you use InnoDB as your database engine.

Keep Performing,

Moshe Kaplan

How to Replicate Part of the Tables in MySQL

When you build a DWH or another intensive processing on a database slave copy, you may not want to replicate the whole database. 


How can you replicate only some of the tables or even part of existing tables to a slave?
In General there are 4 ways to replicate data:

1. Database Definition: You can define what database names will or won't be replicated. You can place all the tables that need to be replicated in one database and all the other in another one. That way you can replicate only the first database.
2. Tables Definition: You can define what specific table names you can replicate and what not.
3. Part of a Table: Replication by SELECT limitation is supported starting from version 5.1.21 instantly. Therefore, you can easily replicate only several columns (replication discards non existing columns if they do not exist in the slave). In earlier versions, you could perform Vertical Sharding, where you could keep some of the columns in one table, and the other in another table. Then you may replicate only one of the tables.
4. Part of a Table: Replication by WHERE limitation is not supported instantly. Therefore, you cannot easily replicate only some of the rows from a single table. Yet, you can perform Horizontal Sharding, where you can keep rows groups in different tables. Then you may replicate only some of the tables to your slave.

When Should You Avoid Partial Replication?

If your slave is being used as a copy for DRP or HA, you may still want it to fully match your master. If the copy is done for specific processing and you want to avoid unneeded replication, this is definitely the right tool for you.

Syntax is the King

Most of the syntax is performed in the slave and it is detailed in Sunny Walia post.

Keep Performing,

Moshe Kaplan