0

I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) suddenly the query takes around 2 minutes, If i restart mysql and rerun it it takes again 2 seconds.

The durations are almost the same (~2 seconds and ~ 2 minutes) so even when it slows down it's not random, and it's specifically this query that gets slower (or maybe this table), everything else is normal.

  • Disabled query_cache
  • increased innodb_buffer_pool_size (I assumed it had to do with a memory or a buffer getting full making the next query slower)
  • checked lock tables

I don't know what else (except the data) can a query affect another query/table after it.

Is there any path to search for or something to try, I don't have any lead on what to search for.


I'm using MySQL version 5.7

the explain plan is

enter image description here

I actually found out that the issue is not with a specific table, but simply too many prepared statements

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Com_prepare_sql    | 60    |
| Com_stmt_prepare   | 82    |
| Com_xa_prepare     | 0     |
| Com_stmt_reprepare | 24    |
+--------------------+-------+

It is mentionned in the doc that A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.

I thought this meant that they should be deallocated explicitely, but apparently this is not the case.

insert into s__demand_table_14_0(year, hospital_id, disease_id, nb_patients)
        SELECT fcbf_parent.year,
           table_referral.to_hospital_id,
           table_referral.disease_id,
           SUM(ifnull(fcbf_parent.nb_patients, 0) *
               ifnull(table_referral.ratio_of_referrals * (1 + ifnull(ir.ratio_of_referrals, 0)), 0)) +
           ifnull(SUM(fcbf.nb_patients), 0) AS referred_patients
    FROM table_hospital bf_current
             join table_hospital bf_parent on bf_parent.type = ? or bf_parent.type = ?
             LEFT JOIN
         table_referral ON bf_current.type = ? and table_referral.to_hospital_id = bf_current.id and table_referral.from_hospital_id = bf_parent.id
            JOIN s__demand_catchment_by_hospital_14_0 fcbf_parent on
            fcbf_parent.disease_id = table_referral.disease_id
            and fcbf_parent.hospital_id = bf_parent.id
            and fcbf_parent.nb_patients > 0
             LEFT JOIN s__demand_catchment_by_hospital_14_0 fcbf on
            fcbf.disease_id = table_referral.disease_id
            and fcbf.hospital_id = bf_current.id
             left join s__intermediate_referral_14_0 ir
                       on (ir.from_hospital_type = ? OR ir.from_hospital_type = ?) and
                          ir.to_hospital_type = ? and ir.year = fcbf_parent.year and ir.disease_id = table_referral.disease_id
    where bf_current.type = ?
    GROUP BY fcbf_parent.year, table_referral.to_hospital_id, table_referral.disease_id;

SHOW GLOBAL STATUS LIKE 'com_%_sql';

| Variable_name   | Value |
+-----------------+-------+
| Com_dealloc_sql | 25    |
| Com_execute_sql | 76    |
| Com_prepare_sql | 50    |
+-----------------+-------+


 SHOW GLOBAL STATUS LIKE 'com_stmt_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_execute        | 100   |
| Com_stmt_close          | 48    |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 74    |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
| Com_stmt_reprepare      | 24    |
+-------------------------+-------+

RAM is not changing drastically

Explain Plan

+----+-------------+---------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------------------------------------------------+-------+----------+----------------------------------------------------+
| id | select_type | table         | partitions | type   | possible_keys                                                                                                                                                                                                    | key                                              | key_len | ref                                            | rows  | filtered | Extra                                              |
+----+-------------+---------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------------------------------------------------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | bf_parent     | NULL       | ref    | PRIMARY,base_facili_type_6d98f9_idx                                                                                                                                                                              | base_facili_type_6d98f9_idx                      | 257     | const                                          |     1 |   100.00 | Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | fcbf_parent   | NULL       | ALL    | NULL                                                                                                                                                                                                             | NULL                                             | NULL    | NULL                                           | 19142 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | base_referral | NULL       | ref    | referral_disease_id_ad6e6e71_fk_disease_icd_code,referral_from_facility_id_b9840c9f_fk_facility_id,referral_to_facility_id_81a49e98_fk_facility_id,base_referr_disease_b74959_idx,base_referr_disease_9bbdaf_idx | referral_disease_id_ad6e6e71_fk_disease_icd_code | 257     | capacity_planning.fcbf_parent.disease_id       |     3 |   100.00 | Using where                                        |
|  1 | SIMPLE      | bf_current    | NULL       | eq_ref | PRIMARY,base_facili_type_6d98f9_idx                                                                                                                                                                              | PRIMARY                                          | 4       | capacity_planning.base_referral.to_facility_id |     1 |    57.14 | Using where                                        |
|  1 | SIMPLE      | fcbf          | NULL       | ALL    | NULL                                                                                                                                                                                                             | NULL                                             | NULL    | NULL                                           | 19142 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | ir            | NULL       | ALL    | forecast_intermediat_disease_id_689b0572_fk_disease_i                                                                                                                                                            | NULL                                             | NULL    | NULL                                           |     1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------------------------------------------------+-------+----------+----------------------------------------------------+
  • MySQL version? What query? EXPLAIN query? Table structures (show create table tblname? How do your stored procedures change the same data as your query? What is the MySQL profile of the query for both your cases. – danblack Jun 7 at 1:58
  • I updated my question with these info – Dany Y Jun 7 at 5:42
  • Maybe when you execute this select after the updates, the engine has to write a lot of dirty pages to disk. You can check that this is the case by setting innodb_max_dirty_pages_pct=0. This way the dirty pages will be written directly to disk (making updates take longer). If this is the case you may want to tune your engine by setting appropriate innodb_max_dirty_pages_pct and innodb_buffer_pool_size numbers according to your requirements, – dbilid Jun 9 at 11:13
  • Just tried what you said, and still have the same issue innodb_max_dirty_pages_pct=0 in my.cnf didn't make any noticeable difference – Dany Y Jun 9 at 11:50
  • Please show us the query! And SHOW CREATE TABLE. How slow are the UPDATEs? How big is the table(s)? How much RAM? What is the value of innodb_buffer_pool_size? – Rick James Jun 10 at 5:00
0

Your com_prepare_sql was 50, com_dealloc_sql was 25, meaning you have left RESOURCES in USE because the CLOSE() has not signaled you have completed the process.

Your com_stmt_prepare was 72, com_stmt_close was 48, meaning you have left RESOURCES in USE because the CLOSE() has not signaled you have completed the process.

Consistent query execution speed is more likely to be achieved when the dealloc and close counts are closer to the respective prepared counts.

General Log capture and analysis will confirm the resources were left in use and not deallocated or closed.

Disclaimer: I am the content author of website mentioned in my profile, Network profile that includes FREE Utility Scripts and contact info.

0

I was finally able to reduce the time dramatically by doing these 3 steps:

  • removed excess of indexes
  • remove autocommit, foreign key checks and unique checks at the beginning of each procedure
  • Changing storage engine of the tables i'm filling to MyISAM.

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.