MySQL MEMORY Engine Performance - Concurrent Insert vs Update

Question

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

Yes, best would be benchmarking it. You'll need several clients and a gigabit connection to the server to make sure you kick it well and good. Don't benchmark on localhost unless you are planning to use it this way in production. I'm betting on the index updates killing your inserts. Maybe something like memcached would be better suited to this. — bobflux, Dec 02 '17 at 12:45

score 1 · Accepted Answer · edited Dec 02 '17 at 11:20

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.

MySQL MEMORY Engine Performance - Concurrent Insert vs Update

1 Answers1