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

[Sveta Smirnova]

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.

[Laurynas Biveinis (laurynas-biveinis)]

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)]

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.

[Laurynas Biveinis (laurynas-biveinis)]

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

[Sveta Smirnova (svetasmirnova)]

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)]

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;

[Laurynas Biveinis (laurynas-biveinis)]

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)]

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

[Laurynas Biveinis (laurynas-biveinis)]

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

[Laurynas Biveinis (laurynas-biveinis)]

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

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/1977
https://github.com/percona/percona-server/pull/1978

[Shahriyar Rzayev (rzayev-sehriyar)]

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