# Please help with optimization

**URL:** <https://forums.percona.com/t/please-help-with-optimization/1415>\
**Category:** Other MySQL® Questions\
**Created:** [May 24, 2010, 5:13am UTC](https://forums.percona.com/t/please-help-with-optimization/1415 "2010-05-24T05:13:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![fenritz](https://avatars.discourse-cdn.com/v4/letter/f/77aa72/32.png) [@fenritz](https://forums.percona.com/u/fenritz)\
**Post date:** [May 24, 2010, 5:13am UTC](https://forums.percona.com/t/please-help-with-optimization/1415/1 "2010-05-24T05:13:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\
**Post date:** [May 24, 2010, 8:12am UTC](https://forums.percona.com/t/please-help-with-optimization/1415/2 "2010-05-24T08:12:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fenritz](https://avatars.discourse-cdn.com/v4/letter/f/77aa72/32.png) [@fenritz](https://forums.percona.com/u/fenritz)\
**Post date:** [May 24, 2010, 8:35am UTC](https://forums.percona.com/t/please-help-with-optimization/1415/3 "2010-05-24T08:35:36Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\
**Post date:** [May 25, 2010, 8:04am UTC](https://forums.percona.com/t/please-help-with-optimization/1415/4 "2010-05-25T08:04:34Z")

</div>

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.