MySQL high lock_time on UPDATE query leads to server freeze

Question

This is killing me. Please help me. My production server freeze once a week. There were queries that lock rows, and block other queries, consuming 100% CPU all the time. I need to manually kill those frozen queries before the server coming back working.

I have a system that will show the highest CTR banners on the website based on any given slot. This is my table structure.

CREATE TABLE IF NOT EXISTS `banners` (
  `banner_id` int(5) NOT NULL AUTO_INCREMENT,
  `banner_slot` varchar(15) NOT NULL,
  `banner_img_path` varchar(200) NOT NULL,
  `banner_link` varchar(200) NOT NULL,
  `banner_views` int(8) NOT NULL DEFAULT '1',
  `banner_clicks` int(8) NOT NULL DEFAULT '0',
  `banner_ctr` double(5,3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`banner_id`),
  KEY `banner_slot` (`banner_slot`,`banner_views`,`banner_ctr`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4231;

This runs on production server (CentOS6.3/MySQL 5.5.28/Apache2/Quad core CPU/8GB RAM). Pretty old, I know.

The server also runs a few more wordpress sites, which I already use the WP super cache plugin. There are some queries from wordpress appears in slow query log file, but they are all under 5 seconds.

Recently, I've got approx 20K page views daily. With about 2-3K UIPs at peak hours (all domains combined).

This is my my.cnf settings:

innodb_file_per_table=1
open_files_limit=10192
skip-external-locking
key_buffer_size=800M
max_allowed_packet=100M
table_open_cache=512
table_cache=500
sort_buffer_size=2M
read_buffer_size=4M
read_rnd_buffer_size=8M
thread_cache_size=4
query_cache_type=0
query_cache_size=4M
join_buffer_size=8M
tmp_table_size=512M
max_heap_table_size=256M
max_connections=200

This is my slow query log file. You'll see that there is high lock time on such a basic query here.

# Query_time: 84.554967  Lock_time: 37.070954 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010708;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=6;
# Query_time: 84.614748  Lock_time: 37.130587 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010708;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=60;
# Query_time: 54.288041  Lock_time: 0.000018 Rows_sent: 0  Rows_examined: 1
SET timestamp=1537010708;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=884;
# Query_time: 104.154232  Lock_time: 34.661097 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010744;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=60;
# Query_time: 107.847145  Lock_time: 38.354068 Rows_sent: 0  Rows_examined: 1
SET timestamp=1537010744;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=6;
# Query_time: 81.974780  Lock_time: 26.446288 Rows_sent: 0 Rows_examined: 1
SET timestamp=1537010771;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=6;
# Query_time: 102.331612  Lock_time: 46.507686 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010772;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=60;
# Query_time: 81.808158  Lock_time: 36.196089 Rows_sent: 0  Rows_examined: 1
SET timestamp=1537010772;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=7;

I've gone through mysql-tuner.pl and almost all settings are green based on the recommendations.

I'm also EXPLAINING all the SELECT queries, and those are all using indexes. I couldn't EXPLAIN on any other queries since I'm running on MySQL 5.5.

Any recommendations on my issue? Thanks in advance! Any help are appreciated!

"I'm also EXPLAINING all the SELECT queries, and those are all using indexes. I couldn't EXPLAIN on any other queries since I'm running on MySQL 5.5. " You can rewrite a UPDATE query into a SELECT query.. `UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=6;` is more or less the same as `SELECT banner_views+1 WHERE banner_id=6;` then you can use a `EXPLAIN`... Also the output of `SHOW CREATE TABLE banners` would be nice. — Raymond Nijland, Sep 15 '18 at 21:10
Can you check your `innodb_buffer_pool_size` ? Also check this answer https://dba.stackexchange.com/a/27341/160363 — Madhur Bhaiya, Sep 15 '18 at 21:11
what happens if you make query `banner_views=banner_views` . I mean when you don't increase banner_views ? — Fatihd, Sep 15 '18 at 21:15
@MadhurBhaiya my innodb_buffer_pool_size is 134217728. Based on your link, I got a '1' as the answer. Is this way too high? — Jessica, Sep 15 '18 at 21:16
@RaymondNijland Thanks. EXPLAIN SELECT banner_views+1 FROM banners WHERE banner_id=6; shows that I'm using the primary key, length 4. — Jessica, Sep 15 '18 at 21:19
@Jessica you may want to consider the possibility that these slow queries are not the actual problem, but a symptom. Cloud servers are cheap, and it might make sense to move this traffic elsewhere, to isolate it. — Michael - sqlbot, Sep 15 '18 at 22:07
@jessica Have you considered running OPTIMIZE TABLE banners; ? Likely will eliminate table page fragmentation due to transactions posted in past few weeks. — Wilson Hauck, Sep 22 '18 at 15:38

score 0 · Answer 1 · answered Sep 16 '18 at 17:46

Rate Per Second= RPS

Suggestions to consider for your my.cnf [mysqld] section

# 20180916 12:30  by  mysqlservertuning.com

query_cache_size=0  # from 4M conserve RAM & CPU QC type=0 = OFF to start with
read_buffer_size=128K  # from 4M to reduce CPU busy & handler_read_next RPS
read_rnd_buffer_size=256K  # from 8M to reduce CPU busy & handler_read_rnd_next RPS
join_buffer_size=128K  # from 8M to conserve RAM
thread_cache_size=32  # from 4 to reduce threads_created & CPU overhead
max_heap_table_size=512M  # from 256M should be same as tmp_table_size always

Copy existing my.cnf for back up somewhere, (just in case) then

Date line and all 6 variables to be copied to END of [mysqld] section of my.cnf to override previous requested values.

STOP/START your instance or RESTART your instance. Please SKYPE me, when time permits.

MySQL high lock_time on UPDATE query leads to server freeze

1 Answers1