stack explanation ?
Hi, our server crashed yesterday with the following error : do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused 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=8388608 read_buffer_size=520192 max_used_connections=392 max_connections=1000 threads_connected=8 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1540184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x6bc2f258 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=0x45d4f88, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80f7893 0xc5ed96 0x82ab930 0x8158a17 0x82302c1 0x819f373 0x815d4e2 0x8105657 0x810868d 0x8103321 0x8102eb8 0x810280d 0xc59e51 0x50e06a 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 0x6c0095e8 is invalid pointer thd-thread_id=109709885 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. 060706 1:26:24 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 165 358046223 InnoDB: Doing recovery: scanned up to log sequence number 165 363289088 InnoDB: Doing recovery: scanned up to log sequence number 165 368531968 InnoDB: Doing recovery: scanned up to log sequence number 165 373774848 InnoDB: Doing recovery: scanned up to log sequence number 165 374990041 060706 1:26:24 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 060706 1:26:28 InnoDB: Flushing modified pages from the buffer pool... 060706 1:26:28 InnoDB: Started /var/lib/mysql/MySQL4.0.18/libexec/mysqld: ready for connections. Version: '4.0.18' socket: '/var/lib/mysql/MySQL4.0.18/var/mysql4.0.18.sock' port: 3306 resolved stack gives : 0x80f7893 handle_segfault + 399 0xc5ed96 (?) 0x82ab930 strnmov + 24 0x8158a17 innobase_mysql_print_thd + 471 0x82302c1 lock_print_info + 1573 0x819f373 srv_sprintf_innodb_monitor + 507 0x815d4e2 _Z18innodb_show_statusP3THD + 138 0x8105657 _Z21mysql_execute_commandv + 5999 0x810868d _Z11mysql_parseP3THDPcj + 329 0x8103321 _Z16dispatch_command19enum_server_commandP3THDPcj + 1069 0x8102eb8 _Z10do_commandP3THD + 100 0x810280d handle_one_connection + 841 0xc59e51 (?) 0x50e06a (?) this is a Mysql 4.0.18 on a dedicated linux Red Hat Enterprise Linux ES release 3 ( kernel 2.4.21-20 ) with 3 Gig RAM could you tell me what happened please? -- Dilipan Sebastiampillai Systems - DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recontruct database from NORMAL logs : how ?
Hi all, We have a copy of an old version of a database and a huge LOG file ( 9 Gig ) ( not the binary log unfortunately but the GENERAL QUERY LOG ) started when we put this database live. The only solution I see would be to run the queries from this log file to the database. What do you think? Is there any script that will enable me to get the queries concerning only one database from the General query log? Any comment is welcome. thanks in advance, -- Dilipan Sebastiampillai Systems - DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CPU 100% + crashes ...
That was the problem.
Thanks a lot Heikki.
I guess my next move would be to persuade upgrading to MySQL 5.
Dilipan
Heikki Tuuri wrote:
Dilipan,
indeed, there was a race in the printing of thd-query in 4.0.18. But
the problem was probably fixed in May 2004.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
5.0.16, ha_innodb.cc:
...
if ((s = thd-query)) {
/* 3100 is chosen because currently 3000 is the maximum
max_query_len we ever give this. */
charbuf[3100];
uintlen;
/* If buf is too small, we dynamically allocate storage
in this. */
char* dyn_str = NULL;
/* Points to buf or dyn_str. */
char* str = buf;
if (max_query_len == 0)
{
/* ADDITIONAL SAFETY: the default is to print at
most 300 chars to reduce the probability of a
seg fault if there is a race in
thd-query_length in MySQL; after May 14, 2004
probably no race any more, but better be
safe */
max_query_len = 300;
}
len = min(thd-query_length, max_query_len);
if (len (sizeof(buf) - 1))
{
dyn_str = my_malloc(len + 1, MYF(0));
str = dyn_str;
}
/* Use strmake to reduce the timeframe for a race,
compared to fwrite() */
len = (uint) (strmake(str, s, len) - str);
putc('\n', f);
fwrite(str, 1, len, f);
if (dyn_str)
{
my_free(dyn_str, MYF(0));
}
...
- Original Message - From: Heikki Tuuri
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, December 16, 2005 3:46 PM
Subject: Re: CPU 100% + crashes ...
Dilipan,
thank you.
I think there indeed is a slight unprotected access in:
0x8158a17 innobase_mysql_print_thd + 471
We will investigate if it has been fixed in 4.1.xx.
Again, SHOW INNODB STATUS\G shows a very light load inside InnoDB. You
would get more informative output if you would wait 20 seconds before
printing each SHOW INNODB STATUS\G. Now it says:
Per second averages calculated from the last 0 seconds
But the high load hardly can be inside InnoDB. Please post SHOW
PROCESSLIST during typical high load.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
Dilipan Sebastiampillai wrote:
can you please post the complete .err log that also contains
information about the crashes. If there are stack traces, please
resolve them.
-
.err
-
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=8388608
read_buffer_size=520192
max_used_connections=148
max_connections=1000
threads_connected=21
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 1540184 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x6f405da0
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=0x1978f88, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80f7893
0x6b4d96
0x82ab930
0x8158a17
0x82302c1
0x819f373
0x815d4e2
0x8105657
0x810868d
0x8103321
0x8102eb8
0x810280d
0x6afe51
0x51e06a
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
Re: CPU 100% + crashes ...
mbarnett_fcqjobsd FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 56197 OS file reads, 14395886 OS file writes, 140898 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.00 writes/s, 1000.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 3, seg size 5, 6504 inserts, 6504 merged recs, 940 merges Hash table size 4425293, used cells 2436838, node heap has 3471 buffer(s) 283000.00 hash searches/s, 43000.00 non-hash searches/s --- LOG --- Log sequence number 1 1949514002 Log flushed up to 1 1949513529 Last checkpoint at 1 1867403594 0 pending log writes, 0 pending chkp writes 14289646 log i/o's done, 9000.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1190142344; in additional pool allocated 4479360 Buffer pool size 65536 Free buffers 1 Database pages 62064 Modified db pages 1812 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 135023, created 24871, written 229592 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 5796, id 114696, state: sleeping Number of rows inserted 1143503, updated 17694615, deleted 1828259, read 2801888653 0.00 inserts/s, 8000.00 updates/s, 0.00 deletes/s, 316000.00 reads/s END OF INNODB MONITOR OUTPUT What does 'top' say? 'top' shows the 4 procs between 70% and %90 and loads of mysqld deamons at the top when you sort by CPU usage. Top shows that mysqlds are using the CPU. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Dilipan Sebastiampillai [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 15, 2005 2:04 PM Subject: CPU 100% + crashes ... Hi all, We have a MySQL server used for a queueing system. It worked fine so far but now we are experiencing some load problems couple with crashes. The load ( around 5-7 ) is due, according to vmstat, to processes waiting for the CPU. And it makes sense because the CPU is used 100% most of the time. Nearly all tables are Innodb ( 3 tables of 1kb are MyIsam ). Around 500 machines open a connection, make a query and disconnect. How can we tune MySQL so that it stops crashing and stays at a reasonnable load? Your help would be greatly appreciated. Dilipan FOLLOWING: system info my.cnf show status show innodb status - SYSTEM INFO - OS : Red Hat Enterprise Linux ES release 3 RAM : 4 Gig Hard disk : 15k rpm CPU : 2 x Intel Xeon 3.60GHz CPU with 1M cache (Hyperthreading Enabled) server version: 4.0.18-log ( I know it's old but we can't update for the moment ) database size : 1.9Gig - MY.CNF - [mysqld] skip-locking skip-grant-table # If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookup with #skip-name-resolve log-error log-slow-queries set-variable= join_buffer_size=256K set-variable= tmp_table_size=32M set-variable= max_tmp_tables=500 set-variable= long_query_time=2 set-variable= max_allowed_packet=1M set-variable= table_cache=2048 set-variable= sort_buffer=1M set-variable= read_buffer_size=512K set-variable= read_rnd_buffer_size=512K set-variable= myisam_sort_buffer_size=64M set-variable= back_log=256 set-variable= thread_cache_size=500 set-variable= max_connections=1000 set-variable= key_buffer_size=8M set-variable= max_connect_errors=5000 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 server-id = 1 set-variable= query_cache_size=20M
CPU 100% + crashes ...
pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 24523, id 114696, state: sleeping Number of rows inserted 64577, updated 1169552, deleted 1617684, read 1105845782 3000.00 inserts/s, 185000.00 updates/s, 0.00 deletes/s, 111256000.00 reads/s END OF INNODB MONITOR OUTPUT -- Dilipan Sebastiampillai Systems - DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automated DELETEs or/and UPDATES ?
Hi all, from time to time I have a huge number of DELETEs or/and UPDATES processed by MySQL at noon and 8pm. The trouble is that I didn't send those queries to MySQL ! i don't have any crons running. Is this an automated MySQL procedure ? Is there any automated procedure within MySQL? thanks in advance Dilipan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
updates after restart MySQL 4.0.18
Hi everyone, Our server crashed the Two questions : 1) is it normal to have a dramatic amount of UPDATES after a MySQL restart after crash ? 2) how to get valuable information from the following? ; Crash from 20/12/04 at 20.10(?) from server.err : 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=838860800 read_buffer_size=104853504 max_used_connections=63 max_connections=400 threads_connected=17 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3930556 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xb478f518 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=0xb57c2c0c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80f7893 0x40094e48 0x4690f068 0x82302c1 0x819f373 0x815d4e2 0x8105657 0x810868d 0x8103321 0x8102eb8 0x810280d 0x4008edac 0x40254a8a 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 0xb507fe68 is invalid pointer thd-thread_id=5725700 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. 041220 20:10:52 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 9 2043063838 InnoDB: Doing recovery: scanned up to log sequence number 9 2047101097 041220 20:10:53 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 041220 20:10:55 InnoDB: Flushing modified pages from the buffer pool... 041220 20:10:57 InnoDB: Started /var/lib/mysql/MySQL4.0.18/libexec/mysqld: ready for connections. Version: '4.0.18-log' socket: '/var/lib/mysql/MySQL4.0.18/var/mysql4.0.18.sock' port: 3306 Resolve stack : [EMAIL PROTECTED] ~mysql/tmp]# resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x80f7893 handle_segfault + 399 0x40094e48 _end + 936804128 0x4690f068 _end + 1046356288 0x82302c1 lock_print_info + 1573 0x819f373 srv_sprintf_innodb_monitor + 507 0x815d4e2 _Z18innodb_show_statusP3THD + 138 0x8105657 _Z21mysql_execute_commandv + 5999 0x810868d _Z11mysql_parseP3THDPcj + 329 0x8103321 _Z16dispatch_command19enum_server_commandP3THDPcj + 1069 0x8102eb8 _Z10do_commandP3THD + 100 0x810280d handle_one_connection + 841 0x4008edac _end + 936779396 0x40254a8a _end + 938638178 [EMAIL PROTECTED] ~mysql/tmp]# Thanks in advance. -- Dilipan Sebastiampillai London -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 - 4.1.7 LEFT JOIN problem
Hi!
On Nov 22, Dilipan Sebastiampillai wrote:
I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query
using LEFT JOIN gives me different result.
The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs
sometimes and I don't want that.
I think it comes from an 'optimization' how can I make a query
without using the LEFT JOIN optimization of MySQL 4.1.7 ?
What is the query ?
Can you provide a repeatable test case ?
Regards,
Sergei
the answer is amazingly wrong !
have a look :
mysql SELECT hosts.name,hosts.hostId, tries.hostId, tries.status FROM
hosts LEFT JOIN tries ON tries.hostId=hosts.hostId AND tries.status
IN('running','waitkill','preemption') LIMIT 20;
+-+++-+
| name| hostId | hostId | status |
+-+++-+
| chimp13 | 1530 | 1393 | running |
| chimp13 | 1530 | 1485 | running |
| chimp13 | 1530 | 1418 | running |
| chimp13 | 1530 | 1499 | running |
| chimp13 | 1530 | 1499 | running |
| chimp13 | 1530 | 1438 | running |
| chimp13 | 1530 | 1514 | running |
| chimp13 | 1530 | 1491 | running |
| chimp13 | 1530 | 1587 | running |
| chimp13 | 1530 | 1471 | running |
| chimp13 | 1530 | 1471 | running |
| chimp13 | 1530 | 1416 | running |
| chimp13 | 1530 | 1477 | running |
| chimp13 | 1530 | 1416 | running |
| chimp13 | 1530 | 1477 | running |
| chimp13 | 1530 | 1493 | running |
| chimp13 | 1530 | 1520 | running |
| chimp13 | 1530 | 1518 | running |
| chimp13 | 1530 | 1502 | running |
| chimp13 | 1530 | 1598 | running |
+-+++-+
20 rows in set (0.00 sec)
mysql
. the hostId are not the same althought i do a LEFT JOIN on them ...
but sometimes the answer is ok ...
here is my.cnf for a Xeon 2.40Ghz * 2
6 Gig of RAM
# This is for a large system with memory of 1G-2G where the system
runs mainly
# MySQL.
# The following options will be passed to all MySQL clients
[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
skip-locking
skip-external-locking
skip-grant-table # added by dlp
log-slow-queries
log-error
key_buffer = 512M # same that key_buffer_size ? dlp
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 100M # ori=2M
read_rnd_buffer_size = 100M # ori= 8M
myisam_sort_buffer_size = 64M
thread_cache = 1024 # ori =8
query_cache_size = 100M # ori = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
max_connections = 1000 # dlp
key_buffer_size = 512M # dlp
server-id= 1
# Point the following paths to different dedicated disks
tmpdir= /tmp/
#log-update = /path-to-dedicated-directory/hostname
innodb_data_home_dir = /var/lib/mysql/innodb/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/innodb/
innodb_log_arch_dir = /var/lib/mysql/innodb/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table=off
innodb_table_locks=off # dlp
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
--
Dilipan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.18 - 4.1.7 LEFT JOIN problem
I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query using LEFT JOIN gives me different result. The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs sometimes and I don't want that. I think it comes from an 'optimization' how can I make a query without using the LEFT JOIN optimization of MySQL 4.1.7 ? thanks in advance -- Dilipan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
phpMyAdmin don't show table size
Hello, after restoring from a mysqldump I use phpMyAdmin to view my database but the size field is unknown ... how could I get a size display ? -- Dilipan Sebastiampillai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
