Release lock on table statistics after query plan created | Stop ANALYZE TABLE from flushing table definition cache

Bug #1704195 reported by Sveta Smirnova on 2017-07-13
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Won't Fix
Wishlist
Unassigned
5.6
Fix Released
Wishlist
Laurynas Biveinis
5.7
Fix Released
Wishlist
Laurynas Biveinis

Bug Description

Description:
Under concurrent environment running ANALYZE TABLE could lead to situation when hundreds of tables are in state "Waiting for table flush".

How to repeat:
Read https://www.percona.com/blog/2013/02/27/mysql-optimizer-analyze-table-and-waiting-for-tabl...

Suggested fix:
Implement new type of lock which holds statistics only and release it immediately after Optimizer creates execution plan.

tags: added: performance
summary: - Release lock on table statistics after query plan created
+ Release lock on table statistics after query plan created | stop ANALYZE
+ TABLE from flushing table definition cache
summary: - Release lock on table statistics after query plan created | stop ANALYZE
+ Release lock on table statistics after query plan created | Stop ANALYZE
TABLE from flushing table definition cache

Experimental 5.6 fix (applies only to non-partitioned InnoDB tables) at https://github.com/laurynas-biveinis/percona-server/tree/bug1704195-5.6-trunk

Sveta Smirnova (svetasmirnova) wrote :

Build fails for me with

[ 28%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/api/api0api.cc.o
In file included from /home/sveta/src/percona-server-laurynas/storage/innobase/include/dict0dict.ic:30:0,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/dict0dict.h:1846,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/rem0rec.ic:28,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/rem0rec.h:985,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/btr0sea.h:31,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/que0que.h:32,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/api0misc.h:32,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/api/api0api.cc:38:
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.ic: In function ‘bool fsp_flags_is_valid(ulint)’:
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:44:39: error: left shift of negative value [-Werror=shift-negative-value]
 #define FSP_FLAGS_WIDTH_POST_ANTELOPE 1
                                       ^
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:86:13: note: in expansion of macro ‘FSP_FLAGS_WIDTH_POST_ANTELOPE’
   ((~(~0 << FSP_FLAGS_WIDTH_POST_ANTELOPE)) \
             ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:107:13: note: in expansion of macro ‘FSP_FLAGS_MASK_POST_ANTELOPE’
   ((flags & FSP_FLAGS_MASK_POST_ANTELOPE) \
             ^~~~~~~~~~~~~~~~~~~~~~~~~~~~
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.ic:61:24: note: in expansion of macro ‘FSP_FLAGS_GET_POST_ANTELOPE’
  ulint post_antelope = FSP_FLAGS_GET_POST_ANTELOPE(flags);
                        ^~~~~~~~~~~~~~~~~~~~~~~~~~~
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:46:35: error: left shift of negative value [-Werror=shift-negative-value]
 #define FSP_FLAGS_WIDTH_ZIP_SSIZE 4
                                   ^
...

Cmake options:

cmake /home/sveta/src/percona-server-laurynas -DCMAKE_INSTALL_PREFIX=$HOME/build/ps-5.7-george -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DDOWNLOAD_BOOST_TIMEOUT=1800 -DWITH_BOOST=./boost -DENABLE_DTRACE=0 -DWITH_ROCKSDB=0 -DWITH_RAPID=0

$ gcc --version
gcc (Ubuntu/Linaro 6.3.0-18ubuntu2~16.04) 6.3.0 20170519
Copyright (C) 2016 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

The error is not related to the branch; please try with an older compiler

Sveta Smirnova (svetasmirnova) wrote :

With gcc 5.4.1-2:

[ 54%] Building CXX object sql/CMakeFiles/sql.dir/gcalc_tools.cc.o
/home/sveta/src/percona-server-laurynas/sql/gcalc_tools.cc: In member function ‘int Gcalc_function::count_internal()’:
/home/sveta/src/percona-server-laurynas/sql/gcalc_tools.cc:225:18: error: suggest parentheses around operand of ‘!’ or change ‘&’ to ‘&&’ or ‘!’ to ‘~’ [-Werror=parentheses]
         result= !result & next_res;
                  ^
cc1plus: all warnings being treated as errors
sql/CMakeFiles/sql.dir/build.make:385: recipe for target 'sql/CMakeFiles/sql.dir/gcalc_tools.cc.o' failed
make[2]: *** [sql/CMakeFiles/sql.dir/gcalc_tools.cc.o] Error 1
CMakeFiles/Makefile2:5638: recipe for target 'sql/CMakeFiles/sql.dir/all' failed
make[1]: *** [sql/CMakeFiles/sql.dir/all] Error 2
Makefile:160: recipe for target 'all' failed
make: *** [all] Error 2

Sveta Smirnova (svetasmirnova) wrote :

Compiled with -DMYSQL_MAINTAINER_MODE=OFF, but still seeing the issue:

mysql> show processlist;
+----+------+-----------------+------+------------+------+-------------------------+--------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------------+------+------------+------+-------------------------+--------------------------------------------+-----------+---------------+
| 2 | root | localhost:59258 | test | Query | 0 | init | show processlist | 0 | 0 |
| 3 | root | localhost:59262 | test | Query | 20 | User sleep | select sleep(1) from goods limit 1000, 300 | 20 | 0 |
| 4 | root | localhost:59264 | test | Field List | 11 | Waiting for table flush | | 0 | 0 |
+----+------+-----------------+------+------------+------+-------------------------+--------------------------------------------+-----------+---------------+
3 rows in set (0,01 sec)

Test case I use:

1. Load dump from the link which will be provided in separate comment.
2. In session 1: select sleep(1) from goods limit 1000, 300;
3. In session 2: analyze table goods;
4. In session 3: either connect to the database or select * from goods order by id limit 10;

I suspect that you built the default branch after "git clone" instead of the bugfix one, this could also explain the previous compilation issues. Please confirm that you have checked out "bug1704195-5.6-trunk" before building (5eee06f372389168c37941dc075f161f43cd5c0f)

Sveta Smirnova (svetasmirnova) wrote :

My bad! Fix works fine. Thought I had to compile with option -DWITH_TOKUDB=0 (not related to the fix)

Dump file for the reference: https://drive.google.com/a/percona.com/file/d/0B67lYkmv0Zcsel9YNGxrYnpWNUU/view?usp=sharing

The experimental fix at https://github.com/laurynas-biveinis/percona-server/tree/bug1704195-5.6-trunk has been extended for partitioned InnoDB tables too.

Enabling this for TokuDB exposes https://jira.percona.com/browse/TDB-83

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2503

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.