CODEWITHSUNDEEP
mysqlperformanceoptimization
Ogugua Belonwu
Ogugua Belonwu

Reputation: 2141

How to resolve update lock issue in MySQL

I have 2 MySQL UPDATE Query problem on my website.

Problem 1

I run a content site that updates page views for posts when users read.

Each time I send push notifications, my server times out; when I comment on the Update query that increments the page views, everything returns to normal.

This I think maybe as a result of hundreds of UPDATE query trying to update the views on the same row.

**The query that updated the tablename**    

update table set views='$newview' where id=1


Query Explain

id: 1
select_type: SIMPLE
table: new_jobs
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using where


**tablename create table**

CREATE TABLE `tablename` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `company_id` int(11) DEFAULT NULL,
 `job_title` varchar(255) DEFAULT NULL,
 `slug` varchar(255) DEFAULT NULL,
 `advert_date` date DEFAULT NULL,
 `expiry_date` date DEFAULT NULL,
 `no_deadline` int(1) DEFAULT 0,
 `source` varchar(20) DEFAULT NULL,
 `featured` int(1) DEFAULT 0,
 `views` int(11) DEFAULT 1,
 `email_status` int(1) DEFAULT 0,
 `draft` int(1) DEFAULT 0,
 `created_by` int(11) DEFAULT NULL,
 `show_company_name` int(1) DEFAULT 1,
 `display_application_method` int(1) DEFAULT 0,
 `status` int(1) DEFAULT 1,
 `upload_date` datetime DEFAULT NULL,
 `country` int(1) DEFAULT 1,
 `followers_email_status` int(1) DEFAULT 0,
 `og_img` varchar(255) DEFAULT NULL,
 `old_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `new_jobs_company_id_index` (`company_id`),
 KEY `new_jobs_views_index` (`views`),
 KEY `new_jobs_draft_index` (`draft`),
 KEY `new_jobs_country_index` (`country`)
) ENGINE=InnoDB AUTO_INCREMENT=151359 DEFAULT CHARSET=utf8

What is the best way of handling this?

[Scenario 2 removed on request]

Upvotes: 0

Views: 848

Answers (1)

Rick James
Rick James

Reputation: 142540

Scenario 1. I would expect the update of a 'view' count (or 'click' or 'like' or whatever) to be more like

UPDATE t SET views = views + 1 WHERE id = 123;

I assume you have an index (probably the PRIMARY KEY) on id?

Since there are other things going on with that table, it may be wise to split off the rapidly incrementing counter into a separate table. This would avoid interfering with other queries. You can get other data, plus the counter, by using JOIN .. USING(id).

Scenario 2 does not make sense. It seems to keep the latest date for each email, but what does country mean? Since it seems like more than just a counter, you might want a separate table to log those 3 columns.

Please provide SHOW CREATE TABLE.

There are many things that novices perceive as a "crash". Please describe further -- out of connections, out of disk space, sluggishness, the client gave error message, other operations taking too long, etc. Each has a different remedy.

Query

Are you are currently logically doing

BEGIN;
$ct = SELECT views ... FOR UPDATE;
...
UPDATE ... SET views = $ct+1 WHERE ...;
COMMIT;

If so, that is much less efficient than

(with autocommit = ON)
UPDATE ... SET views = views+1 ...;

Note that the first version hangs onto the row longer. If you fail to use FOR UPDATE, you will drop some counts.

Splitting into a separate table sort of forces you to run the UPDATE as its own transaction.

Other

innodb_flush_log_at_trx_commit:

  • Default is 1, which is secure, but leads to at least one IOPs for each transaction.
  • 2 leads to a flush once a second. During intense times, this is much more efficient. But a crash could lose up to one second's worth of updates. the inaccuracy of "view count" due to a rare crash is, in my opinion, acceptable.

KEY(views) needs to be updated every time views is changed. But, thanks to the "change buffer", this is unlikely to involve any extra I/O, at least now while you are doing the UPDATE.

INT(1) takes 4 bytes; the (1) has no meaning. Suggest changing to TINYINT (1 byte), thereby saving about 27 bytes per row. (7 columns plus 2 indexes)

country INT(1) -- Is it a flag? What is the meaning? Is it normalized to another table? Using 4 bytes for an id and an extra table when standard abbreviations ('US', 'UK', 'RU', 'IN', etc) would take 2 bytes? Suggest country CHAR(2) CHARACTER SET ascii COLLATE ascii_general_ci.

Indexing flags rarely benefits. Let's see the queries where you think such indexes might be used. And the EXPLAIN SELECT ... for them.

Upvotes: 1

Related Questions