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

             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-d\1"}, [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.

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.