CODEWITHSUNDEEP
mysqlperformance
gfunk
gfunk

Reputation: 381

MySQL MEMORY Engine Performance - Concurrent Insert vs Update

Thanks in advance for your help!

Question

While executing a large volume of concurrent writes to a simple table using the MySQL Memory Storage Engine is there an objective performance difference between having all the writes be (A) updates to a very small table (say 100 rows) vs (B) inserts? By performance difference, I'm thinking speed/locking - but if there are other significant variable(s) please say so. Since the answer to this less specific question is often "it depends" I've written scenario's (A) & (B) below to provide context & define the detail's in hopes of allowing for an objective answer.

Example Scenarios

I've written scenario's (A) & (B) below to help illustrate & provide context. You can assume excess of RAM & CPU, MySQL 5.7 if it matters, the scenarios are simplified, and I'm using the Memory engine to remove Disk I/O from the equation (and I'm aware it uses table-level locking). Thanks again for your help!

~ Scenario A ~

1) I've got a memory table with ~100 rows like this:

CREATE TABLE cache (
  campaign_id MEDIUMINT UNSIGNED NOT NULL,
  sum_clicks SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (campaign_id)
) Engine=MEMORY DEFAULT CHARSET=latin1;

2) And ~1k worker threads populating said table like so:

UPDATE cache SET sum_clicks+=x WHERE campaign_id=y;

3) And finally, a job that runs every ~hour which does:

CREATE TABLE IF NOT EXISTS next_cache LIKE cache;
INSERT INTO next_cache (campaign_id) SELECT id FROM campaigns;
RENAME TABLE cache TO old_cache, next_cache TO cache;
SELECT * FROM old_cache...into somewhere else;
TRUNCATE old_cache;
RENAME TABLE old_cache TO next_cache; // for next time

~ Scenario B ~

1) I've got a memory table like this:

CREATE TABLE cache (
  campaign_id MEDIUMINT UNSIGNED NOT NULL,
  sum_clicks SMALLINT UNSIGNED NOT NULL DEFAULT 0
) Engine=MEMORY DEFAULT CHARSET=latin1;

2) And ~1k worker threads populating said table like so:

INSERT INTO cache VALUES (y,x);

3) And finally, a job that runs every ~hour which does:

(~same thing as scenario A's 3rd step)

Post Script

For those searching stackOverflow for this I found these stackOverflow questions & answers helpful, especially if you are open to using storage engines beyond the MEMORY engine. concurrent-insert-with-mysql and insert-vs-update-mysql-7-million-rows

Upvotes: 0

Views: 886

Answers (1)

Rick James
Rick James

Reputation: 142208

With 1K worker threads hitting this table, they will seriously stumble over each other. Note that MEMORY uses table locking. You are likely to be better off with an InnoDB table.

Regardless of the Engine, do 'batch' INSERTs/UPDATEs whenever practical. That is, insert/update multiple rows in a single statement and/or in a single transaction.

Tips on high-speed ingestion -- My 'staging' table is very similar to your 'cache', though used for a different purpose.

Upvotes: 1

Related Questions