Bug #1758 Mysql crashed and had to be manually restarted
Submitted: 5 Nov 2003 9:36 Modified: 13 Jan 2004 11:37
Reporter: Richard Thomas Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.16-0 OS:Linux (Redhat 9)
Assigned to: CPU Architecture:Any

[5 Nov 2003 9:36] Richard Thomas
Description:
Crashed after 3 days of uptime. Server is a new Quad Zeon with 8 gigs of memory.

mysqlbug

>C compiler:    2.95.3
>C++ compiler:  2.95.3
>Environment:
        <machine, os, target, libraries (multiple lines)>
System: Linux localhost.localdomain 2.4.20-20.9bigmem #1 SMP Mon Aug 18 11:18:32 EDT 2003 i686 i686 i386 GNU/Linux
Architecture: i686
                                                                                                                                                             
Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --wi
Thread model: posix
gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce'  CXX='g++'  CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce                -felide-co
LIBC:
lrwxrwxrwx    1 root     root           13 Oct 23 17:28 /lib/libc.so.6 -> libc-2.3.2.so
-rwxr-xr-x    1 root     root      1557296 Apr  7  2003 /lib/libc-2.3.2.so
-rw-r--r--    1 root     root      2331360 Apr  7  2003 /usr/lib/libc.a
-rw-r--r--    1 root     root          204 Apr  7  2003 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '
                                                                                                                                                             
                                                                                                                                                             
my.cnf settings I have adjusted from default HUGE file included with system.
                                                                                                                                                             
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock
                                                                                                                                                             
# Here follows entries for some specific programs
                                                                                                                                                             
# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
query_cache_type = 1
query_cache_limit = 32M
query_cache_size = 500M
ft_min_word_len = 3
                                                                                                                                                             
skip-locking
key_buffer = 2048M
max_allowed_packet = 2M
table_cache = 4096
sort_buffer_size = 32M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 1024
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
max_connections=500
skip-innodb
                                                                                                                                                             

Error log included.

Version: '4.0.16-standard-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
mysqld got signal 6;
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=2147483648
read_buffer_size=1044480
max_used_connections=53
max_connections=500
threads_connected=54
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2213932 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
                                                                                                                                                             
thd=0xbf443958
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...
Cannot determine thread, fp=0xbf9fdbb8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8071fa4
0x8292178
0x82a3081
0x8292509
0x82a34a0
0x8309c98
0x8309cbd
0x830a776
0x830a945
0x8309aac
0x80c1499
0x80be417
0x80975c9
0x8097fb7
0x8096169
0x8095916
0x807cb4a
0x8080775
0x807bcc3
0x807b70e
0x807aef8
0x828f92c
0x82c303a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x860bbf8 = SELECT count(*) as parts FROM broker_parts.partall WHERE comp_id = 4099
thd->thread_id=17277
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
                                                                                                                                                             
Number of processes running now: 0

How to repeat:
Could not repeat
[11 Nov 2003 7:20] Martin Mokrejs
You have to resolve the stacktrace and post the resolved version at least. For example, this might help you:

#!/bin/sh
echo "This scripts expects stack trace from your mysql error log file"
echo "placed in the current directory as ./stack"

if [ ! -r ./stack ]; then
   echo "./stack file not present or readable"
   exit 255
fi

if [ -r /usr/local/mysql/bin/mysqld.sym.gz ]; then
   gzip -dc /usr/local/mysql/bin/mysqld.sym.gz > ./symbols
else
   echo "nm -n /usr/local/mysql/bin/mysqld > ./symbols"
   nm -n /usr/local/mysql/bin/mysqld > ./symbols
fi

echo "/usr/local/mysql/bin/resolve_stack_dump -s ./symbols -n ./stack"
/usr/local/mysql/bin/resolve_stack_dump -s ./symbols -n ./stack

You should find uout which table caused the crashed and what was the SQL command used.
[11 Nov 2003 8:29] Richard Thomas
Results from provided script

0x8071fa4  + 134684580
0x8292178  + 136913272
0x82a3081  + 136982657
0x8292509  + 136914185
0x82a34a0  + 136983712
0x8309c98  + 137403544
0x8309cbd  + 137403581
0x830a776  + 137406326
0x830a945  + 137406789
0x8309aac  + 137403052
0x80c1499  + 135009433
0x80be417  + 134997015
0x80975c9  + 134837705
0x8097fb7  + 134840247
0x8096169  + 134832489
0x8095916  + 134830358
0x807cb4a  + 134728522
0x8080775  + 134743925
0x807bcc3  + 134724803
0x807b70e  + 134723342
0x807aef8  + 134721272
0x828f92c  + 136902956
0x82c303a  + 137113658

