| 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".
