0

I run a test server and a production server..

When I do some changes on the db structure on the test server I save all the SQL queries to later run them on the production server when I deploy the updates

Today I deployed some updates on the production server, but at least one query is now so slow that it will timeout (it takes several minutes).. It used to take 1-2 sec

On the test server the same query still runs smooth as it used to

query

SELECT dv.id,dv.client_id,b.name block_name,dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,cv.name ocr_vatno_name,dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified,dv.ocr_fi_creditorno,dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
FROM `data_voucher` dv
LEFT JOIN `block` b ON b.id=dv.block_id
LEFT JOIN `cache_vatno` cv ON cv.vatno=dv.ocr_vatno
ORDER BY dv.time DESC,dv.id DESC
LIMIT 0,25

It doesn't make sense that it timeout on one server but doesn't on the other.. When I dump the table structures the query affect the dumps are exactly the same?

If I exclude the line with LEFT JOIN cache_vatno cv ON cv.vatno=dv.ocr_vatno and remove the field cv.name ocr_vatno_name from the select clause it runs and completes as it did before the deploy

I don't know what to do?

I have now used perconas extrabackup to dump the production database and deploy it on the test server.. Now the test server has the same issues?

I can browse the table cache_vatno in phpmyadmin without problems

The table data_voucher has over 100k rows

Have tried to restart mysql

Addeing EXPLAIN to the query give this

enter image description here

1

10 times as slow? And if you ran it again, it would be back to normal?

That is normal. Before the big ALTER, all the data for that query was cached in RAM. Then the ALTER bumped most of it out. After that, the query had to go to disk, hence 10x slower. Running again should see it in cache and be fast again.

If this does not fit with what you are seeing, please provide the following so we can dig deeper:

  • SHOW CREATE TABLE for each table
  • The size of each table
  • The query
  • Value of innodb_buffer_pool_size
  • The ALTER
  • Are the JOINs 1:1? If so, there is a probably a significant rewrite to speed it up, even the 1-2 seconds.

Notice that the query must read all of the rows from all 3 tables, and put all the data into a temp table for sorting.

You don't seem to need LEFT, can you remove it?

Possible indexes (pending seeing the above info):

block -- already has PRIMARY KEY(id)?
cache_vatno:  INDEX(vatno)
data_voucher:  INDEX(time, id)

If the joins are 1:1? Assuming they are, give this rewrite a try:

SELECT  SQL_CALC_FOUND_ROWS dv.id,dv.client_id,
        ( SELECT b.name FROM block b WHERE b.id=dv.block_id ) AS block_name,
        dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,
        dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,
        dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,
        dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,
        ( SELECT cv.name FROM `cache_vatno` cv
              WHERE cv.vatno=dv.ocr_vatno ) AS ocr_vatno_name,
        dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,
        dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,
        dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified, dv.ocr_fi_creditorno,
        dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,
        dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,
        dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,
        dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,
        dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
    FROM  `data_voucher` dv
    ORDER BY  dv.time DESC,dv.id DESC
    LIMIT  0,25

Rationale: I turned the JOINs into subqueries, thereby making it obvious that things are 1:1, and letting the Optimizer focus on just the one table (dv). This should allow it to use INDEX(time, id) to handle both the ORDER BY and the LIMIT, thereby stopping after 25 rows.

Unfortunately, that still leaves the processing of SQL_CALC_FOUND_ROWS. It must proceed to count the rest of the rows in that index. So, removing the CALC may speed up the query signficantly. Does the UI really need it?

...

  • I have removed SQL_CALC_FOUND_ROWS and same problem.. The query hits timeout.. Everthing is 1:1.. – clarkk Jan 6 at 17:16
  • The query is more than 10x slower.. it takes more than 5 minutes and I still haven't managed to complete it because it hits timeout in phpmyadmin – clarkk Jan 6 at 17:29
  • have updated the question with more info and the results of EXPLAIN SELECT ... – clarkk Jan 6 at 17:40
  • Your query with subqueries seems to work.. But I don't get why my query with joins hits timeout.. – clarkk Jan 6 at 17:49
  • @clarkk - I think you have not yet added both of my suggested indexes. – Rick James Jan 6 at 17:52

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.