1

I have a MySQL Database with millions of products. Every day, a script runs to update the data for each product (stock, price, images...) but it has to run hundreds of thousands of insert or updates, and renders the Database unusable. When the script runs, I cannot load a single page on my website.

In general, what could be the reasons for requests to slow down the Databse as such ?

EDIT :

Here are the two queries that run the most and their EXPLAIN :

Query 1:

REPLACE INTO REFERENCE
SET id                       = 8416,
    export_option            = 0,
    remise                   = 0,
    code                     = 'BIH405086',
    type_option_id           = 0,
    designation              = 'Kit bielle Hotrods',
    description              = 'Bielle forgée - Fournis avec maneton, cale et roulements',
    marque_id                = 272,
    creation_time            = NULL,
    statut                   = NULL,
    prix_public_ht           = 96.67,
    designationexport        = NULL,
    web                      = 1,
    tva_id                   = 1,
    marque                   = 'Hot rods',
    categorie_id             = 57,
    timestamp                = '20210930200554215',
    poidskg                  = 0.75,
    souscategorie_id         = 840,
    bundle                   = 0,
    typeref                  = 'Pièce',
    archive                  = 0,
    partie                   = 'Moteur',
    categorie                = 'Kits piston & moteur',
    souscategorie            = 'Kit Bielle',
    remisemax                = 0,
    universel                = 0,
    points                   = 34,
    poids_volumetrique       = 0.5,
    destockage               = 0,
    pointsmanuel             = 0,
    relation_id              = 0,
    priorite                 = 0,
    partie_id                = 11,
    interdit_aerien          = 0,
    reference_fabricant_code = '8640',
    site_id                  = 1,
    site_shogun              = 1,
    user_id                  = 1,
    site_discount            = 0,
    dispo_fournisseur        = 0,
    discount_remise          = 0,
    discount_couleur         = NULL,
    discount_taille          = NULL,
    discount_sexe_femme      = 0,
    discount_prix_vente_ttc  = 0,
    discount_prix_remise_ttc = 0,
    discount_web             = 0,
    google_bestseller        = 0,
    google_marge             = 0,
    visible_discount         = 1,
    ensemble                 = 0,
    remise_uniquement_ref    = 0,
    vendu_par                = 0,
    sm_solde                 = 0,
    sm_prix_barre            = 0,
    sm_remise                = 0,
    sm_prix_remise           = 0,
    dm_solde                 = 0,
    dm_prix_barre            = 0,
    dm_remise                = 0,
    dm_prix_remise           = 0;

EXPLAIN 1

Query 2:

REPLACE INTO REFERENCE_FOURN
SET id                     = 3046776,
    designationimport      = NULL,
    relation_id            = 15,
    code                   = '1106520',
    prix_achat_ht          = 124.92,
    description            = 'Tampons de protection R&G RACING Aero noir - Aprilia Tuono 660',
    datedispo              = '0000-00-00',
    reference_fabricant_id = 0,
    date_heure             = '2021-09-30T20:15:30',
    archive                = 1,
    code_ean               = NULL,
    disponibilite          = 5,
    stock                  = 5,
    prix_public_cons_ht    = 0,
    quantiteconditionnee   = 1,
    designation            = NULL,
    creation_time          = '2021-09-26T20:10:11',
    prixauconditionnement  = 0,
    jour_de_delai          = 0,
    tarif_exclure          = 0,
    prix_remise            = 0,
    prix_rfa               = 0,
    stock_moinsn           = 0,
    code_barre             = '1106520',
    poidskg                = 0,
    frais_sup              = NULL,
    non_livrable           = NULL,
    user_id                = 1,
    discount_stock         = 0,
    inactive               = 0;

EXPLAIN 2

The queries are actually REPLACE and not UPDATE.

4
  • This is going to depend on the query plan that occurs during this update: e.g. is an appropriate index used with an appropriate operation?, and your use cases: e.g. does this update have to happen in the middle of the day or can it be moved off hours?...or can it be changed to occur less frequently?...can you reduce the number of inserts/updates that need to occur either as a whole or by doing it in smaller batches at least? These are some general questions to consider, but aside from that we'd need more information such as your EXPLAIN ANALYZE and the update query with example data.
    – J.D.
    Oct 8 at 11:55
  • 1
    I added the EXPLAINs as images and the two main queries. Thank you for your help
    – Peter suib
    Oct 8 at 12:40
  • @J.D. - The real problem is the "hundreds of thousands" of rows being modified; indexing cannot get away from the saving of the old rows for possible undo.
    – Rick James
    Oct 8 at 20:18
  • @RickJames Surely if an index seek type of operation is more applicable to OP's situation, and they're currently getting a scan type of operation, then the seek would be a quicker operation to locate the data at least, and visa-versa. Any improvement in runtime is of benefit. But yea, agreed it's probably more of an issue of the amount of data (both rows and columns) being updated in a given instance, and how often OP may be running such an update.
    – J.D.
    Oct 9 at 0:51
1

REPLACE performs a DELETE and an INSERT. "Upsert" (INSERT .. ON DUPLICATE KEY UPDATE ..) is a better replacement.

If you are replacing every row, there it is better to:

  1. build a new table
  2. RENAME TABLE real TO old, new TO real;
  3. DROP TABLE old;

The is essentially no downtime for the flipover. (Note: this does neither Replace nor Update.)

If you are "replacing" some of the rows, then load the new data into a temp table, then copy (upsert or otherwise) them into the real table. But do it in chunks of 100 to 1000. See http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks (It talks about Deleting, but the idea applies to other operations.)

The interruptions (to do chunks) are frequent but very brief.

This would be done with

INSERT INTO real 
    SELECT ...
        WHERE id BETWEEN ... AND ... -- start/end picked to limit to 100 or 1000 rows
        FROM tmp_table
    ON DUPLICATE KEY
        code = VALUES(code),
        ...

Yes, the tmp_table needs a PRIMARY KEY to help with the chunking.

1

Are you really updating every field in every record every day?
You'd be better off loading a whole separate table with new data and then making your Application switch over to use that.
One way I've see this done is with two Tables and View over the top of them. This works really well for static and Reference data. While your Application is running with data in one table, you can load the data into other without locking up anything that matters and then "switch" the View to look at the table you've just loaded. That's transactional and very, very quick.

Failing that, are you updating every field every day?
if you only update the fields that have changed, you'll create far less traffic going through the transaction log, and that should speed things up immensely. Of course, you still have to work out what's changed and fire off the right SQL, but the improvement should be worth the effort.

3
  • Note: the VIEW only points to one table at a time.
    – Rick James
    Oct 8 at 20:27
  • So having two tables would mean duplicating the data ? About the queries too, would UPDATE be better that REPLACE ?
    – Peter suib
    Oct 11 at 7:04
  • Two Tables, one View, so yes, you have two copies of the data (worst case - you /can/ truncate one of the tables once the View is looking at the other one, but you still need space for /both/ during the load process). The big advantages of having two tables are (1) that you can use database utilities, which are /really/ fast, to do the loading and (2) the Application is not [much] disturbed during the loading - only momentarily during the switch of the View. REPLACE vs UPDATE is largely syntactic candy - the database has to do the same amount of work for both.
    – Phill W.
    Oct 11 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.