Suspect query from logs 
SELECT count(*) as parts FROM
broker_parts.partall WHERE comp_id = 4099

Server has been up 6 days since reboot without problems now.
[12 Nov 2003 8:07] Richard Thomas
Another crash today.. Same exact stack, diffrent query.
query at 0x870f120 = SELECT COUNT(*) AS cnt FROM broker_users.companytable WHERE (approval=1 OR approval=2)  AND signup_rep=16

Stacks match,query is diffrent.
[10 Dec 2003 6:21] Tom Zlos
Running RH9/Apache 1.3.29/php 4.3.4/mysql 4.0.16
MySQL is running on a separate machine, WWW has its own, separate machine.

I experience very same problems with queries using WHERE clause, running on frequently updated (by INSERT, UPDATE & DELETE) tables.

Example of query:
SELECT AVG( re.rating_value ) AS avgscore, r . * , r.date_added AS date_review_added, i . * , v . * 
FROM rating AS r, items AS i, vendors AS v, rating_entries AS re
WHERE r.item_id = i.item_id AND v.vendor_id = i.vendor_id AND re.rating_id = r.rating_id
GROUP BY rating_id
ORDER BY r.date_added DESC 
LIMIT 5  

This query requires extremely healthy indexes, as due to WHERE is used together with GROUP BY and ORDER BY. When index is not refreshed at the execution of DELETE query, problems with capability of handling huge temporary tables & files occurs immediately.

Size vs. index
--------------
The query is operating on 4 tables, which are not impressive by its size on standalone basis:
- rating: approx. 27000 records
- rating_entries: approx. 31000 records
- items: approx. 4700 items
- vendor: approx. 400 items

But especially rating and rating_entries are updated approx. 5-10 times per minute (including issuing DELETE commands to these tables). This is having a tremendous impact on indexes, which for some reason are not refreshed after deleting rows from a database.

Let's have a look on EXPLAIN of above mentioned query:

table  type  possible_keys  key  key_len  ref  rows  Extra  
i ALL PRIMARY,product_id,vendor_id,item_vendor_category NULL NULL NULL 4818 Using temporary; Using filesort 
v ref PRIMARY,vendor_id PRIMARY 4 i.vendor_id 1   
r ref PRIMARY,product_id,rating_id product_id 4 i.item_id 6   
re ref rating_id rating_id 4 r.rating_id 1 Using where 

This means we have a temporary table of 4818 x 1 x 6 x 1, total 28908 rows. Not small, right? But those 4 tables are equipped with right indexes and execution time goes below 1 second.

As soon as DELETE command removes one row from tables rating and rating_entries, which are relating to each other, we have problem with indexes of tables rating & rating_entries. For some (???) reason those indexes cannot be accessed anymore and mysqld builds a sort file on all the rows from these two tables. Accordingly:

This means we have a temporary table of 4818 x 27000 x 31000 x 1, total 4032666000000 rows. Nice...

This hits hard disk and fills it immediately upto limits. At the same time CPU (1 x P4 2,4MHz) stays at 30-50%, RAM is still enough, no memory leaks at all. But simply it cannot work like that. One would need huge 40-60 GB RAM drive to serve it correctly.

Of course there are ways to overcome it, for example by declaring cron job which constantly optimizes these tables. As I did. But then it becomes the question of luck, which process will be first right after deleting a row from these tables - SELECT query or OPTIMIZE query? 

Therefore my machine surrvives sometimes 8 days, but with huge load (like Google spidering my site) even each 5 minuts server has to be restarted.

Hope this will lead in the right direction.
[10 Dec 2003 7:58] Richard Thomas
I think your right, a fragmented index can cause higher memory usage and issues.

I was already optimizing every night, the problems did not start till I moved to a new server dedicated to mysql ( the previous was shared with apache and sendmail ).

I fine tuned the memory really close to using the max ram. After the crashes I scaled back those settings slightly and have had a uptime of 21 days now.. the longest yet. If your having issues try reducing your memory settings in my.cnf some and see if that helps.
[11 Dec 2003 0:36] Tom Zlos
Well. I am even more surprised, as I have also recently moved to a dedicated mysql machine. 

