[#MDEV-16531] Insert starts analyze table caulsing the insert process to be blocked with use_stat

Comment by Elena Stepanova [ 2018-06-19 ]

What do you mean by "auto analyze"? Who/what performs the auto-analyze?

Comment by Ramesh [ 2018-06-20 ]

When any insert or update runs with use_stat_tables = PREFERABLY, we see analyze on tables run automatically blocking the insert.

Comment by Elena Stepanova [ 2018-06-20 ]

How/where do you see "analyze on tables"? Do you see it in the process list as a separate command? In logs somewhere? In the stack trace?

Comment by Ramesh [ 2018-06-20 ]

yes in the show processlist command output wher use_stat_tables = preferably

Comment by Ramesh [ 2018-06-20 ]

+-----+-------------+--------------------------------------------+---------+---------+------+--------------------------+-------------------------------------------+----------+

| Id  | User        | Host                                       | db      | Command | Time | State                    | Info                                      | Progress |

+-----+-------------+--------------------------------------------+---------+---------+------+--------------------------+-------------------------------------------+----------+

|   1 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL                                      |    0.000 |

|   2 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL                                      |    0.000 |

|   3 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge coordinator | NULL                                      |    0.000 |

|   4 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL                                      |    0.000 |

|   5 | system user |                                            | NULL    | Daemon  | NULL | InnoDB shutdown handler  | NULL                                      |    0.000 |

| 360 | root        | xx.xx.xx.xx:51034                        | NULL    | Sleep   |  480 |                          | NULL                                      |    0.000 |

| 361 | root        | xx.xx.xx.xx:51035                        | NULL    | Sleep   |  480 |                          | NULL                                      |    0.000 |

| 679 | factset     | xx.xx.xx.xx:53736                          | factset | Sleep   |   30 |                          | NULL                                      |    0.000 |

| 680 | factset     | xx.xx.xx.xx.:53737                          | NULL    | Sleep   |   28 |                          | NULL                                      |    0.000 |

| 703 | root        | xxxxxxxxxxx:00000| NULL    | Query   |    0 | init                     | show processlist                          |    0.000 |

| 704 | factset     | localhost                                  | factset | Sleep   |    2 |                          | NULL                                      |    0.000 |

| 705 | factset     | localhost                                  | factset | Query   |    0 | NULL                     | ANALYZE TABLE rbics_v1_rb_sec_entity_hist |    0.000 |

| 706 | factset     | localhost                                  | factset | Query   |    0 | NULL                     | ANALYZE TABLE rbics_v1_rbics_bus_seg_item |    0.000 |

+-----+-------------+--------------------------------------------+---------+---------+------+--------------------------+-------------------------------------------+----------+

Comment by Ramesh [ 2018-06-20 ]

Snapshot after 10 minutes.

+-----+-------------+--------------------------------------------+---------+---------+------+--------------------------+-------------------------------------------+----------+

| Id  | User        | Host                                       | db      | Command | Time | State                    | Info                                      | Progress |

+-----+-------------+--------------------------------------------+---------+---------+------+--------------------------+-------------------------------------------+----------+

|   1 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL                                      |    0.000 |

|   2 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL                                      |    0.000 |

|   3 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge coordinator | NULL                                      |    0.000 |

|   4 | system user |                                            | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL                                      |    0.000 |

|   5 | system user |                                            | NULL    | Daemon  | NULL | InnoDB shutdown handler  | NULL                                      |    0.000 |

| 360 | root        | xx.xx.xx.xx:51034                        | NULL    | Sleep   |  577 |                          | NULL                                      |    0.000 |

| 361 | root        | xx.xx.xx.xx:51035                        | NULL    | Sleep   |  576 |                          | NULL                                      |    0.000 |

| 679 | factset     | xx.xx.xx.xx:53736                          | factset | Sleep   |   46 |                          | NULL                                      |    0.000 |

| 680 | factset     | xx.xx.xx.xx:53737                          | NULL    | Sleep   |   56 |                          | NULL                                      |    0.000 |

| 704 | factset     | localhost                                  | factset | Sleep   |  699 |                          | NULL                                      |    0.000 |

| 712 | factset     | localhost                                  | factset | Query   |    1 | NULL                     | ANALYZE TABLE evt_v1_ce_events_coverage   |    0.000 |

| 714 | root        | xx.xx.xx.xx::32776 | NULL    | Query   |    0 | init                     | show full processlist                     |    0.000 |

| 721 | factset     | localhost                                  | factset | Query   |   13 | NULL                     | ANALYZE TABLE hier_v1_hier_segrev_summary |    0.000 |

+-----+-------------+--------------------------------------------+---------+---------+------+--------------------------+-------------------------------------------+----------+

Comment by Elena Stepanova [ 2018-06-20 ]

First, I don't see any problem in these process lists, nothing is blocking here; In the first snapshot, two threads are just starting ANALYZE TABLE on that very second, and there is no other activity, nothing is waiting on locks or anything. In the second snapshot, two different threads are running ANALYZE TABLE, one has been running it for 1 second, another one for 13 seconds, which is not huge time if the table size is significant.

Secondly, server doesn't launch ANALYZE TABLE statements automatically. They must be coming from your application, monitoring or setup. If they are bothering you, get rid of them or reduce their frequency, because generally yes, ANALYZE TABLE may take much longer and take a lot of resources with engine-independent persistent statistics, if the tables are big and have more indexes. It is not worth running ANALYZE after each INSERT or UPDATE.

Comment by Ramesh [ 2018-06-20 ]

+--------------------------------------+-------------+

| Variable_name                        | Value       |

+--------------------------------------+-------------+

| innodb_stats_auto_recalc             | OFF         |

| innodb_stats_include_delete_marked   | OFF         |

| innodb_stats_method                  | nulls_equal |

| innodb_stats_modified_counter        | 0           |

| innodb_stats_on_metadata             | OFF         |

| innodb_stats_persistent              | OFF         |

| innodb_stats_persistent_sample_pages | 20          |

| innodb_stats_sample_pages            | 8           |

| innodb_stats_traditional             | OFF         |

| innodb_stats_transient_sample_pages  | 8           |

+--------------------------------------+-------------+

Like I mentioned above if I have the change use_stat_tables = NEVER (default) the process completes in 10 minutes

Comment by Elena Stepanova [ 2018-06-20 ]

And as I mentioned above, ANALYZE table is expectedly much more expensive with use_stat_tables than without it, so it's not surprising that if you have big tables, run DML on them and run ANALYZE after each insert or update, it will take much longer – it seems to be fairly obvious, you collect full complete statistics for the whole tables and indexes after each table update, how can it be not longer?

InnoDB stat values that you pasted above have nothing to do with use_stats_tables, they are completely unrelated, it's different statistics.

Comment by Elena Stepanova [ 2018-06-20 ]

That said, while it should be expected that ANALYZE takes (much) longer when persistent statistics are enabled and collected, I believe we have some issues with it using too much resources in general, I think this one can be added to them for general improvements in performance and efficiency.

Comment by Ramesh [ 2018-06-20 ]

Not sure what I am missing here. As enclosed above persistent stat collection is turned off as is auto recalc.

Can you point me to the setting that will turn off this behavior ?

Comment by Elena Stepanova [ 2018-06-20 ]

You need to read on engine-independent statistics: https://mariadb.com/kb/en/library/engine-independent-table-statistics/ – that's what you enable by setting use_stat_tables to PREFERABLE. It has nothing to do with innodb_stats_* options, you are mixing up different functionalities which are completely unrelated to each other. They use separate options, separate stat tables, separate algorithms.

For turning it off, I don't know what behavior you mean. To turn of engine-independent statistics, you need to set use_stat_tables to NEVER, but you already know that. To stop running ANALYZE, revise your application or whatever produces it, they are not coming from the server, something triggers them from outside.

[MDEV-16531] Insert starts analyze table caulsing the insert process to be blocked with use_stat_tables = PREFERABLY Created: 2018-06-19 Updated: 2018-06-20 Resolved: 2018-06-20
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Manipulation - Insert, Optimizer
Affects Version/s:	10.3.7
Fix Version/s:	N/A

[MDEV-16531] Insert starts analyze table caulsing the insert process to be blocked with use_stat_tables = PREFERABLY Created: 2018-06-19 Updated: 2018-06-20 Resolved: 2018-06-20