3

I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen (int(1)). Seen column is indexed.

My pseudo code:

SELECT * FROM pics WHERE id=:id LIMIT 1;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

And when client requests 10+ pictures in parallel (for different Id) then some UPDATE have unexpectedly hung for 1-10 seconds. I see Innodb_row_lock_waits is increased every time in this case.

Without UPDATE my code works very fast all time.

I've tried use transaction, UPDATE DELAYED (I'm not sure that I tried it correctly).

Are there some best practices for selecting and updating same rows? What additional information should I provide to clarify the question?

UPDATE 1 Server Configuration

Server: CPU: 1.5 Ghz, 1 Core, RAM 8Gb
Debian: 7.8
MySQL: 5.5.41

UPDATE 2

I've changed the SQL code to this but situation did't change. Also I removed INDEX for 'seen' column without any effects to this code.

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
END;

In mysql-slow.log I see next entries:

# Time: 150210  2:03:24
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 13.011485  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1423530204;
commit;
# Time: 150210  2:03:34
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 9.765468  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 1
SET timestamp=1423530214;
UPDATE `pics` SET seen=1 WHERE id=315 LIMIT 1;

UPDATE 3

Amount of rows in pics table are about 70.

SHOW CREATE TABLE pics;

CREATE TABLE `pics` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `image` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `archive` tinyint(1) NOT NULL DEFAULT '0',
 `seen` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `tbl_picture_timestamp` (`timestamp`),
 KEY `tbl_picture_archive` (`archive`),
 KEY `tbl_picture_seen` (`seen`),
) ENGINE=InnoDB AUTO_INCREMENT=319 DEFAULT CHARSET=utf8

SELECT COUNT(1) RowsReturned FROM pics WHERE id=315; (It took about 0.000035s)

RowsReturned 1
  • Please run SELECT COUNT(1) RowsReturned FROM pics WHERE id=315;. What is RowsReturned ? How long did it take ? – RolandoMySQLDBA Feb 10 '15 at 20:37
  • Please post the output of SHOW CREATE TABLKE pics\G – RolandoMySQLDBA Feb 10 '15 at 20:37
  • I've added details related to pics table. I want add that the server is run without any CPU/Memory loading and with almost empty tables. And I need re-run my test 10-30 times to repeat this issue. In most cases my requests work fast. – Victor Feb 11 '15 at 2:33
2

You are too focused on the details; back off. Let's look at the big picture, the benchmarking, the indexing, the transactions, etc.

How many simultaneous users are you benchmarking for? How many do you expect in reality? How many cores does your CPU(s) have? What version of MySQL are you running?

My points are: (a) The benchmark is stressing the limits, not looking for reality; (b) Oracle has made great strides recently in handling more connections.

When you go beyond the effective connection limit, latency of queries will suffer terribly. So, don't benchmark beyond that. Furthermore, throttle the users so that not "too many" get to MySQL 'simultaneously'. In older versions, it was so bad that (a) throughput would go down as you add more clients, and (of course) (b) latency would go through the roof. Now, throughput plateaus while latency climbs.

For a single item, be sure to wrap Rolando's SQL in a transaction:

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
COMMIT;

For handling more than one id at the same time, you should sort the ids to help avoid deadlocks. Then do them in a single transaction:

BEGIN;
SELECT * FROM pics WHERE id IN ($id_list) FOR UPDATE;
UPDATE pics SET seen=1 WHERE id IN ($id_list) LIMIT 1;
COMMIT;

You can simplify the code more: Do the

UPDATE...
Check rows_affected; exit if 0
SELECT ...

Since rows_affected is local to the 'session', you can discover whether the UPDATE grabbed the row. Note that there is no need for transactions (as far as this code snippet goes), and autocommit=1 would suffice.

Let me point out another issue with the design: Indexing a flag (seen) has two problems (a) The optimizer is unlikely to use the index, due to low cardinality; and (b) the update has to remove a row from that index and add a new row elsewhere; this is costly.

The logic you described does not seem to need INDEX(seen); does something else need it? If not, DROP that INDEX; that may solve the problem.

  • Thank you for the answer. I've added some my server info. Real situation is next: one user requests 10-30 pictures and sometimes MySQL hangs in UPDATE operation for 1-15 seconds. I believe that problem in UPDATE because a) without UPDATE all work fine b) I saw in 'long query log' that some UPDATE wait 1-3 seconds. This benchmark test helps me reproduce situation more often that 'normal' scenario. Related to 'seen' field, I use this field to count (or getting) all unseen pictures. – Victor Feb 6 '15 at 13:58
  • I'm still having trouble seeing the code that touches 10 pictures. Please include all the SQL in the transaction. – Rick James Feb 6 '15 at 17:36
  • Actually the SQL code is the same as in my question. This function is called by client (REST call) for each picture (:id). The issue repeats even for 10 requests at the same time. I wrapped SELECT/UPDATE by BEGIN/COMMIT and I see that COMMIT sometimes waits ~1-5 seconds. Also I removed INDEX for seen column but it did't change situation. – Victor Feb 10 '15 at 0:51
  • Consider combining the 10 REST calls into one. Then put the 10 UPDATEs in a single transaction (as discussed above). – Rick James Feb 10 '15 at 4:34
  • It could be good idea but it's existing REST interface between REST client and server. And what is more important that in the most cases it works fast. For example to repeat this issue I need refresh my client 10-30 times. I hope that problem can be resolved on server side only. – Victor Feb 10 '15 at 12:38
