Gdb basics for my sql db as (percona live europe 2019)

imikay

2019/10/04 发布于 技术 分类

文字内容
1. GDB Basics for MySQL DBAs Valerii Kravchuk, Principal Support Engineer, MariaDB vkravchuk@gmail.com 1
2. Who am I? Valerii (aka Valeriy) Kravchuk: ● ● ● ● ● ● ● ● MySQL Support Engineer in MySQL AB, Sun and Oracle, 2005-2012 Principal Support Engineer in Percona, 2012-2016 Principal Support Engineer in MariaDB Corporation since March 2016 http://mysqlentomologist.blogspot.com - my blog about MySQL (a lot about MySQL bugs, but some HowTos as well) https://www.facebook.com/valerii.kravchuk - my Facebook page, a lot about MySQL (mostly bugs…) http://bugs.mysql.com - my personal playground @mysqlbugs #bugoftheday Community Contributor of the Year 2019 www.percona.com 2
3. GDB for DBAs? GDB, gdb, GNU Debugger www.percona.com 3
4. Usually gdb is used by developers, to study core dumps... ● Mostly like this: gdb /path/to/mysqld /path/to/coredump ● Bug #76432 - “handle_fatal_signal (sig=11) in __strlen_sse2_pminub on CHANGE MASTER” Bug #69898 - “change_master() invokes ha_innobase::truncate() in a DML transaction” - a lot of useful gdb-related reading inside (check how Marko uses call rec_print_old(stderr,$8.frame+0x16e) etc) See also related Bug #69825 and how bug reporter attached full backtrace in related Bug #73155 Bug #96224 - “The MTR case innodb.innodb_corrupt_bit get a randomly coredump” for a more recent example (fixed in upcoming 8.0.18). Developers and users with QA mindset often use debug builds, hit debug assertions etc, for MySQL DBAs to never hit the problems :) ● ● www.percona.com 4
5. ...or (surprise!) to debug their code ● Running “under gdb”: gdb --args bin/mysqlcheck -u root -p -S/tmp/mysql.sock --all-databases --optimize (gdb) thread apply all bt ● Attaching gdb to the process already running: gdb -p `pidof mysqld` ● Some examples: ○ ○ Percona Server Bug #1483251 - “savepoints and replication”. Check how Vlad Lesin uses backtrace to understand the reason of the bug Percona Server Bug #1426345 - “Prepared statements in stored procedures crash query response time plugin”. Check how Nickolay Ihalainen pinpoint the root cause of the bug by comparing values of various variables in gdb www.percona.com 5
6. More examples here on how MariaDB developers use gdb ● MDEV-13797 - InnoDB may hang if shutdown is initiated soon after startup, while rolling back recovered incomplete transactions ● MDEV-12052 - our buildbot tries to get backtrace for all threads for crash ● MDEV-12413 - be ready to run some gdb commands when you report bugs ● MDEV-14051 - this is how developers use “advanced” gdb. See Bug #88150 ● MDEV-13787 - real crash (fixed) ● MDEV-11044 - dumping pages etc, can't repeat, but still some useful details www.percona.com 6
7. But production DBAs also may benefit from gdb! ● First of all, gdb allows to inspect the values of variables in the mysqld process memory, and thus you can check some details about user threads and statements executed that may not be easily available via SQL (missing feature, can’t connect, hangs, bugs) ● Also gdb allows to change the values of variables, both global and session ones (missing feature, read only ones) directly or indirectly (by calling functions in the code) ● Finally, attaching gdb allows to get a backtrace for further study of the root cause of the problem www.percona.com 7
8. GDB for DBAs? ● So, eventually DBAs I work with agree to install gdb (and -debuginfo, -dbg etc packages to get symbolic information for their binaries), and even enable coredumps on their production servers ● They try to use gdb to get backtraces and more in testing/staging environment ● Sending gdb outputs (backtraces of all threads in case of any coredump) becomes a part of their SOP… ● And these help A LOT! www.percona.com 8
9. Domas is famous for these tricks... ● http://dom.as/2009/02/15/poor-mans-contention-profiling/ this is what ended up as http://poormansprofiler.org/ and pt-pmp ● http://dom.as/2009/07/30/evil-replication-management/ mysql> system gdb -p $(pidof mysqld) -ex "set opt_log_slave_updates=1" -batch ● http://dom.as/2010/01/02/read-ahead/ gdb -ex "set srv_startup_is_before_trx_rollback_phase=1" -batch -p $(pidof mysqld) ● http://dom.as/2009/12/29/when-bad-things-happen/ www.percona.com 9
10. More examples of gdb use for MySQL DBAs ● ● ● ● ● ● Remember the names: Domas Mituzas, Shane Bester, Roel Van De Paar, Mark Callaghan, Aurimas Mikalauskas, Zhai Weixiang, ... http://www.percona.com/blog/2012/09/09/obtain-last-executed-statement-fromoptimized-core-dump/ http://www.percona.com/blog/2013/11/11/how-to-extract-all-running-queries-inc luding-the-last-executed-statement-from-a-core-file/ http://mysqlbugs.blogspot.com.au/2012/09/how-to-obtain-all-executing-queries. html http://www.percona.com/blog/2010/03/23/too-many-connections-no-problem/ All the above (or examples from any links shared here) are not recipes for current/your exact version - more like inspiration for checks. They may not apply literally to your version of MySQL! www.percona.com 10
11. What MySQL DBA can do with gdb? ● Check stack traces (and variables), per thread: thread apply all bt [full] ● Print variables, up to complex one (MySQL 5.6 times): thread 1 print do_command::thd->query_string.string.str ● In MySQL 5.7: (gdb) p dispatch_command::com_data->com_query.query Set new values for variables (global and per thread, even those formally read-only in MySQL while it’s running): set max_connections=5000 set opt_log_slave_updates=1 ● Call functions (that may do complex changes): call rpl_filter->add_do_db(strdup("hehehe")) ● ● ● ● Set breakpoints and watchpoints and get information when shit happens Use gdb as a command line utility (-batch), use Python scripting & more… All these may not work, fail, hang, crash, produce obscure errors… For this to work well DBA has to read and understand the source code www.percona.com 11
12. pt-pmp (Poor Man’s Profiler) ● https://www.percona.com/doc/percona-toolkit/LATEST/pt-pmp.html pt-pmp [-i 1] [-s 0] [-b mysqld] [-p pidofmysqld] [-l 0] [-k file] [--version] ● ● ● ● ● It is based on original idea by Domas, http://poormansprofiler.org/. I use the awk code from the above to analyse backtraces of all threads. When mysqld hangs or is slow, you can get some insight quickly - use pt-pmp to find out why (or what threads mostly do at the moment). For example, see Bug #92108 (fixed in 5.7.25+, binlog access vs P_S query), Yet another example of how it is used: Bug #78277 - InnoDB deadlock, thread stuck on kernel calls from transparent page compression, “Open” Use in production as a last resort (may hang mysqld, --SIGCONT) pt-pmp surely slows server down :) Hints:https://bugs.launchpad.net/percona-toolkit/+bug/1320168 - partial workaround ○ Use quickstack instead of gdb (check this discussion and this post) www.percona.com 12
13. Multi-threaded mysqld process and gdb ● process/thread/frame concepts: (gdb) thread 2 [Switching to thread 2 (Thread 0x7fe771550700 (LWP 2544))] #0 0x0000000000605774 in Item_func_numhybrid::val_int ( this=) at /home/openxs/bzr2/percona-5.6/sql/item_func.cc:1013 1013 } (gdb) bt ... #12 0x00000000006f8a45 in dispatch_command (command=COM_QUERY, thd=0x7fe760f94000, packet=0x7fe77154fac0 "", packet_length=0) at /home/openxs/bzr2/percona-5.6/sql/sql_parse.cc:1434'>parse.cc:1434 ... (gdb) frame 12 #12 0x00000000006f8a45 in dispatch_command (command=COM_QUERY, thd=0x7fe760f94000, packet=0x7fe77154fac0 "", packet_length=0) at /home/openxs/bzr2/percona-5.6/sql/sql_parse.cc:1434'>parse.cc:1434 warning: Source file is more recent than executable. 1434 mysql_parse(thd, thd->query(), thd->query_length(), &parser_state); (gdb) p thd->query_string.string.str $2 = 0x7fe75301d010 "select benchmark(5", '0' , ", 2*2)" ● https://sourceware.org/gdb/onlinedocs/gdb/Variables.html www.percona.com 13
14. THD structure grep -rn THD sql/sql_class.h class THD :public MDL_context_owner, public Statement, public Open_tables_state HASH user_vars; // hash for user vars struct system_variables variables; // Changeable local vars struct system_status_var status_var;// Per thread stat vars struct system_status_var *initial_status_var; /* used by show status */ Security_context main_security_ctx; ... CSET_STRING query_string; // inherited from Statement… ... www.percona.com 14
15. Checking core dump ● Make sure you know how to run gdb commands on core file: gdb -ex "set pagination 0"\ -ex "thread apply all bt"\ ... -batch `which mysqld` core. > backtrace.txt ● Make sure you know how to get core when mysqld crashes: http://www.percona.com/blog/2011/08/26/getting-mysql-core-file-on-linux/ ● In case of troubles with SELinux, systemd etc read this www.percona.com 15
16. Attaching to alive mysqld This is how it goes: [root@centos openxs]# mysql -uroot -e "show variables like 'innodb_autoinc_lock_mode'" +--------------------------+-------+ Variable_name Value +--------------------------+-------+ innodb_autoinc_lock_mode 0 +--------------------------+-------+ [root@centos openxs]# mysql -uroot -e "set global innodb_autoinc_lock_mode=1" ERROR 1238 (HY000) at line 1: Variable 'innodb_autoinc_lock_mode' is a read only variable [root@centos openxs]# gdb -ex "set innobase_autoinc_lock_mode=1" -batch -p `pidof mysqld` … [Thread debugging using libthread_db enabled] 0x00007ff31d6830d3 in poll () from /lib64/libc.so.6 … check the variable value again now [root@centos openxs]# ps aux grep mysqld [root@centos openxs]# kill -SIGCONT `pidof mysqld` www.percona.com 16
17. Finding processlist thread id with gdb http://mysqlentomologist.blogspot.fi/2017/07/how-to-find-pro cesslist-thread-id-in-gdb.html ● It may depend on MySQL version (changes in 5.7+) ● Basic idea - check threads one by one, find frame where thd is defined, print: (gdb) thread 2 (gdb) p do_command::thd-'>command::thd-'>command::thd-'>command::thd->thread_id ● In 5.7+ there is some difference: (gdb) thread 7 (gdb) p do_command::thd-'>command::thd-'>command::thd-'>command::thd->m_thread_id (gdb) p do_command::thd-'>command::thd-'>command::thd-'>command::thd->m_main_security_ctx ● Even more difference if you want to automate looping through threads… (more C++, singletons vs variables) www.percona.com 17
18. Checking session variables with gdb http://mysqlentomologist.blogspot.fi/2017/08/how-to-find-valu es-of-session-variables.html ● It started with a “simple” question: how to find out from the core dump if the session behind the crashing thread had mrr=ON in the optimizer_switch? ● Basic idea is simple, it’s in thd->variables, somehow: (gdb) p do_command::thd-'>command::thd-'>command::thd-'>command::thd->variables->optimizer_switch (gdb) p global_system_variables->optimizer_switch ● Then see defines in sql/sql_const.h: #define OPTIMIZER_SWITCH_MRR (1ULL << 6) ● Then we can print it better: p do_command::thd-'>command::thd-'>command::thd-'>command::thd->variables->optimizer_switch & (1<<6) p /t do_command::thd-'>command::thd-'>command::thd-'>command::thd->variables->optimizer_switch www.percona.com 18
19. Checking user variables with gdb http://mysqlentomologist.blogspot.com/2017/08/how-to-find-v alues-of-user-variables.html ● Basically it’s somewhere there, in thd: p do_command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd->user_vars ● But it’s not a simple array, it’s a HASH: (gdb) p my_hash_element(&(do_command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd->user_vars), 1) (gdb) set $uvar = (user_var_entry *)(my_hash_element(&(do_command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd->user_vars), 1)) (gdb) p $uvar (gdb) p *$uvar ● We can also get element by name: (gdb) set $uvar=(user_var_entry *)(my_hash_search(&(do_command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd-'>command::thd->user_vars), "e", strlen("e"))) (gdb) p *((my_decimal *)$uvar->m_ptr) www.percona.com 19
20. HASH structures in MySQL http://mysqlentomologist.blogspot.com/2017/08/more-on-stu dying-mysql-hashes-in-gdb.html ● ● ● HASH structure is used everywhere in MySQL, from keyring to UDFs and table cache, to replication and NDB Cluster, with everything in between Check include/hash.h: typedef struct st_hash { ... ulong records; DYNAMIC_ARRAY array; ... } HASH; This gives us a way eventually to dump data without calling functions: (gdb) set $uvars=&(do_command::thd->user_vars) ... (gdb) p *(user_var_entry *) (((HASH_LINK*)((&($uvars->array))->buffer) + (0))->data) www.percona.com 20
21. Some gdb versions have Python, and this helps ● ● ● ● ● ● If you like and know Python and have gdb linked with it (try py print(1+1))... Use ~/.gdbinit file for complex Python macros http://mysqlbugs.blogspot.com/2012/09/how-to-obtain-all-executing-queries. html - Shane Bester on simplified navigation over threads, nice printing of selected values etc, https://mariadb.org/duel-gdb-vs-linked-lists-trees-hash-tables/ - “Duel: gdb vs. linked lists, trees, and hash tables”. Sergei Golubchik on simplified way to apply “something” (like print) to all/selected items of arrays, linked lists etc. Check https://github.com/vuvova/gdb-tools https://mariadb.org/making-life-prettier-gdb-prettyprinting-api/ - “Making life prettier with gdb PrettyPrinting API”. Sergei Golubchik on how to use Python classes to pretty print almost anything inside MySQL code in gdb Make sure to check if you have Python 2 or 3 in gdb! (pip vs pip3 etc): [openxs@fc23 ~]$ ldd `which gdb` grep pyt libpython3.5m.so.1.0 => /lib64/libpython3.5m.so.1.0 (0x00007fee3957d000) www.percona.com 21
22. Some things to check before relying on gdb ● Check that gdb is installed and works ● Check that MySQL/Percona/MariaDB server you use has symbolic information for gdb ● DBA may need to get sudo/root access ● Make sure you know how to enable core dumps on your Linux, and know where they are located (it may become complicated) ● Install pt-pmp (or entire Percona Toolkit) https://www.percona.com/get/pt-pmp - and check it ● Check all “recipes” at staging environment first! ● It’s probably a good idea to create useful ~/.gdbinit www.percona.com 22
23. Results of using gdb to study MySQL internals ● ● ● Immediate DBA problems solved without restart etc Better understanding of how MySQL works! Blog posts, talks, presentations: ○ ○ ○ ○ ○ ○ ● http://mysqlentomologist.blogspot.com/2016/01/exploring-metadata-locks-with-gdb-first.html http://mysqlentomologist.blogspot.com/2016/01/exploring-metadata-locks-with-gdb.html http://mysqlentomologist.blogspot.com/2016/01/exploring-metadata-locks-with-gdb-how.html http://mysqlentomologist.blogspot.com/2015/03/using-gdb-to-understand-what-locks-and_31.html http://mysqlentomologist.blogspot.com/2015/04/using-gdb-to-understand-what-locks-and.html http://www.slideshare.net/valeriikravchuk1/understanding-innodb-locks-and-deadlocks Bug reports and documentation requests to make MySQL and its manual better: ○ ○ ○ ○ ○ Bug #79665 - Manual does NOT explain locks set by INSERT ... ON DUPLICATE KEY UPDATE properly Bug #77390 - Manual does not explain a "deadlock" case of online ALTER Bug #76588 - Metadata lock is NOT released when SELECT completes in case of autocommit=0 Bug #76563 - Manual does NOT explain when exactly AUTO-INC lock is set for "bulk inserts" Bug #76533 - AUTO_INC lock seems to be NOT set for INSERT INTO t(val) SELECT val FROM t www.percona.com 23
24. Is gdb an ultimate answer for MySQL DBA? No, usually it is a temporary, one time solution or last resort Instead you may (or should, whenever possible): ● Use real profilers at OS level (like perf) ● Use troubleshooting tools at MySQL level (like P_S) ● Implement missing feature (like setting some variable dynamically) or request it from developers ● Consider upgrade to version or fork that already has a feature you miss ● Plan your work and do maintenance properly ● Read the manual and source code www.percona.com 24
25. Thank you! Questions and Answers? Please, report bugs at: http://bugs.mysql.com https://jira.mariadb.org www.percona.com 25