MySQL Optimization

There is more to "installing" a service, a world beyond, similar to the other side of this world - paranormal. Optimization falls under the following categories:

• Code Optimization (Algorithmic and Architecture Specific)
• Compiler Optimization
• Architecture Specific Optimization
• System Optimization

A good resource on System Optimization/Tuning: http://people.redhat.com/alikins/system_tuning.html

You may read this post on Optimization (not really, will post a real one next week to next to it) if:

• You spent 4+ good years in a CS programme
  
• You have a Linux server that does not run an Xserver

Lets cover "System Optimization" with Linux and MySQL as a case:

Operating and Application (user-space) System may be optimized or tweaked or tuned (I prefer to say Optimized) to achieve optimum performance and higher fault-tolerance.

Optimizations to MySql Database engine:
Parameters modified in the configuration file "/etc/my.cnf".

Information on MySQL Server Variables for Optimization can be obtained from the
following URL:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Lets modify few default parameters assuming a high traffic site and a costly DELL/IBM server that you bought for donation to scrap next year, keywords from hell (Virtualization, 10Gbps, Windows Server 20XX, GUI/Xwin on a Linux Server):

Can you please turn-off bluetooth, sendmail, hollywood, and disney-land services before we go any further? seriously they are not required.

Increase Buffer Size:

key_buffer_size = 8388600 (8MB) to 268435456 (256MB)
read_buffer_size = 131072 (128KB) to 1048576 (1MB)
read_rnd_buffer_size = 262144 (256KB) to 2097152 (2MB)
sort_buffer_size = 2097144 (2MB) to 20971520 (20MB)
net_buffer_length = 16384 (16KB) to 2097152 (2MB)
table_cache = 64 to 512
max_allowed_packet = 1048576 (1MB) to 16777216 (16MB)

Process Scheduling: How about high-priority/real-time scheduling? Read:

• http://oreilly.com/catalog/linuxkernel/chapter/ch10.html
• http://www.redhat.com/magazine/008jun05/features/schedulers/

Original/Default Priority set to 78 (low -see ps output below):

[root@mysql ~]# ps -elf|grep mysql
4 S root 12858 1 0 85 0 - 16480 wait May11 pts/0 00:00:00 /bin/
sh /usr/bin/mysqld_safe --datadir=/nfs/mysql-db
--socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pidfile=/
var/run/mysqld/mysqld.pid
4 S mysql 12924 12858 0 78 0 - 112545 stext May11 pts/0 00:00:05 /usr/
libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db --user=mysql --pidfile=/
var/run/mysqld/mysqld.pid --skip-external-locking --port=3306
--socket=/var/lib/mysql/mysql.sock
0 R root 23152 7328 0 78 0 - 15286 -10:00 pts/1 00:00:00 grep
mysql

[root@mysql ~]# ps aux|grep mysql
root 12858 0.0 0.0 65920 1272 pts/0 S May11 0:00 /bin/sh
/usr/bin/mysqld_safe --datadir=/nfs/mysql-db --socket=/var/lib/mysql/mysql.sock
--log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
mysql 12924 0.0 3.4 450180 71988 pts/0 Sl May11 0:05
/usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db --user=mysql --pidfile=/
var/run/mysqld/mysqld.pid --skip-external-locking --port=3306
--socket=/var/lib/mysql/mysql.sock
root 25194 0.0 0.0 61144 720 pts/1 R+ 11:06 0:00 grep mysql
[root@mysql ~]# chrt -v -p 12924
pid 12924's current scheduling policy: SCHED_OTHER
pid 12924's current scheduling priority: 0
[root@mysql ~]# chrt -v -p 12858
pid 12858's current scheduling policy: SCHED_OTHER
pid 12858's current scheduling priority: 0

Verify the RT Priority for mysqld process:

[root@mysql ~]# ps -C mysqld -To pid,rtprio,cmd

PID RTPRIO CMD
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33

Process (PID 12924) is not using a Real-Time scheduling Policy.

We change the Scheduling Policy for mysqld to Real-Time (99 is the
highest):

[root@mysql ~]# chrt -p 85 12924
[root@mysql ~]# ps -C mysqld -To pid,rtprio,cmd

PID RTPRIO CMD
12924 85 /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33
12924 - /usr/libexec/mysqld --basedir=/usr --datadir=/nfs/mysql-db
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--port=33

[root@mysql ~]# top -p 12924
top - 11:17:16 up 3 days, 21:06, 2 users, load average: 0.00, 0.00, 0.00
Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2059768k total, 862920k used, 1196848k free, 284124k buffers
Swap: 4192924k total, 0k used, 4192924k free, 412272k cached


PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12924 mysql -86 0 439m 70m 4288 S 0.0 3.5 0:05.32 mysqld


Please do not ask what distributaion am I running, Linux is just Linux! if you move from Redhat to Debian and unable to find a run control script, you shouldn't be on Linux.