Performance issue in mysql 5.6

Question

I am facing serious performance issue in inserting, selecting and updating rows to a table in mysql.

The table structure I am using is

CREATE TABLE `sessions` (
     `sessionid` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
     `expiry` datetime NOT NULL,
     `value` text NOT NULL,
     `data` text,
     PRIMARY KEY (`sessionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sessions';

The queries for which I face issue are :

INSERT INTO sessions (SESSIONID, EXPIRY, DATA, VALUE) VALUES ('b8c10810c505ba170dd9403072b310ed', '2019-05-01 17:25:50', 'PFJlc3BvbnNlIHhtbG5zPSJ1cm46b2FzaXM6bmFtZXM', '7bKDofc/pyFSQhm7QE5jb6951Ahg6Sk8OCVZI7AcbUPb4jZpHdrCAKuCPupJO14DNY3jULxKppLadGlpsKBifiJavZ/');

UPDATE sessions SET EXPIRY = '2019-05-01 17:26:07' WHERE (SESSIONID = 'e99a0889437448091a06a43a44d0f170');

SELECT SESSIONID, EXPIRY, DATA, VALUE FROM sessions WHERE (SESSIONID = '507a752c48fc9cc3043a3dbe889c52eb');

I tried explaining the query but was not able to infer much about optimizing the table/query.

From the slow query report the time taken

for select in average is 23.45, for update it is 15.93 and for insert it is 22.31.

Any help in identifying the issue is much appreciated.

Answer 1

How many queries per second?

How big is the table?

How much RAM?

What is the value of innodb_buffer_pool_size?

UUIDs are terrible for performance. (Is that a SHA1?) This is because they are so random that the 'next' query (any of those you mentioned) is likely not to be in cache, hence necessitating a disk hit.

So, with a table that is much larger than the buffer_pool, you won't be able to sustain more than about 100 queries per second with a spinning drive. SSD would be faster.

More on the evils of UUIDs (SHA1 has the same unfortunate properties, but no solution like the one for uuids): http://mysql.rjweb.org/doc.php/uuid

One minor thing you can do is to shrink the table:

session_id BINARY(20)

and use UNHEX() when inserting/updating/deleting and HEX() when selecting.

More

51KB avg row len --> The TEXT columns are big, and "off-record", hence multiple blocks needed to work with a row.

0.8GB buffer_pool, but 20GB of data, and 'random' PRIMARY KEY --> The cache is virtually useless.

These mean that there will be multiple disk hits to for each query, but probably under 10.

300ms (a fast time) --> about 30 disk hits on HDD (more on SSD; which do you have?).

So, I must guess that 20s for a query happened when there was a burst of activity that had the queries stumbling over each other, leading to lots of I/O contention.

What to do? Most of the data looks like hex. If that is true, you could cut the disk footprint in half (and cut back some on disk hits needed) by packing and using BINARY(..) or BLOB.

INSERT INTO sessions (SESSIONID, EXPIRY, DATA, VALUE)
    VALUES (UNHEX('b8c10810c505ba170dd9403072b310ed'),
            '2019-05-01 17:25:50',
            UNHEX('PFJlc3BvbnNlIHhtbG5zPSJ1cm46b2FzaXM6bmFtZXM'),
            UNHEX('7bKDofc/pyFSQhm7QE5jb6951Ahg6Sk8OCVZI7AcbUPb4jZpHdrCAKuCPupJO14DNY3jULxKppLadGlpsKBifiJavZ/'));

UPDATE sessions SET EXPIRY = '2019-05-01 17:26:07'
 WHERE SESSIONID = UNHEX('e99a0889437448091a06a43a44d0f170');

SELECT SESSIONID, EXPIRY, DATA, VALUE FROM sessions
 WHERE SESSIONID = UNHEX('507a752c48fc9cc3043a3dbe889c52eb');

and

 `sessionid` VARBINARY(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `expiry` datetime NOT NULL,
 `value` BLOB NOT NULL,
 `data` BLOB,

And ROW_FORMAT=DYNAMIC might be optimal (but this is not critical).

Answer 2

Your queries looks good, but problem is with your server, it may not be having enough memory to handle such request, you can increase memory of your database server to to get optimised response