MySQL Forums
Forum List  »  InnoDB

Mysql mysql hangs up 1-2 times per day
Posted by: Igor Chaban
Date: November 07, 2014 07:01AM

MySQL after some period of time just hangs up...
I'm thinking that it's table lock or dead lock, but cant prove that or find how to fix situation...

OS : Debian 7.7
Mysql version : 5.6.20-68.0

Table on which selects are stopping have 1000 partitions
Only 2 operations on table : select, alter table drop partition

Here is the output of show engine innodb status on hangs moment :




Type: InnoDB
Name:
Status:
=====================================
2014-11-05 09:07:03 7f69050c5700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 57411 srv_active, 0 srv_shutdown, 728 srv_idle
srv_master_thread log flush and writes: 58133
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1025041
--Thread 140089183930112 has waited at ha_innodb.cc line 5181 for 1.0000 seconds the semaphore:
Mutex at 0x1d20e3f8 '&dict_sys->mutex', lock var 1
waiters flag 1
--Thread 140089190053632 has waited at row0sel.cc line 1410 for 1.0000 seconds the semaphore:
S-lock on RW-latch at 0x1d202188 '&btr_search_latch_arr'
a writer (thread id 140087573100288) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 1380
Last time write locked in file /mnt/workspace/percona-xtradb-cluster-5.6-debian-binary/label_exp/debian-wheezy-x64/percona-xtradb-cluster-5.6-5.6.20-25.7/storage/innobase/btr/btr0sea.cc line 1494
--Thread 140089319945984 has waited at btr0sea.cc line 1869 for 1.0000 seconds the semaphore:
X-lock on RW-latch at 0x1d202188 '&btr_search_latch_arr'
a writer (thread id 140087573100288) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 1380
Last time write locked in file /mnt/workspace/percona-xtradb-cluster-5.6-debian-binary/label_exp/debian-wheezy-x64/percona-xtradb-cluster-5.6-5.6.20-25.7/storage/innobase/btr/btr0sea.cc line 1494
--Thread 140089336452864 has waited at row0sel.cc line 1410 for 1.0000 seconds the semaphore:
S-lock on RW-latch at 0x1d202188 '&btr_search_latch_arr'
a writer (thread id 140087573100288) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 1380
Last time write locked in file /mnt/workspace/percona-xtradb-cluster-5.6-debian-binary/label_exp/debian-wheezy-x64/percona-xtradb-cluster-5.6-5.6.20-25.7/storage/innobase/btr/btr0sea.cc line 1494
--Thread 140089035384576 has waited at ha_innodb.cc line 5181 for 1.0000 seconds the semaphore:
Mutex at 0x1d20e3f8 '&dict_sys->mutex', lock var 1
waiters flag 1
--Thread 140089020741376 has waited at btr0cur.cc line 2168 for 1.0000 seconds the semaphore:
X-lock on RW-latch at 0x1d202188 '&btr_search_latch_arr'
a writer (thread id 140087573100288) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 1380
Last time write locked in file /mnt/workspace/percona-xtradb-cluster-5.6-debian-binary/label_exp/debian-wheezy-x64/percona-xtradb-cluster-5.6-5.6.20-25.7/storage/innobase/btr/btr0sea.cc line 1494
--Thread 140089310893824 has waited at ha_innodb.cc line 5181 for 1.0000 seconds the semaphore:
Mutex at 0x1d20e3f8 '&dict_sys->mutex', lock var 1
waiters flag 1
--Thread 140087594399488 has waited at row0ins.cc line 2394 for 9.0000 seconds the semaphore:
X-lock on RW-latch at 0x7f6a40781640 '&block->lock'
a writer (thread id 140089029793536) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.cc line 3230
Last time write locked in file /mnt/workspace/percona-xtradb-cluster-5.6-debian-binary/label_exp/debian-wheezy-x64/percona-xtradb-cluster-5.6-5.6.20-25.7/storage/innobase/row/row0ins.cc line 2394




------------
TRANSACTIONS
------------
Trx id counter 504508809
Purge done for trx's n:o < 504507444 undo n:o < 0 state: running but idle
History list length 1205
... truncated...
days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504689, not started
MySQL thread id 60442, OS thread handle 0x7f690aba7700, query id 9161119 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504505033, not started
MySQL thread id 60439, OS thread handle 0x7f690ac6a700, query id 9161911 localhost root cleaning up
---TRANSACTION 504507431, not started
MySQL thread id 60440, OS thread handle 0x7f690ac29700, query id 9163663 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504505116, not started
MySQL thread id 60436, OS thread handle 0x7f690acec700, query id 9161785 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504505110, not started
MySQL thread id 60434, OS thread handle 0x7f690c8da700, query id 9161774 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504368, not started
MySQL thread id 60433, OS thread handle 0x7f690ad2d700, query id 9160546 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504526, not started
MySQL thread id 60432, OS thread handle 0x7f690ad6e700, query id 9160854 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504205, not started
MySQL thread id 60431, OS thread handle 0x7f690adaf700, query id 9160351 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504204, not started
MySQL thread id 60430, OS thread handle 0x7f690adf0700, query id 9160350 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504187, not started
MySQL thread id 60429, OS thread handle 0x7f690ae31700, query id 9160320 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504181, not started
MySQL thread id 60428, OS thread handle 0x7f690b07a700, query id 9160310 localhost root Opening tables
SELECT DISTINCT(prod_id), wp_code_srch AS code_srch, prod_name
FROM PRICES.workprice
JOIN CALC.acc_price_types
ON pt_id = wp_pt_id AND
pt_enabled = "Y" AND
pt_allow_usr_view = "Y"
JOIN GENERAL.producers
ON prod_id = wp_prod_id
WHERE wp_code_srch = "43053SR4A00"
---TRANSACTION 504504173, not started
MySQL thread id 60427, OS thread handle 0x7f690d64f700, query id 9160298 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504155, not started
MySQL thread id 60426, OS thread handle 0x7f690b408700, query id 9160265 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504211, not started
MySQL thread id 60425, OS thread handle 0x7f690b54d700, query id 9160364 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504504045, not started
MySQL thread id 60424, OS thread handle 0x7f690ccea700, query id 9160088 localhost root Opening tables
SELECT
prod_name,
prod_is_orig,

wp_id,
wp_prod_id,
wp_code_srch,
wp_code_printed,
wp_price,
wp_descr,
wp_weight,
wp_qty,
wp_cur_id,
wp_sup_id,
wp_pt_id,
wp_sps_id,
wp_qty_type,
wp_price_change_date,
wp_damaged_flag,
wp_used_flag,
wp_oldcode_flag,
wp_restored_flag,

pt_code,
pt_id,
pt_region,
pt_average_days,
pt_usr_descr_short,
pt_delivery_type,
pt_usr_descr_long,

sup_id,
sup_discount_l2,

dict_cur_code,

supstat_deliveries_count,
s
---TRANSACTION 504506908, not started

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql mysql hangs up 1-2 times per day
4501
November 07, 2014 07:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.