CODEWITHSUNDEEP
mysqlsqlperformancesqlite
Scott
Scott

Reputation: 61

Simple SQL update query is slow on small table

I've recently migrated to database storage for my PHP sessions. After making the session handler functions I've realized that my writes are.. slow.

The following query takes 0.04 seconds on MySQL and 0.08 seconds on SQLite. This doesn't seem slow, until I realize that the table only has 6 rows on a new set up.

UPDATE sm_sessions
    SET last_access =  '1463104877'
    WHERE session_id = 'smsess-5734112c09619927459593e9866792515f74ba3115d3a1093b3a31018'
    LIMIT 1

And, here's the table structure.

CREATE TABLE IF NOT EXISTS `sm_sessions` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `session_id` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `session_data` text COLLATE utf8mb4_unicode_ci,
  `last_access` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `session_id` (`session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=57 ;

Note that I've tried everything, including the following:

I understand that 0.04 seconds isn't that bad. But, the reads complete in 0.0006 seconds or so. If I have 10 writes that's 0.4 seconds. And, yes, I know that reads are usually always faster than writes, but 0.04 or worse sounds extremely bad for a table with 6 rows, a new set up, and such a simple query.

I feel like this update query should take 0.01s at WORST and usually in the range of 0.005s. Is this a reasonable expectation?

This is done locally but it seems that even on a different server the writes would be relatively slow because everything else is fast. I can post code or mysql/sqlite settings but I feel like that shouldn't matter on a recently made table with 6 rows.

What's wrong?

EDIT| Updating the mysql table that is created when installing mysql, a simple update query takes 0.005 seconds. Same database server. Something's wrong.

EDIT2| Changing the storage engine from InnoDB to MyISAM made the query run much faster - 0.0009 seconds. I would mark this as solved, and I probably will, but does anyone know why this sped it up?

EDIT3| Sorry for all of the edits, but anyone reading should know that I found the solution in the answer below.

Upvotes: 0

Views: 1702

Answers (3)

Rick James
Rick James

Reputation: 142560

char(64) COLLATE utf8mb4_unicode_ci is awful for ascii strings.

  • CHAR is fixed width, there wasting space. In particular, that takes 4*64 (=256) bytes, regardless of the contents.

  • COLLATE utf8mb4_unicode_ci adds unnecessary complexity if the text is always letters, dash, and digits.

Switch to VARCHAR(64) CHARACTER SET ascii. session_data can stay utf8mb4 if that is suitable.

Upvotes: 0

Scott
Scott

Reputation: 61

I started benchmarking using mysqlslab, thanks to @tadman's suggestion, and I've figured out the problem.

My connection and server charsets and collations were set to a mixture of latin1 and utf8.

This table is comparing strings using utf8mb4 and utf8mb4_general_ci.

After changing the connection and the server to match, I get speeds of <0.001 seconds (more than 40 times faster than the previous 0.04 seconds).

Showing my tests..

Before I figured out the problem, I ran the following command:

scott@scottsdevbox:/etc/mysql$ /usr/bin/mysqlslap --user=root --password=mypasswordhere --delimiter=";" --create="CREATE TABLE a (session_id char(64) COLLATE utf8mb4_unicode_ci NOT NULL, session_data text COLLATE utf8mb4_unicode_ci, last_access int(10) NOT NULL, PRIMARY KEY (session_id)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO  a (session_id ,session_data ,last_access)VALUES ('smsess-57356c97a576f244771728b1cf2b22c3d05ee2a891ab34c4c60cd5fe9',  'abczyx123string',  '1463120872');" --query="UPDATE a SET last_access =  '1463120866' WHERE session_id = 'smsess-57356c97a576f244771728b1cf2b22c3d05ee2a891ab34c4c60cd5fe9' LIMIT 1" --concurrency=1 --iterations=100 --engine="InnoDB"

And got the usual results with the 0.04 speeds:

Benchmark
    Running for engine InnoDB
    Average number of seconds to run all queries: 0.046 seconds
    Minimum number of seconds to run all queries: 0.033 seconds
    Maximum number of seconds to run all queries: 0.211 seconds
    Number of clients running queries: 1
    Average number of queries per client: 1

And then, after changing my server and client charset (to utf8mb4) and collation (to utf8mb4_general_ci) to match the database and fields..

Command:

scott@scottsdevbox:/etc/mysql$ /usr/bin/mysqlslap --user=root --password=mypasswordhere --delimiter=";" --create="CREATE TABLE a (session_id char(64) character set utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, session_data text character set utf8mb4 COLLATE utf8mb4_general_ci, last_access int(10) NOT NULL, PRIMARY KEY (session_id))  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO  a (session_id ,session_data ,last_access)VALUES ('smsess-57356c97a576f244771728b1cf2b22c3d05ee2a891ab34c4c60cd5fe9',  'abczyx123string',  '1463120872');" --query="UPDATE a SET last_access =  '1463120866' WHERE session_id = 'smsess-57356c97a576f244771728b1cf2b22c3d05ee2a891ab34c4c60cdsed5fe9' LIMIT 1" --concurrency=1 --iterations=100 --engine="InnoDB"

Results:

Benchmark
    Running for engine InnoDB
    Average number of seconds to run all queries: 0.000 seconds
    Minimum number of seconds to run all queries: 0.000 seconds
    Maximum number of seconds to run all queries: 0.001 seconds
    Number of clients running queries: 1
    Average number of queries per client: 1

In conclusion, I learned that changing your client and server charset and collation to match the database and tables and fields charset and collation increases query speed.

Upvotes: 2

CL.
CL.

Reputation: 180310

Every change to the database involves locking and sychronization overhead, which happens only once per transaction, and is not dependent on the amount of data accessed or written.

So it is likely that the time will not get much larger when the size of the DB increases.

To make multiple writes faster, put them all into the same transaction.

Upvotes: 0

Related Questions