Transcript Slide 1
1 <Insert Picture Here> When things go wrong: how to find SQL error Sveta Smirnova Principle Technical Support Engineer, MySQL @ Oracle Part 2. Complex things • • • • Crashes Problems repeatable only in production/high load Sandbox Best practices <Insert Picture Here> 3 Crashes • Symptoms: – «Lost connection to MySQL server» returned to the application – Message in the error log file • Check error log file • If message contains a query try it using MySQL command line client • If crash is repeatable fix the query 4 Crashes • If there is no query try general query log: it will contain query unless this was crash during writing to general query log • Check backtrace for a clue about what can cause the problem • Create core file and analyze it using debugger if needed 5 Backtrace help • • • • • • stack_bottom = 0x450890f0 thread_stack 0x40000 /5.1.39/bin/mysqld(my_print_stacktrace+0x2e)[0x8ac81e] /5.1.39/bin/mysqld(handle_segfault+0x322)[0x5df502] /lib64/libpthread.so.0[0x3429e0dd40] /5.1.39/bin/mysqld(_ZN6String4copyERKS_+0x16)[0x5d9876] /5.1.39/bin/mysqld(_ZN14Item_cache_str5storeEP4Item+0xc9)[0x5 2ddd9] • /5.1.39/bin/mysqld(_ZN26select_singlerow_subselect9send_dataE R4ListI4ItemE+0x45)[0x5ca145] 6 Backtrace help • • • • • /5.1.39/bin/mysqld[0x6386d1] ... Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x6a39e60 = select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`) • thd->thread_id=2 • thd->killed=NOT_KILLED 7 Crashes • If problem is not particular query • Analyze other messages in the log • If there is a clue fix the problem 8 Too greedy buffers • • • • • • • key_buffer_size=235929600 read_buffer_size=4190208 max_used_connections=17 max_connections=2048 threads_connected=13 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 21193712 K • ----• 21193712K ~= 20G 9 Crashes • Check if you allocated reasonable values for buffers • Set realistic value of max_connections based on your operating system resources • Use monitoring tools of your operating system to find which application use enormous amount of resources which lead to crashes of MySQL server. 10 Production applications • Be sure you have error logging turned on • In case of a problem check error log first • Turn InnoDB Monitoring to on to have information about transactions in error log file • Use slow query log to have information about all queries which run slowly • Tune time of query considered slow 11 Production applications • Once problem query is found try to run it isolated. If needed in test environment. • If problem is not repeatable analyze if this is effect of options or concurrency. • If problem is options adjust them in such a way so problem is not repeatable 12 Concurrency • Analyze if this is effect of locks: – SHOW PROCESSLIST – ENGINE INNODB STATUS – PERFORMANCE_SCHEMA.*_instances tables • If yes rewrite application so queries which lock each other don't run concurrently • If not try you can enable core files and use kill to create a core or attach debugger to the running process 13 Replication errors • IO errors – Check error log first – Check connection and network interface – Try to connect to master using replication credentials • All replication SQL errors can be checked using command line client too – They are single threaded, therefore it is usually easy to identify what caused error – SHOW SLAVE STATUS usually contains message about SQL error 14 Replication SQL errors • Check error log for a query which caused the problem • Check if table definitions are same on both master and slave • Check if table on master and slave are synchronised • Compare master and slave options. If there are different try these you have on master 15 Replication SQL errors • Get query as slave gets it from master binary log • mysqlbinlog binlog_name.00000X • Use option –verbose to decode row events into SQL statements • Analyze how real query would be executed on master 16 Sandbox • Create environment same as production server, but which you can freely change and even destroy – Same server version – Same options 17 Sandbox • MySQL Sandbox • https://launchpad.net/mysql-sandbox • Easiest method to deploy copy of your production environment • You just need package of MySQL-version-you-use***.tar.gz 18 • • • • • • • Sandbox $make_sandbox mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz unpacking /mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz ... The MySQL Sandbox, version 3.0.05 (C) 2006,2007,2008,2009 Giuseppe Maxia installing with the following parameters: upper_directory = /users/ssmirnova/sandboxes 19 Sandbox • ... • ........ sandbox server started • Your sandbox server was installed in $HOME/sandboxes/msb_5_4_2 20 Sandbox • Load dump of your production database into your sandbox • Now you have working copy you can experiment with • To dump only part of data: – CREATE TABLE test_problem LIKE problem; – INSERT INTO test_problem SELECT FROM problem WHERE [condition which exists in the original query, but executed properly] 21 Sandbox • You also can create «sandbox» database in production server: – – – – CREATE DATABASE sandbox USE sandbox CREATE TABLE problem LIKE production.problem INSERT INTO problem SELECT * FROM production.problem [WHERE ...] 22 Sandbox • Make sure you don't kill production server if testing in «sandbox» database • For scenarios which can kill server use separate MySQL server or, better, physical server 23 Links • • • • http://sql-error.microbecal.com/en/ http://forge.mysql.com/wiki/MySQL_Proxy https://launchpad.net/mysql-sandbox http://dev.mysql.com/ 24 ? 25 Thank you! 26 27 The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 28