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)