2
+25

Perhaps you should impose a SELECT FOR UPDATE

SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

I have mentioned this before

UPDATE 2015-02-05 22:22 EST

There will always be row locks imposed, and there is no getting around it. Yet what is the difference between your code and my code ?

Think about it. Your running the SELECT in your code does not shield the rows from changes. Those row locks you are seeing would happen during the UPDATE. My code imposes the locks on rows and all corresponding index entries during the SELECT. This makes the UPDATE have less stress.

This is in agreement with the MySQL Documentation

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

UPDATE 2015-02-10 22:05 EST

Your issue seems to be pointed out already by @RickJames: the index on seen field.

Take a look at the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

InnoDB Architecture

Please note two structures from the Picture

  • Insert Buffer With the InnoDB Buffer Pool (IB_IBP)
  • Insert Buffer With the System Tablespace (better known as ibdata1) (IB_SYSTBLSPC)

These structures are responsible for updating nonunique indexes.

Now, please note the effect of setting seen=1 on id = 315.

Assuming seen is either 0 or 1 (Cardinality of 2)

  • Having an index on seen with just 0 and 1 as values makes the index no different than managing two linked lists, each ordered by the PRIMARY KEY id.
  • Changing a 0 to 1 (or 1 to 0) will place information into IB_IBP
  • When it time to write the change to pics.ibd
    • Copy of the Changed Data Page is Written to the Double Buffer
    • Information Needed for Index Positioning is passed from IB_IBP into IB_SYSTBLSPC
  • The index entry of id 315 is moved from the side of the index where seen=0 to the other side of the index where seen=1

If you are changing the archive column in your benchmarking, these same machinations would also apply to the archive index.

In addition to the indexes being churned into and out of the InnoDB Buffer Pool, don't forget where this question started: ALL THE ROW LOCKS.

Keep something in minds about row locks. When a row lock is issued, an entire page can also be locked. The page can be the location of multiple primary key values. I have another posts where I discussed page locks (Dec 31,2012 : Queries getting stuck on very simple COUNT queries)

Please note in your comment, that you said

And what is more important that in the most cases it works fast

Why not all cases ? Because of the reasons I just mentioned. @RickJames did mention first the seen index, and he is right to suggest removing that index. Along those same lines, I suggest removing the archive index as well (especially if the archive value is being changed in the benchmark). Without removing those indexes, look at all the stress you are imposing on the InnoDB Infrastructure even though the table has 70 rows. It would be far worse with thousands or millions of rows.

  • Thank you for the answer but it seems that FOR UPDATE doesn't influence on my issue. For example I run ab -n 200 -c 20 ... (with FOR UPDATE or without) and result is in range [300ms..10000ms] per request. Without UPDATE the result is always ~300ms. – Victor Feb 6 '15 at 2:38
  • Thanks. I've removed indexes for seen and archive columns. Currently it seems that problem is repeated more rarely then with indexes but still repeated. My snippet (BEGIN;SELECT... FOR UPDATE;UPDATE...;COMMIT) sometimes wait in COMMIT for 2-4s. – Victor Feb 12 '15 at 2:07
  • The problem would be less often because there is less churn inside the InnoDB Architecture. The timestamp index would still produce some churn because the benchmark would create lots of records with the same timestamp. You could probably remove the index for the sake of the benchmark but use the index in production. – RolandoMySQLDBA Feb 12 '15 at 15:58
  • Just some additional info on churn by indexes even with a bigger buffer pool : dba.stackexchange.com/a/43828/877 – RolandoMySQLDBA Feb 12 '15 at 17:24
1

Row locks are held for the duration of a transaction. If there is contention, one of the first things you should do is make sure the code path between updating the row and committing is as tight as possible.

Useful diagnostics to be able to see these locks:

  • SHOW CREATE TABLE pics
  • SHOW ENGINE INNODB STATUS
  • SELECT * FROM information_schema.innodb_trx

Index structure affects locking (hence SHOW CREATE TABLE).

SHOW ENGINE INNODB STATUS and innodb_trx will show information about which row locks are conflicting, and which statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.