Re: Which test suites are valid for --extern option in mysql-test-run?
Those tests are typically used by people developing the mysqld server. The packages distributed have already had the tests ran on them. I'm not sure how much value it will provide unless you're modifying mysqld. I don't have a great answer but my best guess is that extern can't be used with tests that start or stop mysqld servers like the replication tests. On Sat, Nov 24, 2012 at 12:16 AM, Tianyin Xu wrote: > Hi, all, > > I wanna test my running MySQL server. According to the README in the > "mysql-test" directory, "If you want to use an already running MySQL server > for specific tests, use the --extern option to mysql-test-run". > > For example, mysql-test-run --extern alias > > But the README also pointed out that "some tests cannot run with an > external server". > > My question is how to know which test suites in "t" directory are valid for > --extern option? Without knowing this, I cannot judge whether a test fails > is because it's not supported or "real" problems. > > Thanks a lot! > Tianyin > > -- > Tianyin XU, > http://cseweb.ucsd.edu/~tixu/ -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Query Plan Analyzer
Yes and no. It takes a lot of experience to get beyond what EXPLAIN gives us. EXPLAIN says what it does, but usually one wants to know "what to do". To that end, I have written some tips: http://mysql.rjweb.org/doc.php/ricksrots http://mysql.rjweb.org/doc.php/index1 But if you have one of the classic "problems", you need to think out of the box. Examples: EAV, Pagination, Big deletes, nearest pizza parlors, lat/long searching, latest news articles, UUID indexing, picking a random row. The above links lead to other pages that discuss efficient approaches to those gnarly tasks. I find Profiling to be useless, since it seems to always have 95% of the time is in "copying"; this says nothing actionable. EXPLAIN is useful in confirming that the query will be executed in the way you think it ought to be -- Using this index or that, hitting the tables in a particular order, "Using index" (meaning that the data did not need to be touched), "Using join buffer" (often bad). I repeatedly tell people that "using filesort/temp-table" is not necessarily evil; think what it must take to do GROUP BY z ORDER BY x If you want an analysis of a particular query, please provide SHOW CREATE TABLE -- for engine, datatypes, and indexes SHOW TABLE STATUS -- for size EXPLAIN SELECT -- for what the optimizer's plan EXPLAIN PARTITIONS SELECT -- if partitioned. Oh, another note... Akiban's EXPLAIN is far more detailed than MySQL's. (But then, it is doing some tricky things.) > -Original Message- > From: Cabbar Duzayak [mailto:cab...@gmail.com] > Sent: Thursday, November 22, 2012 12:45 AM > To: mysql@lists.mysql.com > Subject: Re: Query Plan Analyzer > > Is this a joke? > > On Thu, Nov 22, 2012 at 10:20 AM, Zhigang Zhang > wrote: > > > By experience! > > > > -Original Message- > > From: Cabbar Duzayak [mailto:cab...@gmail.com] > > Sent: Thursday, November 22, 2012 3:13 PM > > To: mysql@lists.mysql.com > > Subject: Query Plan Analyzer > > > > Hi All, > > > > Is there a way of looking at how mysql builds the query plan and > > executes it for a given query? EXPLAIN is definitely a useful tool, > > but it is not exact (shows approximations as far as I can see), > > furthermore I want something like how the predicates were applied > > (before or after JOINS), which indexes were used, etc. > > > > Basically, Oracle's EXPLAIN PLAN and SQL TRACE do this, and it is > very > > very useful for optimizing queries. Don't get me wrong, I love mysql, > > but with mysql, you optimize queries more by trial and error, instead > > of understanding what exactly is going on. > > > > Thanks a ton. > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load?
Hi Tom, I am assuming nothing relevant shows up in dmesg, right? I have experienced random crashes like that and most of them turned to be HW issues - hard disk and memory banks related. Is it a HW RAID? Have you tried looking at the controller logs? (Megacli). And yes, corrupted tables would fail when restoring them (or even when backuping them). Good luck! Manuel 2012/11/26, Rick James : > Nothing looks bad. > 96G for the buffer_pool is bigger than I have experienced, but I know of no > reason for it to fail (given that you have 128GB of RAM). > >> -Original Message- >> From: Tom [mailto:livefortheda...@gmail.com] >> Sent: Wednesday, November 21, 2012 5:17 PM >> To: mysql@lists.mysql.com >> Subject: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf >> cause mysqld to randomly crash on high load? >> >> We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - >> running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large >> InnoDB tables, over 50+ gigs, randomly after a few hours of heavy load, >> mysql does a Signal 11 and crashes. >> >> We have tried to move hardware. Doing a full dump (but not a restore >> yet) gives no issues. Usually on corrupted tables, a dump would fail >> no? >> >> Below is the crash log and my.cnf . >> >> --- >> 12:45:4 UTC - mysqld got signal 11 ; >> This could be because you hit a bug. It is also possible that this >> binary or one of the libraries it was linked against is corrupt, >> improperly built, or misconfigured. This error can also be caused by >> malfunctioning hardware. >> We will try our best to scrape up some info that will hopefully help >> diagnose the problem, but since we have already crashed, something is >> definitely wrong and this may fail. >> >> key_buffer_size=536870912 >> read_buffer_size=131072 >> max_used_connections=324 >> max_threads=200 >> thread_count=308 >> connection_count=308 >> It is possible that mysqld could use up to key_buffer_size + >> (read_buffer_size + sort_buffer_size)*max_threads = >> 965187 K bytes of memory >> Hope that's ok; if not, decrease some variables in the equation. >> >> Thread pointer: 0x7fc7eb1b5040 >> Attempting backtrace. You can use the following information to find out >> where mysqld died. If you see no messages after this, something went >> terribly wrong... >> stack_bottom = 7fadf6abfe60 thread_stack 0x3 >> /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fc758522759] >> /usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fc7583e9ae3] >> /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fc75713bcb0] >> /usr/sbin/mysqld(+0x6671b0)[0x7fc75863a1b0] >> /usr/sbin/mysqld(+0x61d6b9)[0x7fc7585f06b9] >> /usr/sbin/mysqld(+0x630d12)[0x7fc758603d12] >> /usr/sbin/mysqld(+0x6319c2)[0x7fc7586049c2] >> /usr/sbin/mysqld(+0x631d85)[0x7fc758604d85] >> /usr/sbin/mysqld(+0x626e7d)[0x7fc7585f9e7d] >> /usr/sbin/mysqld(+0x633cea)[0x7fc758606cea] >> /usr/sbin/mysqld(+0x6347e2)[0x7fc7586077e2] >> /usr/sbin/mysqld(+0x624426)[0x7fc7585f7426] >> /usr/sbin/mysqld(+0x610871)[0x7fc7585e3871] >> /usr/sbin/mysqld(+0x5d4cb0)[0x7fc7585a7cb0] >> /usr/sbin/mysqld(+0x5b7c9c)[0x7fc75858ac9c] >> /usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_ra >> nge+0x24)[0x7fc7583e9fe4] >> /usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x7fc7584a3c8 >> c] >> /usr/sbin/mysqld(+0x4e9195)[0x7fc7584bc195] >> /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x71)[0x7fc7582f >> 1741] >> /usr/sbin/mysqld(+0x32f025)[0x7fc758302025] >> /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4a5)[0x7fc758311155] >> /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_E >> S2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sele >> ct_lex+0x130)[0x7fc75830d000] >> /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x >> 7fc758312f5c] >> /usr/sbin/mysqld(+0x2f66b4)[0x7fc7582c96b4] >> /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16d8)[0x7fc7582d1118] >> /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fc758 >> 2d5daf] >> /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 >> 80)[0x7fc7582d7200] >> /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fc75837b7a >> d] >> /usr/sbin/mysqld(handle_one_connection+0x50)[0x7fc75837b810] >> /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fc757133e9a] >> /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc756864cbd] >> >> Trying to get some variables. >> Some pointers may be invalid and cause the dump to abort. >> Query (7faa4c18e440): is an invalid pointer Connection ID (thread ID): >> 2286 >> Status: NOT_KILLED >> >> The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html >> contains information that should help you find out what is causing the >> crash. >> 121120 12:48:48 [Note] Plugin 'FEDERATED' is disabled. >> 121120 12:48:48 InnoDB: The InnoDB memory heap is disabled >> 121120 12:48:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins >> 121120 12:48:48 InnoDB: Comp
RE: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load?
Nothing looks bad. 96G for the buffer_pool is bigger than I have experienced, but I know of no reason for it to fail (given that you have 128GB of RAM). > -Original Message- > From: Tom [mailto:livefortheda...@gmail.com] > Sent: Wednesday, November 21, 2012 5:17 PM > To: mysql@lists.mysql.com > Subject: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf > cause mysqld to randomly crash on high load? > > We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - > running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large > InnoDB tables, over 50+ gigs, randomly after a few hours of heavy load, > mysql does a Signal 11 and crashes. > > We have tried to move hardware. Doing a full dump (but not a restore > yet) gives no issues. Usually on corrupted tables, a dump would fail > no? > > Below is the crash log and my.cnf . > > --- > 12:45:4 UTC - mysqld got signal 11 ; > This could be because you hit a bug. It is also possible that this > binary or one of the libraries it was linked against is corrupt, > improperly built, or misconfigured. This error can also be caused by > malfunctioning hardware. > We will try our best to scrape up some info that will hopefully help > diagnose the problem, but since we have already crashed, something is > definitely wrong and this may fail. > > key_buffer_size=536870912 > read_buffer_size=131072 > max_used_connections=324 > max_threads=200 > thread_count=308 > connection_count=308 > It is possible that mysqld could use up to key_buffer_size + > (read_buffer_size + sort_buffer_size)*max_threads = > 965187 K bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > Thread pointer: 0x7fc7eb1b5040 > Attempting backtrace. You can use the following information to find out > where mysqld died. If you see no messages after this, something went > terribly wrong... > stack_bottom = 7fadf6abfe60 thread_stack 0x3 > /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fc758522759] > /usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fc7583e9ae3] > /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fc75713bcb0] > /usr/sbin/mysqld(+0x6671b0)[0x7fc75863a1b0] > /usr/sbin/mysqld(+0x61d6b9)[0x7fc7585f06b9] > /usr/sbin/mysqld(+0x630d12)[0x7fc758603d12] > /usr/sbin/mysqld(+0x6319c2)[0x7fc7586049c2] > /usr/sbin/mysqld(+0x631d85)[0x7fc758604d85] > /usr/sbin/mysqld(+0x626e7d)[0x7fc7585f9e7d] > /usr/sbin/mysqld(+0x633cea)[0x7fc758606cea] > /usr/sbin/mysqld(+0x6347e2)[0x7fc7586077e2] > /usr/sbin/mysqld(+0x624426)[0x7fc7585f7426] > /usr/sbin/mysqld(+0x610871)[0x7fc7585e3871] > /usr/sbin/mysqld(+0x5d4cb0)[0x7fc7585a7cb0] > /usr/sbin/mysqld(+0x5b7c9c)[0x7fc75858ac9c] > /usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_ra > nge+0x24)[0x7fc7583e9fe4] > /usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x7fc7584a3c8 > c] > /usr/sbin/mysqld(+0x4e9195)[0x7fc7584bc195] > /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x71)[0x7fc7582f > 1741] > /usr/sbin/mysqld(+0x32f025)[0x7fc758302025] > /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4a5)[0x7fc758311155] > /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_E > S2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sele > ct_lex+0x130)[0x7fc75830d000] > /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x > 7fc758312f5c] > /usr/sbin/mysqld(+0x2f66b4)[0x7fc7582c96b4] > /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16d8)[0x7fc7582d1118] > /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fc758 > 2d5daf] > /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 > 80)[0x7fc7582d7200] > /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fc75837b7a > d] > /usr/sbin/mysqld(handle_one_connection+0x50)[0x7fc75837b810] > /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fc757133e9a] > /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc756864cbd] > > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort. > Query (7faa4c18e440): is an invalid pointer Connection ID (thread ID): > 2286 > Status: NOT_KILLED > > The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html > contains information that should help you find out what is causing the > crash. > 121120 12:48:48 [Note] Plugin 'FEDERATED' is disabled. > 121120 12:48:48 InnoDB: The InnoDB memory heap is disabled > 121120 12:48:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins > 121120 12:48:48 InnoDB: Compressed tables use zlib 1.2.3.4 > 121120 12:48:48 InnoDB: Initializing buffer pool, size = 96.0G > 121120 12:48:56 InnoDB: Completed initialization of buffer pool > 121120 12:48:57 InnoDB: highest supported file format is Barracuda. > InnoDB: Log scan progressed past the checkpoint lsn 1341738337497 > 121120 12:48:58 InnoDB: Database was not shut down normally! > InnoDB: Starting crash recovery. > InnoDB: Reading tablespace information from the .ibd files... > InnoDB: Restoring possible hal
Re: Why configuration directives are all case sensitive?
- Original Message - > From: "Eric Bergen" > > Anger and OS religious arguments the real answer is that is just how > the option parsing code works. It doesn't always have to make sense. Ye gods, it's an outbreak of common sense! Someone quarantine that man before it spreads! In all seriousness, though, that's a pretty useful explanation. I've never run into the case sensitivity in configfiles because I'm a lazy bugger, but I never realized they were just turned into so much commandline options. Thanks for that. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
