Databases
StackOverflow2013
Postsdbo
POPerformance of MySQL very bad when stored procedure iterates over large table of 15M rows
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPerformance of MySQL very bad when stored procedure iterates over large table of 15M rows
    primarykey
    data
    text
    <p>I have a <strong>stored procedure</strong> that opens a <code>CURSOR</code> on a select statement that iterates over a table of <strong>15M rows</strong> (This table is a simpl import of a large CSV).</p> <p>I need to normalize that data by <strong>inserting various pieces of each row into 3 different tables</strong> (capture auto-update ID's, use them in forein key constraints, and such).</p> <p>So I wrote a simple stored procedure, open <code>CURSOR</code>, <code>FETCH</code> the fields into varialbes and do the 3 insert statements.</p> <p>I'm on a small DB server, default mysql installation (1 cpu, 1.7GB ram), I had hoped for a few hours for this task. I'm at 24 hours+ and top shows <strong>85% wasted CPU</strong>.</p> <p>I think I have some kind of terrible inefficiency. Any ideas on improving the efficiency of the task? Or just determining <strong>where the bottleneck is</strong>?</p> <hr> <pre><code>root@devapp1:/mnt/david_tmp# vmstat 10 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 1 256 13992 36888 1466584 0 0 9 61 1 1 0 0 98 1 1 2 256 15216 35800 1466312 0 0 57 7282 416 847 2 1 12 85 0 1 256 14720 35984 1466768 0 0 42 6154 387 811 2 1 10 87 0 1 256 13736 36160 1467344 0 0 51 6979 439 934 2 1 9 89 </code></pre> <hr> <pre><code>DROP PROCEDURE IF EXISTS InsertItemData; DELIMITER $$ CREATE PROCEDURE InsertItemData() BEGIN DECLARE spd TEXT; DECLARE lpd TEXT; DECLARE pid INT; DECLARE iurl TEXT; DECLARE last_id INT UNSIGNED; DECLARE done INT DEFAULT FALSE; DECLARE raw CURSOR FOR select t.shortProductDescription, t.longProductDescription, t.productID, t.productImageURL from frugg.temp_input t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN raw; read_loop: LOOP FETCH raw INTO spd, lpd, pid, iurl; IF done THEN LEAVE read_loop; END IF; INSERT INTO item (short_description, long_description) VALUES (spd, lpd); SET last_id = LAST_INSERT_ID(); INSERT INTO item_catalog_map (catalog_id, catalog_unique_item_id, item_id) VALUES (1, CAST(pid AS CHAR), last_id); INSERT INTO item_images (item_id, original_url) VALUES (last_id, iurl); END LOOP; CLOSE raw; END$$ DELIMITER ; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDavid Parks
    1. USDavid Parks
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COJust a thought but you might be running out of physical memory at which point mysql has to start swapping blocks... this usually decreases performance drastically. But it's hard to tell without actually looking into the code and logs etc..
      singulars
      1. POPerformance of MySQL very bad when stored procedure iterates over large table of 15M rows
      1. USErik
    2. COGreat thinking, but if I'm not wrong, my update above shows no issue there.
      singulars
      1. POPerformance of MySQL very bad when stored procedure iterates over large table of 15M rows
      1. USDavid Parks
    3. COTwo things I would try: 1. Drop indexes on the tables that get inserted when doing the inserts. Add it back after all inserts are done. 2. Instead of copying row by row, dump and migrate the whole table, add an auto_increment field, copy 2 more times, then drop the fields that you don't need.
      singulars
      1. POPerformance of MySQL very bad when stored procedure iterates over large table of 15M rows
      1. USsn00k4h
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload