mysql using 100% cpu on one core

Question

I have a mysql 5.1.61 on a Linux Production server with kernel 2.6.32-131.0.15.el6.x86_64 having 2 CPU. However i find that a single select query makes the cpu go 100% on only one core.

Eg of select command (I've replaced the columns by xxx and data as yy for security reasons):

select count(*)
 from xx
 where xxx = '12'
 and xxxxx like 'df'
 and xx like 'yyy'
 and xxxx like 'yyyy'
 and xxxx like '%'
 and xxx=0
 and xxx like '%server%'
 and xxx = 'yyyy'
 and xxx like '%yyyy%'
 and xxx like '%yyy%' 

As seen in the picture the following request is using only one CPU at 100% and the memory and swap consumption is stable enter image description here

 total used free shared buffers cached
Mem: 3834 3673 160 0 55 2439
-/+ buffers/cache: 1178 2655
Swap: 2046 96 1950

The database size is of 25Gb and the innodb_thread_concurrency is at 16 more than my CPU cores.

mysql -e "show variables;" |grep -i innodb
have_innodb YES
ignore_builtin_innodb OFF
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_size 134217728
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table ON
innodb_flush_log_at_trx_commit 0
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 15
innodb_locks_unsafe_for_binlog OFF
innodb_log_buffer_size 8388608
innodb_log_file_size 67108864
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 16
innodb_thread_sleep_delay 10000
innodb_use_legacy_cardinality_algorithm ON

Below is an extract of an strace of the mysql PID:

 Process 3822 attached - interrupt to quit
 select(14, [12 13], NULL, NULL, NULL) = 1 (in [13])
 fcntl(13, F_SETFL, O_RDWR|O_NONBLOCK) = 0
 accept(13, {sa_family=AF_FILE, NULL}, [2]) = 555
 fcntl(13, F_SETFL, O_RDWR) = 0
 getsockname(555, {sa_family=AF_FILE, path="/var/run/lbn-d1円"}, [43]) = 0
 fcntl(555, F_SETFL, O_RDONLY) = 0
 fcntl(555, F_GETFL) = 0x2 (flags O_RDWR)
 fcntl(555, F_SETFL, O_RDWR|O_NONBLOCK) = 0
 setsockopt(555, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
 futex(0xd385a4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xd385a0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
 futex(0xd379e0, FUTEX_WAKE_PRIVATE, 1) = 1
 strace -cp 3822
 Process 3822 attached - interrupt to quit
Process 3822 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
 99.95 0.047993 6856 7 select
 0.05 0.000025 2 14 futex
 0.00 0.000000 0 7 accept
 0.00 0.000000 0 7 getsockname
 0.00 0.000000 0 10 4 setsockopt
 0.00 0.000000 0 35 fcntl
------ ----------- ----------- --------- --------- ----------------
100.00 0.048018 80 4 total

For info the queries are being logged as slow queries

mysql -e "show variables like 'thread_h%';"
+-----------------+---------------------------+
| Variable_name | Value |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+

Is there anything can be done to allow the cpu consumption to be evenly distributed to both core instead of utilizing only one core?

********** UPDATE ***************

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3939 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql -e "show variables;" |grep myisam
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036853727232
myisam_mmap_size 18446744073709551615
myisam_recover_options BACKUP,FORCE
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
myisam_use_mmap OFF

Infact the tables are of myisam engine and not Innodb and the select is being done from a View.

Answer 1

Almost always 100% CPU can be traced to a query that needs an index and/or needs to be rewritten.

It's hard to help you without explicit field names.

Have a composite index with these fields in

 where xxx = '12'
 and xxxxx like 'df'
 and xxx=0
 and xx like 'yyy'
 and xxxx like 'yyyy'
 and xxx = 'yyyy'

The order is important; start with any columns that are always used in WHERE and are tested with '='.

More on optimal indexes.

Do you need LIKE '%...' -- that construct cannot make use of an index.

• Kheshav Sewnundun: Thank you indexes indeed helped to reduce the cpu usage by the query :)

Answer 2

First, what on the surface:

innodb_buffer_pool_size 134 217 728, 134Mb for 25Gb database it is exactly too low

from the screenshot we can see MySQL use only 15% of server memory, for InnoDB 50-75% of server memory - good choice

Than need work with processes inside MySQL - what queries You run, how big result, any ORDER BY and GROUP BY operations, check how many temporary tables create on disk.

Next - depending from number of transactions, calculate proper size of inndob_log_file

Step, by step excluding the problem, first step - resolve problem in mostly case, if not second in 99% and only after start "precision" tuning.

• Kheshav Sewnundun: Hello, the select statement examines 147205 rows. and temp disk tables 0 .
• a_vlad: Start from increasing memory, it always GOOD step, give for MySQL Server at least 50% of memory.
• a_vlad: With last update - "Infact the tables are of myisam engine and not Innodb and the select is being done from a View." and starting original post from many InnoDB parameters ... means You are use InnoDB :-) as well as Select from View of course