Re: Index causing lock up to 5 minutes
Posted by: Paul Michaels
Date: October 09, 2019 01:20AM

Hi Peter,

Just an update. We have rebooted the server last night (8 hours ago) to clear memory and swap. However, it does't fix our problem.

Our hardware current resource usage is as follows:

cpu: 8%
RAM: 47%
Swap: 0%

The table currently has ~ 25k rows.

We are still experiencing delays with this query:

------------------
---TRANSACTION 421121721187368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 428177573, ACTIVE 154 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 90273, OS thread handle 139609435698944, query id 31187174 localhost sms_user Updating
UPDATE OutQueue SET ConfigID = 423 WHERE BindID = 311 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,423) OR ConfigID IS NULL) LIMIT 1000
------- TRX HAS BEEN WAITING 154 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 910 page no 305 n bits 616 index idx_OutQueue_2 of table `sms`.`OutQueue` trx id 428177573 lock_mode X locks rec but not gap waiting
Record lock, heap no 248 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: SQL NULL;
2: len 4; hex 86f35792; asc W ;;

------------------
---TRANSACTION 428176410, ACTIVE 187 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 89504, OS thread handle 139609402820352, query id 31144763 localhost sms_user Updating
UPDATE OutQueue SET StatusID = 2,SubmitTime = now(),ExpireTime = '2019-10-09 17:35:09',ConfigID = 339,SourceAddr = '35639',GatewayReference = '2029100907020968148' WHERE ID = 116610962
------- TRX HAS BEEN WAITING 187 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 910 page no 305 n bits 616 index idx_OutQueue_2 of table `sms`.`OutQueue` trx id 428176410 lock_mode X locks rec but not gap waiting
Record lock, heap no 248 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: SQL NULL;
2: len 4; hex 86f35792; asc W ;;


Our configuration is below:

[mysqld]
plugin-load-add=auth_socket.so
bind-address=0.0.0.0
max_allowed_packet=256M
max_connections=1500
max_user_connections=1500

innodb_flush_method=O_DIRECT
innodb_doublewrite=0
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_change_buffering=all
innodb_deadlock_detect=0
innodb_print_all_deadlocks=1
innodb_thread_concurrency=0
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_log_file_size=1G
innodb_log_buffer_size=64M
innodb_log_files_in_group=2
innodb_flush_neighbors=0
innodb_adaptive_hash_index=0

query_cache_type=1
query_cache_size=16M
query_cache_limit=2M
table_open_cache=2048
thread_cache_size=200
key_buffer_size=512M
join_buffer_size=1M
sort_buffer_size=2M
read_rnd_buffer_size=1M
tmp_table_size=128M
max_heap_table_size=128M
back_log=1500
transaction-isolation='READ-COMMITTED'
tmpdir=/media/mysql/tmpdir
innodb_tmpdir=/media/mysql/tmpdir

slow_query_log=1
slow_query_log_file=/var/log/mysql/slow_query.log
log_error=/var/log/mysql/error.log
log-warnings=1

server-id=1
log_bin=/media/mysql/binlog/mysql-bin.log
sync_binlog=1
binlog_do_db=sms
binlog_do_db=email

performance_schema=off


Is there anything that we can change to resolve this problem?

Options: ReplyQuote


Subject
Written By
Posted
Re: Index causing lock up to 5 minutes
October 09, 2019 01:20AM


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.