Please help with optimization

MySQL & MariaDB Other MySQL® Questions
1

Hello. I have server with 24 processors and 16 GB of RAM. Mysql files are located on partition which is built with 3 64GB SSD hard drives in RIAD5. OS is Fedora 11 x86_64. My database is mainly InnoDB (95% of tables).
My problem is that some not complicated queries are getting stuck for 100+ seconds even when server is not under heavy load.
Can anybody please help to understand and fix this problem. I think I made something wrong in my.cnf.
Here is my full my.cnf:

[mysqld]
user = mysql

port = 3306
socket =/var/lib/mysql/mysql.sock

max_connections = 2048

back_log = 128

max_connect_errors = 1000

connect_timeout = 2

max_allowed_packet = 16M

net_buffer_length = 8K

datadir = /home/mysql/data

log-error = /var/log/mysqld_error.log

slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
log_output = FILE
long_query_time = 3

table_cache = 10000

join_buffer_size = 32M

tmp_table_size = 64M

sort_buffer_size = 64M

thread_cache_size = 64
thread_concurrency = 8
thread_stack = 192K

query_cache_size = 1024M
query_cache_type = 1
query_cache_limit = 16M

transaction_isolation = REPEATABLE-READ

key_buffer_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 2

innodb_data_home_dir =
innodb_data_file_path=/home/mysql/ibdata/ibdata1:1G;/home/my sql/ibdata/ibdata2:1G;/home/mysql/ibdata/ibdata3:1G;/home/my sql/ibdata/ibdata4:1G;/home/mysql/ibdata/ibdata5:10M:autoext end

innodb_file_per_table

innodb_buffer_pool_size = 5G

innodb_additional_mem_pool_size = 512M

innodb_log_group_home_dir = /home/mysql/iblogs

innodb_log_files_in_group = 4

innodb_log_file_size = 512M

innodb_log_buffer_size = 8M

innodb_max_dirty_pages_pct = 80

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

innodb_flush_method = O_DIRECT

innodb_thread_concurrency = 12

innodb_fast_shutdown = 0

innodb_max_purge_lag = 0

innodb_support_xa = 0

2

You will have to provide more information about what the “not complicated queries” look like, and the execution plan from EXPLAIN EXTENDED for these queries.
If we are going to have a chance to tell you what is wrong.

3

Here is one of the queries that is getting stuck for a long time:

mysql> EXPLAIN EXTENDED SELECT COUNT( * ) AS count
→ FROM wum_users users
→ LEFT JOIN wum_users_groups ug ON ( ug.user_id = users.id )
→ LEFT JOIN users_gps gps ON ( gps.user_id = users.id )
→ WHERE 1
→ AND users.enable = ‘1’
→ AND ug.group_id = ‘3’
→ AND users.sex = ‘2’
→ AND users.age <= ‘31’
→ AND gps.node_id = ‘73’
→ ORDER BY users.creation_date DESC , users.has_photo DESC;
±—±------------±------±-------±---------------------- -------------------------------------------------±--------- -----------±--------±--------------------±------±------- --±-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-------±---------------------- -------------------------------------------------±--------- -----------±--------±--------------------±------±------- --±-----------------------------+
| 1 | SIMPLE | gps | ref | user_id,user_to_gps_node_id | user_to_gps_node_id | 4 | const | 45374 | 100.00 | Using where; Using temporary |
| 1 | SIMPLE | users | eq_ref | PRIMARY,ind_age,ind_sex,ind_enable,obshi,ind_last_onlines,wh os_onlines | PRIMARY | 4 | edesirs.gps.user_id | 1 | 100.00 | Using where |
| 1 | SIMPLE | ug | ref | fk_ru,fk_rg,common | fk_ru | 4 | edesirs.gps.user_id | 1 | 100.00 | Using where |
±—±------------±------±-------±---------------------- -------------------------------------------------±--------- -----------±--------±--------------------±------±------- --±-----------------------------+
3 rows in set, 1 warning (0.00 sec)

But my question is if my my.cnf is good for my hardware?

4

Your config is OK.
But there certainly isn’t something in it that should cause a simple query like this with indexes and only ~45,000 rows examined in one table to take 100+ seconds.

Exactly how is it hanging?
What is the exact status in the processlist?

Because it sounds like it is waiting for something else to happen.