For me the real issue is fragmentation of indexes. As I have 2GB of RAM only, exceeding some Linux system limites values is not a problem, I hope.

Good way to repeat this bug is:
- simple delete one row from table, which you use in your WHERE clause, 
- point your file manager (mc) to mysql temporary catalogue
- then run the query
- observe temporary file size

If your machine is strong enough and table is not locked you can try to obtain EXPLAIN of your SELECT statement. Would be interesting for you to see how big Kartesian table is.

In my case temporary files go immediately over 4GB of HDD space. This is not actually stopping server, it simply extremely slows down execution of mysql threads.  MyISAM tables get locked and all the other threads (including OPTIMIZE) are waiting untill fat boys will be pushed through HDD.

I am just wondering, should I report a separated bug related to index fragmentation?
[11 Dec 2003 0:52] Tom Zlos
Sorry, I have forgotten to mention, that optimization (run from apache' cron job) goes each 20 minutes. As my mysql serves approx. 5000 queries per minute:

- I can't rung cron more frequently, due to accesibility issued & performance of the server itself

- Indexes got fragmented anyhow :-(
[13 Dec 2003 11:37] Michael Widenius
This bug report contains two different, independent issues. I try to handle both here. Future discussion about 'framgendted indexes' should however be moded to a separte bug report.

The crash:
The stack trace doesn't help us as this is not symbolic (we need the names of the called functions to get an idea of the problem). Sorry but I don't know why the script supplied by Martin Mokrejs doesn't work :(

Please read the link http://www.mysql.com/doc/en/Using_stack_trace.html for how to do the stack trace.

If you have more crashes, I would recommend you to download the debug version of the MySQL server and start this with the --core-file option. If you now get a crash, then the generated core file should contain enough information  to solve the problem.  You can upload the core file, the used mysqld binary and a short description of the bug to ftp://support.mysql.com/pub/mysql/secret/ so that we can analyse it.

About index fragementation (should be moved to another bug report)

There are a few separate, independent problems with indexes when one
does a lot of deletes on a table

- The cardinality of the index changes (not likely but possible) and MySQL uses   the wrong index in the query.

  In this case one could try to update the cardinality with ANALYZE
  TABLE (faster than OPTIMIZE TABLE) or use IGNORE INDEX / USE INDEX in
  the query to get MySQL to use the right indexes.

- The indexes gets 'unbalanced' and the range optimizer estimates
  wrongly the wrong number of matching keys for expressions like
  'key_column op constant'.  In this case one should update the index
  with OPTIMIZE TABLE or use IGNORE INDEX / USE INDEX.

- The index files could be fragmented on disk, causing more seeks than
are optimal, but I don't think this is the problem here.

Neither of the above problems are frequent and only happens to some
specific tables and specific usage.  One common case where the index
gets unbalanced is when you have an indexed timestamp columns and you
insert a lot of new rows and delete a lot of old rows with an old
timestamp.  In this case the optimizer will estimate wrongly the
number of rows for queries like

WHERE timestamp_column BETWEEN date and data2

In most cases the estimate is still 'good enough' but we know of cases
when the estimate is so wrong that MySQL will instead choose another
index to solve the query.

Fragmentation of indexes should not happen for just a single delete of
one row.  If this is the case then there is some unknown factor
involved and we would need to get a test case to be able to find this
out.

To know what is happening in this particular case I would need the
explain from tables before the delete is done (when the query was fast) 
and the explain after the delete was done (and the query is slow).

You can the the information what exactly happened during a query by doing:

mysql> FLUSH STATUS;
mysql> SELECT ...;
       <A short version of the output from SELECT,
       including the time taken to run the query>
mysql> SHOW STATUS;
       <output from SHOW STATUS>

(The above assumes you are the only one using the system as the STATUS
variables are global).

The best way to know what is happening is of course if we could get a
copy of the problem tables when the query is slow so that we can test
this yourself. (Feel free to reset any columns that are not part of
the query with NULL or ""). Upload the tables, with a description of
the problem, to ftp://support.mysql.com/pub/mysql/secret.

We presently found out that 4.0.16 has a bug (4.0.15 was ok) that in
some cases makes table scans when it doesn't have to.  This will be
fixed in 4.0.17 that will be out in a few days.

Regads,
Monty
[14 Feb 2005 22:54] 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".