Understanding and configuring Percona server 5.5 with high server performance

Jephe Wu - http://linuxtechres.blogspot.com

Objective: understanding and configuring Percona server 5.5 with high server performance
Environment: CentOS 6.3 64bit, Percona server 5.5


Part I: the most important parameters for tuning mysql server performance

•   innodb_buffer_pool_size
•   innodb_log_file_sized

1. for innodb_buffer_pool_size

size it properly:
-----------------

You probably should set it at least 80% of your total memory the server can see.
Actually, if your server is dedicated mysql server, without nightly backup job scheduled, you might want to set it in such way it only left 4G to

Linux OS.

Monitoring for usage:
--------------------

Innodb_buffer_pool_pages_total - The total size of the buffer pool, in pages.

Innodb_buffer_pool_pages_dirty/ Innodb_buffer_pool_pages_total  - the percentage of dirty pages

Innodb_buffer_pool_pages_data - The number of pages containing data (dirty or clean).

buffer pool page size:  - always 16k per page
---------------------
[root@db03 ~]# echo "20000*1024/16" |bc
1280000
[root@db03 ~]# grep innodb_buffer_pool_size /etc/my.cnf
innodb_buffer_pool_size=20000M
---------------------


When innodb flush pages to disk:
-----------------------------

a. LRU list to flush
If there's no free pages to hold the data read from disk, innodb will LRU list to flush least recently used page.

b. Fust list
used if the percentage of dirty pages reach innodb_max_dirty_pages_pct, innodb will write pages from buffer pool memory to disk.

c. checkpoint acitivity
When innodb log file circles, it must make sure the coresponding dirty pages have been flushed to disk already before overwritting log file content.

Refer to http://www.mysqlperformanceblog.com/2011/01/13/different-flavors-of-innodb-flushing/

Monitoring flush pages:

[root@db04 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Innodb_buffer_pool_pages
Innodb_buffer_pool_pages_data 919597
Innodb_buffer_pool_pages_dirty 6686
Innodb_buffer_pool_pages_flushed 115285384
Innodb_buffer_pool_pages_LRU_flushed 0
Innodb_buffer_pool_pages_free 4321306
Innodb_buffer_pool_pages_made_not_young 0
Innodb_buffer_pool_pages_made_young 11000
Innodb_buffer_pool_pages_misc 1976
Innodb_buffer_pool_pages_old 339440
Innodb_buffer_pool_pages_total 5242879


How innodb flush dirty pages to disk:
------------------------------------
Innodb uses background thread to merge writes together to make it as sequential write, so it can improve performance.
It's called lazy flush. Another flush is called 'furious flushing' which means it has to make sure dirty pages have been written to disk before

overwriting transaction log files. So large innodb log file size will improve performance because innodb doesn't have to write dirty page more often.


2. For innodb_log_file_size

Before writing to innodb log file, it uses innodb_log_buffer_size, range is 1M-8M, don't have to be very big unless you write a lot of huge blob records.
the log entries are not page-based. Innodb will write buffer content to log file when transaction commits.

How to size it properly, a few ways below:
-----------------------
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
1) use method mentioned in above blog
mysql> pager grep sequence
show engine innodb status\G

--------------

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\G select sleep(60)\Gshow engine innodb status\G
Log sequence number 9591605145344
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 9591617510628
1 row in set (0.00 sec)

mysql> select (9591617510628-9591605145344)/1024/1024 as MB_per_min;
+-------------+
| MB_per_min  |
+-------------+
| 11.79245377 |
+-------------+
1 row in set (0.00 sec)

mysql> select 11.79245377*60/2 as MB_per_hour for each file in group.
+------------------+
| MB_per_hour      |
+------------------+
| 353.773613100000 |
+------------------+
1 row in set (0.00 sec)

------------------

2) use innodb_os_log_written - The number of bytes written to the log file
You can monitor this parameter for 10 seconds during peak hour time, then get value X kb/s.
use x * 1800s(half an hour for one of two log files) for log file size.

3) monitor actual file size modified time, make it at least half an hour for each log file modification time.


Part II - Other useful parameters in /etc/my.cnf for innodb

1. innodb_max_dirty_pages_pct

This is an integer in the range from 0 to 99. The default value is 75. The main thread in InnoDB tries to write pages from the buffer pool so that the

percentage of dirty (not yet written) pages will not exceed this value.

2. innodb_flush_method=O_DIRECT  - eliminate Double Buffering

3. default-storage-engine=innodb

4. innodb_file_per_table

5. innodb_flush_log_at_trx_commit=2

Value Meaning
0 Write to the log and flush to disk once per second
1 Write to the log and flush to disk at each commit
2 Write to the log at each commit, but flush to disk only once per second

Note that if you do not set the value to 1, InnoDB does not guarantee ACID prop-
erties; up to about a second’s worth of the most recent transactions may be lost if a
crash occurs.

6. max_connections=512

7. max_connect_errors
# default it's 10 only, we will get error like Host X is blocked because of many connection errors; unblock with mysqladmin flush-hosts
max_connect_errors=5000
# end

Part III - monitoring mysql server performance

1. aborted_client / abort_connects

2. bytes_sent/bytes_received , the number of bytes received/sent from/to all client

3. connections , the number of connection attempts (success or not ) to mysql server

4. created_tmp_disk_tables/created_tmp_tables , created on-disk temporary tables / the total number of created temporary tables

5. innodb_log_waits - The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
mysql -uroot -ppassword -e 'show global status' | grep -i  Innodb_log_waits


6.  Innodb_os_log_written

The number of bytes written to the log file.

7.  Innodb_rows_deleted

The number of rows deleted from InnoDB tables.

 Innodb_rows_inserted

The number of rows inserted into InnoDB tables.

 Innodb_rows_read

The number of rows read from InnoDB tables.

 Innodb_rows_updated

The number of rows updated in InnoDB tables.

8. Max_used_connections - The maximum number of connections that have been in use simultaneously since the server started.

[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Max_used_connections
Max_used_connections 353

9.  Open_tables

The number of tables that are open.

10.  Select_full_join

The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your

tables.

11. Select_range_check - The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the

indexes of your tables.

[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Select_range_check
Select_range_check 1453


12. slow_queries
[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Slow_queries    
Slow_queries 38180440

13. [root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i  Table_locks_waited
Table_locks_waited 304

The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance

problems, you should first optimize your queries, and then either split your table or tables or use replication.


14. thread_connected/thread_running/max_used_connection
 Max_used_connections

The maximum number of connections that have been in use simultaneously since the server started.

 Threads_running

The number of threads that are not sleeping.

 Threads_connected

The number of currently open connections.