MySQL Performance Tuning Settings

>Binary logging however is not free, so if you do not need for instance on a replica that is not a master, it is recommended to keep it disabled.

I think binary logging is required for point-in-time recovery, this type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made.

innodb-purge-threads – This option variable controls purge function, In versions prior to 5.5 that was part of the responsibility of the InnoDB master thread. A high load on the server that dirtied a lot of pages would force the master thread to spend most of its time flushing and therefore no purging would get done and vice a versa.

A quibble: “For instance, innodb_buffer_pool_size should be set in MB”. That setting is in bytes, not “MB”, although you can use a suffix of M (or G).

It would be interesting to know more about the query cache being a performance bottleneck – in what situations?

I performed recomendation above but I see slow “Copying to tmp table” (tmpdir on RAM device). I tried to increase join_buffer_size but problem still the same. Can expedite the processing of such requests without changing the sql requests (unfortunately at the moment there is no possibility to optimize queries)?

MYSQL server 5.5.34 on zfs filesystem.
> show global status like ‘Created_tmp%’;
+————————-+——–+
| Variable_name | Value |
+————————-+——–+
| Created_tmp_disk_tables | 305388 |
| Created_tmp_files | 7944 |
| Created_tmp_tables | 590596 |
+————————-+——–+
3 rows in set (0.00 sec)

Example:
> SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, GREATEST(n.changed, l.last_comment_timestamp) AS last_updated, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 1 AND (n.uid = 28178 OR c.uid = 28178) ORDER BY last_updated DESC LIMIT 0, 25;

rows in set (5.42 sec)

> show profile for query 8;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| starting | 0.000027 |
| Waiting for query cache lock | 0.000005 |
| checking query cache for query | 0.000114 |
| checking permissions | 0.000006 |
| checking permissions | 0.000003 |
| checking permissions | 0.000003 |
| checking permissions | 0.000007 |
| Opening tables | 0.000035 |
| System lock | 0.000014 |
| Waiting for query cache lock | 0.000038 |
| init | 0.000044 |
| optimizing | 0.000035 |
| statistics | 0.000122 |
| preparing | 0.000030 |
| Creating tmp table | 0.000039 |
| executing | 0.000005 |
| Copying to tmp table | 5.419415 |
| Sorting result | 0.000056 |
| Sending data | 0.000037 |
| end | 0.000006 |
| removing tmp table | 0.000050 |
| end | 0.000006 |
| query end | 0.000022 |
| closing tables | 0.000020 |
| freeing items | 0.000041 |
| logging slow query | 0.000004 |
| logging slow query | 0.000004 |
| cleaning up | 0.000009 |
+——————————–+———-+
28 rows in set (0.00 sec)

> explain SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, GREATEST(n.changed, l.last_comment_timestamp) AS last_updated, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 1 AND (n.uid = 28178 OR c.uid = 28178) ORDER BY last_updated DESC LIMIT 0, 25;
+—-+————-+——-+——–+—————————————–+———+———+————-+——–+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————————————–+———+———+————-+——–+———————————+
| 1 | SIMPLE | n | ref | PRIMARY,status,uid,node_status_type,nid | status | 4 | const | 403897 | Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.n.uid | 1 | Using where |
| 1 | SIMPLE | c | ref | lid | lid | 4 | xxxx.n.nid | 2 | Using where |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.n.nid | 1 | |
+—-+————-+——-+——–+—————————————–+———+———+————-+——–+———————————+

mysql config:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1024M
table_open_cache = 256
sort_buffer_size = 3M
read_buffer_size = 3M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 512M
query_cache_limit = 16M
tmp_table_size = 512M
max_heap_table_size = 512M
max_connections = 100
back-log = 20
thread_cache=256
join_buffer_size=128M
key_buffer = 512M
query_cache_type = 1
skip-innodb_doublewrite
innodb-doublewrite = FALSE
long_query_time = 5
slow-query-log = 0
slow_query_log_file = /var/log/mysql/mysqld_slow_query.log
thread_concurrency = 12
skip-name-resolve
server-id = 2
auto_increment_increment=2
auto_increment_offset=2
log-bin=x_mysql-bin
expire_logs_days=7
binlog_format=mixed
slave-compressed = 1
relay-log=x_slave-relay-bin
relay-log-index=x_slave-relay-bin.index
innodb=on
innodb_data_home_dir = /var/db/mysql/innodb
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/mysql/iblogs
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 9
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 1500M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency=16
tmpdir=/tmp
[mysqldump]
quick
max_allowed_packet = 1024M
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout

ZFS properties for /var/db/mysql/data:
recordsize 8K
atime off
primarycache all

for /var/db/mysql/innodb:
recordsize 16K
atime off
primarycache metadata

for /var/db/mysql/iblogs:
recordsize 128K
atime off
primarycache all

Thanks

Nice one.

I am a complete novice with mysql and am attempting to tune our WordPress DB. As suggested, I am doing one thing at a time. Here is our config:
CentOS 6.4, 4GB (virtual server)
MySQL 5.1.73

innodb status showed the following before starting to make changes:
BUFFER POOL AND MEMORY
———————-
Total memory allocated 20365640; in additional pool allocated 818176
Dictionary memory allocated 85192
Buffer pool size 512
Free buffers 476
Database pages 35
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 35, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout

The original my.cnf file looked like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I added the following line:
innodb_buffer_pool_size = 2000M

Now innodb status shows the following:
BUFFER POOL AND MEMORY
———————-
Total memory allocated 20358952; in additional pool allocated 653824
Dictionary memory allocated 33320
Buffer pool size 512
Free buffers 491
Database pages 21
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 21, created 0, written 0
0.14 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 950 / 1000

It doesn’t look like my.cnf changes are taking place. Can you tell me what I am doing wrong?

I restarted the server, no love.

$ uptime
18:18:24 up 22 min, 2 users, load average: 0.02, 0.01, 0.00
$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# added innodb_buffer_pool_size below on 1/12/15
innodb_buffer_pool_size = 2000M

mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
150113 18:20:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 4, signal count 4
Mutex spin waits 0, rounds 20, OS waits 1
RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 1
————
TRANSACTIONS
————
Trx id counter 0 7936
Purge done for trx’s n:o < 0 4836 undo n:o

The total memory here is listed in bytes, correct?
Total memory allocated 20358952; in additional pool allocated 653824

Hello;

Thanks for this tutorial, it worked for me

Thanks for this tutorial. Nice one.

I have enabled HugeTLB on MySQL server. I have modified SHMAX, SHMALL , vm.nr.pages,vm.hugetlb_shm_group in sysctl file and every works fine. But after I’m inspecting the status of TOP command it is showing Mysql RES memory below 3G and VRTS showing 44G. innodb_buffer_pool size was set to 38G. My question is why MySQL RES showing 3G even though we have set to reserve 38G Physical memory to mysql.
Do you have any idea about RES value showing very low

I have a column in my table which keeps on changing very frequently .How should i implement caching on that particular table ,should i move this column to different table ? How should i avoid caching invalidation ?

Hello, Stephane!
Good post, thanks for that. I want to translate it to russian language. Please let me know if you have any objection.

nice one as part of first-cut performance design with the following:
1. data modelling for the workload (OLTP vs DSS vs combination0
2. table spaces, database creation time
3. redo log and data files organization (mounting)
4. choosing right table features, types (heap organized vs partioned vs IOT etc.,
5. indexes – first-cut ones
7. writing good queries for correctness and performance
8. measure it with right workload (representative of production)

and probably much more