Performance problem on Linux

MySQL & MariaDB Other MySQL® Questions
1

Hi
I have a website running PHP + MySQL (Percona-Server-server-55-5.5.15-rel21.0.158.rhel6.x86_64)
Today I noticed high %system load on my server caused by mysqld. Database table format is MyISAM. Database files are located on SSD.

Config:

[mysqld]user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplanguage = /usr/share/mysql/englishskip-external-lockingbind-address = 127.0.0.1key_buffer = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size = 8myisam-recover = BACKUPmax_connections = 100table_cache =256max_heap_table_size = 16Mthread_cache_size = 4query_cache_size = 20Mtmp_table_size = 64Mkey_buffer_size = 4Mbulk_insert_buffer_size = 4Mmyisam_sort_buffer_size = 4Mmyisam_max_sort_file_size = 2Gmyisam_repair_threads = 1myisam_recoverlog-slow-queries=/var/log/mysql/slow.loglong_query_time = 3server-id = 1expire_logs_days = 10max_binlog_size = 100M

strace output:

strace -c -f -p 22283Process 22283 attached with 23 threads - interrupt to quitProcess 23102 attached (waiting for parent)Process 23102 resumed (parent 22283 ready)Process 23103 attached (waiting for parent)Process 23103 resumed (parent 22283 ready)Process 23104 attached (waiting for parent)Process 23104 resumed (parent 22283 ready)Process 23105 attached (waiting for parent)Process 23105 resumed (parent 22283 ready)…% time seconds usecs/call calls errors syscall------ ----------- ----------- --------- --------- ---------------- 42.65 45.960006 127667 360 io_getevents 29.19 31.456203 1571 20021 4525 futex 16.69 17.983456 203 88735 read 3.97 4.280503 30358 141 poll 3.69 3.973396 993349 4 select 1.50 1.614578 21 75963 pread 1.25 1.348565 19 71479 lseek 0.43 0.459066 25 18151 102 setsockopt 0.27 0.286388 32 9000 write 0.21 0.222788 26 8498 time 0.11 0.115981 28995 4 3 restart_syscall 0.02 0.020877 29 710 fcntl 0.01 0.006529 58 112 lstat 0.01 0.005446 32 170 28 access 0.01 0.005407 38 142 getsockname 0.00 0.004816 20 236 pwrite 0.00 0.004772 34 142 accept 0.00 0.004429 40 112 close 0.00 0.004234 38 111 shutdown 0.00 0.001858 46 40 set_robust_list 0.00 0.000866 31 28 getcwd 0.00 0.000838 28 30 open 0.00 0.000754 19 40 clone 0.00 0.000404 25 16 mprotect 0.00 0.000000 0 17 madvise 0.00 0.000000 0 40 getpeername 0.00 0.000000 0 2 2 readlink------ ----------- ----------- --------- --------- ----------------100.00 107.762160 294304 4660 total

How can I find why mysql spends so much time on kernel syscalls any where is the problem.

Thanks.

2

Remove key_buffer from your configuration, it’s an old name for key_buffer_size.
And increase the key_buffer_size a lot, since I’m guessing that you don’t have only 20MB RAM on that machine.
The rough recommendation is to set key_buffer_size to about 25% of available RAM on the machine. So if you are running a web-server on the same machine then you have to deduct the memory usage of it from available RAM on the server.

Another suggestion is that you lower the long_query_time (you can set fractions of a second) so that more slow queries turn up in the slow query log. Also set the:
log-queries-not-using-indexes variable to on so that you can get non optimized queries in the log regardless of execution time.

Quote:

3

First of all I want to thank you for your help!
I’ve done changes you suggested, but I can still observe high (15 to 30) %system in iostat output. What could it be? Which system calls causes mysql to consume so much time in kernel calls?