Bug #36812 | MySQL "crashing" on subselect queries (I think) | ||
---|---|---|---|
Submitted: | 20 May 2008 2:28 | Modified: | 23 Jun 2008 21:40 |
Reporter: | Aaron | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: Errors | Severity: | S2 (Serious) |
Version: | 5.0.51a | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[20 May 2008 2:28]
Aaron
[20 May 2008 3:16]
Valeriy Kravchuk
Thank you for a problem report. Please, send your my.cnf file content and the results of: uname -a free Linux commands, for the beginning.
[20 May 2008 3:18]
Aaron
[root@db admin]# uname -a Linux db.teenspot.com 2.6.9-55.0.2.ELsmp #1 SMP Tue Jun 26 14:30:58 EDT 2007 i686 i686 i386 GNU/Linux [root@db admin]# free total used free shared buffers cached Mem: 4090552 3978460 112092 0 45528 3674292 -/+ buffers/cache: 258640 3831912 Swap: 8289532 192 8289340 [root@db admin]# cat /etc/my.cnf [client] port=3306 socket=/var/lib/mysql/mysql.sock [mysqld] bind-address=192.168.0.2 port=3306 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-name-resolve skip-locking skip-innodb max_connections=500 key_buffer=256M myisam_sort_buffer_size=64M join_buffer_size=2M read_buffer_size=2M read_rnd_buffer_size=1M sort_buffer_size=4M table_cache=1800 thread_cache_size=512 thread_concurrency=4 wait_timeout=60 connect_timeout=10 interactive_timeout=60 tmp_table_size=256M bulk_insert_buffer_size=16M max_allowed_packet=64M max_connect_errors=10 max_heap_table_size=128M query_cache_limit=2M query_cache_size=128M query_cache_type=1 query_prealloc_size=16384 query_alloc_block_size=16384 #log-warnings log-error #log-slow-queries=mysql-slow-may-27.log #long-query-time=1 #log-queries-not-using-indexes [mysqld_safe] open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M
[20 May 2008 15:56]
Sveta Smirnova
Thank you for the feedback. Why do you need to manually kill MySQL server? Does it freeze or? Also please try to catch query which causes such behavior. You can do it if you connect as root in time when event, because which you kill MySQL, happens and run query SHOW PROCESSLIST.
[20 May 2008 18:16]
Aaron
Hi Sveta, We have to kill all of the MySQL processes because they are still running! I know that is a simple answer, but it is the only way to get MySQL working correctly again. If we just do a "/etc/rc.d/init.d/mysql start" when it "crashes" it just says that MySQL is already running, I believe. I will double check this next time it happens. I will also try and get a processlist from it, if it lets me. Just to clarify on killing MySQL processes... we literally have to do this: /etc/rc.d/init.d/mysql stop It does it's ............... thing for a few minutes, but the MySQL processes are still running. At this point we do a: killall -9 mysqld It appears the mysqld processes are *still* running, then we have to find the mysqld process running under the "root" user and manually kill that pid. kill -9 <root process pid> Then we do this again: killall -9 mysqld and FINALLY, after those steps are all done, then MySQL is able to be started again. Thanks!
[23 May 2008 2:45]
Aaron
I wanted to add that I tried to do a "mysqladmin -u root processlist" when the "crash" happened, but it would not work. It was like it was lagged beyond belief. Thanks
[23 May 2008 21:40]
Sveta Smirnova
Thank you for the feedback. For me many sub_select calls can show you issue some large SELECT query with JOIN of many tables. Additionally free shows you use almost all RAM of the system. To be sure big SELECT is the cause turn on general query log and catch this select from the log file (it would be one of latest queries).
[23 Jun 2008 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".