Optimising small mysql DB for large inserts

Question

So, an AWS micro instance with around 300mb given to mysql and rest for apache with a live site on it. Everything runs fine in regards to the operation of this setup (didnt really test for ddos but otherwise fine)

New data comes in via a script, prices inserted into 1 MYISAM table of some 1 million records. Noticed a slowdown and an eventual crash of mysql and the whole server sometimes after an import. Import is scripted to mark all old records in a table, then it inserts around 600,000 new records, and finally deletes the marked old records. After this process, mysql seems to just slow down, its using all of its allowed RAM and while the site is still barely responsive it seems to eventually grind to a halt. After a restart everything is fine.

I believe whats happening is its paging all the inserts through ram and then tries to write to disk afterwards possibly causing a slowdown.

No other write operations are performed on the db, this import is the main one. Rest of the data is rarely changed, so all of the rest is mainly reads from tables for site functionality. How would you go about optimising this to remove the slowdown without increasing RAM or the instance size? My thoughts are:

• Optimise the import script, and instead of marking records for deletion, do the deletion immediately then proceed with new inserts (I know I could optimise the inserts for bulk inserts but the script is so complicated I would rather a lazier solution :-)
• Optimise the mysql setup forcing it to insert straight to disk (tried to find this but, there were so many settings to tweak that I just thought nah, too complicated)
• replicate the db into a copy, do the inserts on copy, then migrate the table in question to live db, i supopose theoretically maybe this may alleviate original db from any post processes?

Answer 1

"Marking old records" -- As in a big UPDATE? Chunk it: mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Abandon MyISAM; switch to InnoDB. The big Update probably changed part of the MyISAM table, leaving the operation unfinished and possibly no way to repair the damage. InnoDB is 'ACID', as in "all or none" in the case of big Updates.

Big INSERTs should be batched with groups of 100-1000 per group unless it is already being done with LOAD DATA.

It might be better to load the copy over the rows to keep. This is a lot faster than a big delete. See the above link for a discussion of this technique and others.

Yes, I/O is probably the cause of the slowdown. Update, Insert, and Delete are all "read, modify, write". This is because all operations are done via "blocks". And all is done in RAM. But, due to caching, only a few blocks must be in RAM at a time.

Delete: Fetch the disk block with the data, and mark it as deleted. Fetch each index block that points to it, and remove the index entry.

Insert and Update operate similarly.

MyISAM caches indexes in the "key_buffer" (size controlled by key_buffer_size), blocked 2KB at a time. MyISAM caches uses the OS to data blocks; these might be 4KB at a time.

InnoDB caches data blocks and index blocks 16KB etc, in the "buffer_pool", of size innodb_buffer_pool_size.

Since you are tight on RAM, I recommend

key_buffer_size = 60M  -- if using MyISAM
innodb_buffer_pool_size = 150M  -- if using InnoDB

(Caution: There are reasons why my recommendations could be wrong.)

Bottom line: Create a new table with the desired data, then swap tables:

LOAD DATA INFILE  ... -- the changes
-- build new_table with minimal read-modify-write
RENAME TABLE main_table TO old,
             new_table TO main_table;
DROP TABLE old;

The RENAME is "instantaneous".

Be sure to have optimal indexes on the tables for each step. But do not have unnecessary indexes on the resulting table. Instead, ALTER TABLE ... ADD INDEX ..., ADD INDEX ... after loading the table. Since RAM is tight, this helps avoid thrashing in the cache; adding index afterward is a streaming table scan + sort + streaming write to disk.

(I have tried to write my advice so that it will apply to both MyISAM and InnoDB.)

Answer 2

Wow, ok, thank you previous poster, so I have taken your advice on running operations on a temp table, without any indexes. As soon as I ran the import I could tell piped output was flying faster on my screen than before. The import time went down, from one hour, to 10 minutes.

Basically, what I added to import,

    DROP TABLE IF EXISTS tmp;
    CREATE TABLE tmp blah blah same as original
    INSERT tmp SELECT * FROM original WHERE blah blah (this bit does the deletion effectively);
    DO the import data crunch and multiple inserts into tmp
    CREATE INDEXes...
    RENAME TABLE original TO old,
    tmp TO original;
    DROP TABLE old

Unfortunately, server still locked up at the end and a restart was needed, however some clues were left, swap space was seen to increase in activity rapidly right before the crash but not at all during the import. On another overview of the original import, especially the end code, I noticed a little clean up function, which really did no cleaning at all but just threw mysql into a fit with some crazy additional selects and deletes. Upon removing this, the original crash issue was solved, however the optimisation of using no indexes on the temp table copy and still doing multiple inserts (which were not staggered, grouped, or optimised in any way) yielded such an improvement! I can see mysql was not even bothered during the actual 10 minutes of the import of these 600k new records, the front end still being responsive during and after. :-)

Thanks to the other guy who made me look at the code more, I HAVE seen the offending cleanup function output pop up on screen at the end, but always presumed its operations were not as heavy as the actual import itself, lol.

Also, here are some stability tests for the setup, ddosing the frontend with 2,5 then 10 concurrent users going through the whole sitemap. Server remains rock solid.

siege -b -c 2 -r once -i -f /var/tmp/urls.txt --no-parser --no-follow
** SIEGE 4.0.4
** Preparing 2 concurrent users for battle.
The server is now under siege...
Transactions:                    490 hits
Availability:                 100.00 %
Elapsed time:                 152.74 secs
Data transferred:               7.37 MB
Response time:                  0.60 secs
Transaction rate:               3.21 trans/sec
Throughput:                     0.05 MB/sec
Concurrency:                    1.92
Successful transactions:         490
Failed transactions:               0
Longest transaction:           14.69
Shortest transaction:           0.11

siege -b -c 5 -r once -i -f /var/tmp/urls.txt --no-parser --no-follow
** SIEGE 4.0.4
** Preparing 5 concurrent users for battle.
The server is now under siege...
Transactions:                   1225 hits
Availability:                 100.00 %
Elapsed time:                 256.70 secs
Data transferred:              18.63 MB
Response time:                  1.00 secs
Transaction rate:               4.77 trans/sec
Throughput:                     0.07 MB/sec
Concurrency:                    4.76
Successful transactions:        1225
Failed transactions:               0
Longest transaction:            8.61
Shortest transaction:           0.11

siege -b -c 10 -r once -i -f /var/tmp/urls.txt --no-parser --no-follow
** SIEGE 4.0.4
** Preparing 10 concurrent users for battle.
The server is now under siege...
Transactions:                   2450 hits
Availability:                 100.00 %
Elapsed time:                 510.80 secs
Data transferred:              38.01 MB
Response time:                  1.99 secs
Transaction rate:               4.80 trans/sec
Throughput:                     0.07 MB/sec
Concurrency:                    9.53
Successful transactions:        2450
Failed transactions:               0
Longest transaction:           12.74
Shortest transaction:           0.11