List:General Discussion« Previous MessageNext Message »
From:Dilipan Sebastiampillai Date:December 16 2005 10:45am
Subject:Re: CPU 100% + crashes ...
View as plain text  
> 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.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa633050 = SHOW INNODB STATUS
thd->thread_id=6289685
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.
051214 19:09:08  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 1 2404815205
InnoDB: Doing recovery: scanned up to log sequence number 1 2410057728
InnoDB: Doing recovery: scanned up to log sequence number 1 2415300608
InnoDB: Doing recovery: scanned up to log sequence number 1 2420543488
InnoDB: Doing recovery: scanned up to log sequence number 1 2425786368
InnoDB: Doing recovery: scanned up to log sequence number 1 2431029248
InnoDB: Doing recovery: scanned up to log sequence number 1 2436272128
InnoDB: Doing recovery: scanned up to log sequence number 1 2441515008
InnoDB: Doing recovery: scanned up to log sequence number 1 2446757888
InnoDB: Doing recovery: scanned up to log sequence number 1 2452000768
InnoDB: Doing recovery: scanned up to log sequence number 1 2457243648
InnoDB: Doing recovery: scanned up to log sequence number 1 2462486528
InnoDB: Doing recovery: scanned up to log sequence number 1 2463763843
051214 19:09:10  InnoDB: Starting an apply batch of log records to the 
database...
InnoDB: Progress in percents: 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
051214 19:09:17  InnoDB: Flushing modified pages from the buffer pool...
051214 19:09:18  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
051215  8:40:46  /var/lib/mysql/MySQL4.0.18/libexec/mysqld: Normal shutdown

051215  8:40:47  InnoDB: Starting shutdown...
051215  8:40:50  InnoDB: Shutdown completed
051215  8:40:50  /var/lib/mysql/MySQL4.0.18/libexec/mysqld: Shutdown 
Complete

051215  8:40:52  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


-------------------------------------------------------------------------------------------------------------------------------------------------------------


resolved stack traces
-------------------------------------------------------------------------------------------------------------------------------------------------------------


mysql@:~/MySQL4.0.18/var =>resolve_stack_dump -s /tmp/mysqld.sym -n 
mysqld.stack
0x80f7893 handle_segfault + 399
0x6b4d96 (?)
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
0x6afe51 (?)
0x51e06a (?)


> The workload inside InnoDB does not look that big if what you have 
> posted is a typical SHOW INNODB STATUS\G. Is that the case?
>
Let me give you another output of SHOW INNODB STATUS\G :

-------------------------------------------------------------------------------------------------------------------------------------------------------------


INNODB STATUS
-------------------------------------------------------------------------------------------------------------------------------------------------------------


=====================================
051214 16:50:57 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 34499561, signal count 27541001
Mutex spin waits 3822607233, rounds 3743522392, OS waits 14003312
RW-shared spins 16215186, OS waits 3033025; RW-excl spins 29795448, OS 
waits 2032273
------------------------
LATEST DETECTED DEADLOCK
------------------------
051214 10:34:46
*** (1) TRANSACTION:
TRANSACTION 0 55972531, ACTIVE 0 sec, process no 15571, OS thread id 
1409111 starting index read
mysql tables in use 8, locked 8
LOCK WAIT 4 lock struct(s), heap size 320
MySQL thread id 5759288, query id 72872456 ####
/*Finished slot*/UPDATE ###
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 91191 n bits 96 table fcq/commands index 
commandId trx id 0 55972531 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 RECORD: info bits 0 0: len 4; hex 83ffeafc; asc 
....;; 1: len 6; hex 000003560dd1; asc ...V..;;
*** (2) TRANSACTION:
TRANSACTION 0 55971281, ACTIVE 2 sec, process no 14592, OS thread id 
1081411 fetching rows, thread declared inside InnoDB 165
mysql tables in use 1, locked 1
223 lock struct(s), heap size 27968, undo log entries 5219
MySQL thread id 5759191, query id 72872880 #### Updating
UPDATE ####
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 91191 n bits 96 table fcq/commands index 
commandId trx id 0 55971281 lock_mode X locks rec but not gap
Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 83ffeaf9; asc 
....;; 1: len 6; hex 00000352d0fa; asc ...R..;;
Record lock, heap no 3 RECORD: info bits 0 0: len 4; hex 83ffeafa; asc 
....;; 1: len 6; hex 00000352d107; asc ...R..;;
Suppressing further record lock prints for this page
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 70716 n bits 136 table fcq/tries index 
tryId trx id 0 55971281 lock_mode X locks rec but not gap waiting
Record lock, heap no 28 RECORD: info bits 0 0: len 4; hex 82d62f5f; asc 
../_;; 1: len 6; hex 00000355e1fe; asc ...U..;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 76190667
Purge done for trx's n:o < 0 76187856 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 76190648, not started, process no 8183, OS thread id 753711
MySQL thread id 7187988, query id 95399020 rk001.prod.local 172.18.57.1 
fcqdaemon
---TRANSACTION 0 76190630, not started, process no 31450, OS thread id 
1622116
MySQL thread id 7187954, query id 95398987 rk195.prod.local 
172.18.57.195 fcqdaemon
---TRANSACTION 0 76186342, not started, process no 8178, OS thread id 671786
MySQL thread id 7187617, query id 95385407 ps23.prod.local 172.18.17.23 
fcqweb
---TRANSACTION 0 0, not started, process no 29778, OS thread id 1540191
MySQL thread id 7171535, query id 95157268 sys385.prod.local 
172.17.40.85 dilipan
---TRANSACTION 0 0, not started, process no 5801, OS thread id 196621
MySQL thread id 7150472, query id 94542926 sys385.prod.local 
172.17.40.85 root
---TRANSACTION 0 73849091, not started, process no 8177, OS thread id 655401
MySQL thread id 7036138, query id 92917099 sys170.prod.local 
172.17.80.170 sdeverly
---TRANSACTION 0 0, not started, process no 5800, OS thread id 180236
MySQL thread id 6289494, query id 79345697 sys385.prod.local 
172.17.40.85 root
---TRANSACTION 0 0, not started, process no 8197, OS thread id 983101
MySQL thread id 6289685, query id 95398991 sys385.prod.local 
172.17.40.85 root
---TRANSACTION 0 0, not started, process no 8196, OS thread id 966716
MySQL thread id 6195150, query id 95398906 wsys385.prod.local 
172.17.42.85 root
---TRANSACTION 0 0, not started, process no 15569, OS thread id 1376341
MySQL thread id 6195045, query id 94308327 wsys385.prod.local 
172.17.42.85 root
---TRANSACTION 0 76189183, not started, process no 8184, OS thread id 770096
MySQL thread id 572201, query id 95395005 sys328.prod.local 172.18.40.28 
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, 10000.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" 
> <dilipan.sebastiampillai@stripped>
> 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
>>
>> innodb_data_home_dir = /var/lib/mysql/MySQL4.0.18/var/
>> innodb_data_file_path=ibdata1:1000M;ibdata2:10M:autoextend
>> innodb_log_group_home_dir = /var/lib/mysql/MySQL4.0.18/var/
>> innodb_log_arch_dir = /var/lib/mysql/MySQL4.0.18/var/
>>
>> # You can set .._buffer_pool_size up to 50 - 80 %
>> # of RAM but beware of setting memory usage too high
>> set-variable = innodb_buffer_pool_size=1924M # previous was 1024M
>> set-variable = innodb_additional_mem_pool_size=30M # previous was 20M
>>
>> # Set .._log_file_size to 25 % of buffer pool size
>> set-variable = innodb_log_file_size=256M
>> set-variable = innodb_log_buffer_size=8M
>> set-variable = innodb_lock_wait_timeout=300 #=5min
>> innodb_flush_log_at_trx_commit=2
>> set-variable    =innodb_thread_concurrency=8
>> tmpdir          = /tmp/
>>
>>
>> [mysqldump]
>> quick
>> set-variable    = max_allowed_packet=16M
>>
>> [mysql]
>> no-auto-rehash
>>
>> [isamchk]
>> set-variable    = key_buffer=512M
>> set-variable    = sort_buffer=512M
>> set-variable    = read_buffer=2M
>> set-variable    = write_buffer=2M
>>
>> [myisamchk]
>> set-variable    = key_buffer=512M
>> set-variable    = sort_buffer=512M
>> set-variable    = read_buffer=2M
>> set-variable    = write_buffer=2M
>>
>> [mysqlhotcopy]
>> interactive-timeout
>>
>> [safe_mysqld]
>> err-log=/var/lib/mysql/MySQL4.0.18/var/safe_mysqld_mysqld4.0.18.log
>> pid-file=/var/run/mysqld4.0.18.pid
>>
>>
>>
>>
>>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> 
>>
>> SHOW STATUS
>>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> 
>>
>> This MySQL server has been running for 0 days, 2 hours, 47 minutes and
>> 55 seconds. It started up on Dec 15, 2005 at 08:53 AM.
>>
>> mysql> show status ;
>> +--------------------------+------------+
>> | Variable_name            | Value      |
>> +--------------------------+------------+
>> | Aborted_clients          | 2          |
>> | Aborted_connects         | 36         |
>> | Bytes_received           | 2126078417 |
>> | Bytes_sent               | 1070759771 |
>> | Com_admin_commands       | 0          |
>> | Com_alter_table          | 0          |
>> | Com_analyze              | 0          |
>> | Com_backup_table         | 0          |
>> | Com_begin                | 311        |
>> | Com_change_db            | 43         |
>> | Com_change_master        | 0          |
>> | Com_check                | 0          |
>> | Com_commit               | 312        |
>> | Com_create_db            | 0          |
>> | Com_create_function      | 0          |
>> | Com_create_index         | 0          |
>> | Com_create_table         | 0          |
>> | Com_delete               | 18         |
>> | Com_delete_multi         | 12         |
>> | Com_drop_db              | 0          |
>> | Com_drop_function        | 0          |
>> | Com_drop_index           | 0          |
>> | Com_drop_table           | 0          |
>> | Com_flush                | 0          |
>> | Com_grant                | 0          |
>> | Com_ha_close             | 0          |
>> | Com_ha_open              | 0          |
>> | Com_ha_read              | 0          |
>> | Com_insert               | 46633      |
>> | Com_insert_select        | 0          |
>> | Com_kill                 | 0          |
>> | Com_load                 | 0          |
>> | Com_load_master_data     | 0          |
>> | Com_load_master_table    | 0          |
>> | Com_lock_tables          | 0          |
>> | Com_optimize             | 26         |
>> | Com_purge                | 0          |
>> | Com_rename_table         | 0          |
>> | Com_repair               | 0          |
>> | Com_replace              | 0          |
>> | Com_replace_select       | 0          |
>> | Com_reset                | 0          |
>> | Com_restore_table        | 0          |
>> | Com_revoke               | 0          |
>> | Com_rollback             | 0          |
>> | Com_savepoint            | 0          |
>> | Com_select               | 3477135    |
>> | Com_set_option           | 115790     |
>> | Com_show_binlog_events   | 0          |
>> | Com_show_binlogs         | 3          |
>> | Com_show_create          | 1          |
>> | Com_show_databases       | 10         |
>> | Com_show_fields          | 63         |
>> | Com_show_grants          | 0          |
>> | Com_show_keys            | 8          |
>> | Com_show_logs            | 0          |
>> | Com_show_master_status   | 0          |
>> | Com_show_new_master      | 0          |
>> | Com_show_open_tables     | 0          |
>> | Com_show_processlist     | 14681      |
>> | Com_show_slave_hosts     | 0          |
>> | Com_show_slave_status    | 0          |
>> | Com_show_status          | 23976      |
>> | Com_show_innodb_status   | 9505       |
>> | Com_show_tables          | 79         |
>> | Com_show_variables       | 530        |
>> | Com_slave_start          | 0          |
>> | Com_slave_stop           | 0          |
>> | Com_truncate             | 0          |
>> | Com_unlock_tables        | 426        |
>> | Com_update               | 2408550    |
>> | Connections              | 444434     |
>> | Created_tmp_disk_tables  | 108778     |
>> | Created_tmp_tables       | 280390     |
>> | Created_tmp_files        | 2          |
>> | Delayed_insert_threads   | 0          |
>> | Delayed_writes           | 0          |
>> | Delayed_errors           | 0          |
>> | Flush_commands           | 1          |
>> | Handler_commit           | 312        |
>> | Handler_delete           | 0          |
>> | Handler_read_first       | 277779     |
>> | Handler_read_key         | 90929262   |
>> | Handler_read_next        | 1152511422 |
>> | Handler_read_prev        | 0          |
>> | Handler_read_rnd         | 7821452    |
>> | Handler_read_rnd_next    | 57375083   |
>> | Handler_rollback         | 424513     |
>> | Handler_update           | 29812487   |
>> | Handler_write            | 25867776   |
>> | Key_blocks_used          | 7793       |
>> | Key_read_requests        | 25104538   |
>> | Key_reads                | 0          |
>> | Key_write_requests       | 204156     |
>> | Key_writes               | 0          |
>> | Max_used_connections     | 143        |
>> | Not_flushed_key_blocks   | 0          |
>> | Not_flushed_delayed_rows | 0          |
>> | Open_tables              | 225        |
>> | Open_files               | 24         |
>> | Open_streams             | 0          |
>> | Opened_tables            | 331        |
>> | Questions                | 7214176    |
>> | Qcache_queries_in_cache  | 555        |
>> | Qcache_inserts           | 3221408    |
>> | Qcache_hits              | 674342     |
>> | Qcache_lowmem_prunes     | 0          |
>> | Qcache_not_cached        | 256785     |
>> | Qcache_free_memory       | 20158008   |
>> | Qcache_free_blocks       | 317        |
>> | Qcache_total_blocks      | 1439       |
>> | Rpl_status               | NULL       |
>> | Select_full_join         | 1545       |
>> | Select_full_range_join   | 251        |
>> | Select_range             | 1850013    |
>> | Select_range_check       | 0          |
>> | Select_scan              | 275729     |
>> | Slave_open_temp_tables   | 0          |
>> | Slave_running            | OFF        |
>> | Slow_launch_threads      | 0          |
>> | Slow_queries             | 149        |
>> | Sort_merge_passes        | 1          |
>> | Sort_range               | 17305      |
>> | Sort_rows                | 2904469    |
>> | Sort_scan                | 101391     |
>> | Table_locks_immediate    | 9383249    |
>> | Table_locks_waited       | 1590       |
>> | Threads_cached           | 131        |
>> | Threads_created          | 144        |
>> | Threads_connected        | 13         |
>> | Threads_running          | 6          |
>> | Uptime                   | 10138      |
>> +--------------------------+------------+
>> 132 rows in set (0.00 sec)
>>
>>
>>
>>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> 
>>
>> INNODB STATUS
>>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> 
>>
>>
>> mysql> show innodb status\G
>> *************************** 1. row ***************************
>> Status:
>> =====================================
>> 051215 11:27:05 INNODB MONITOR OUTPUT
>> =====================================
>> Per second averages calculated from the last 0 seconds
>> ----------
>> SEMAPHORES
>> ----------
>> OS WAIT ARRAY INFO: reservation count 2314454, signal count 1923933
>> Mutex spin waits 1057261556, rounds 2350226711, OS waits 896528
>> RW-shared spins 1526452, OS waits 194614; RW-excl spins 3582238, OS
>> waits 148956
>> ------------------------
>> LATEST DETECTED DEADLOCK
>> ------------------------
>> 051215 10:05:42
>> *** (1) TRANSACTION:
>> TRANSACTION 0 112269141, ACTIVE 0 sec, process no 24526, OS thread id
>> 163851 starting index read
>> mysql tables in use 8, locked 8
>> LOCK WAIT 4 lock struct(s), heap size 320
>> **
>> *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
>> RECORD LOCKS space id 0 page no 34769 n bits 88 table fcq/commands index
>> commandId trx id 0 112269141 lock_mode X locks rec but not gap waiting
>> Record lock, heap no 7 RECORD: info bits 0 0: len 4; hex 83fdfeb5; asc
>> ....;; 1: len 6; hex 000006b1164c; asc .....L;;
>> *** (2) TRANSACTION:
>> TRANSACTION 0 112268876, ACTIVE 0 sec, process no 24530, OS thread id
>> 229391 fetching rows, thread declared inside InnoDB 332
>> mysql tables in use 1, locked 1
>> 643 lock struct(s), heap size 44352, undo log entries 1924
>> MySQL thread id 155698, query id 2843769  Searching rows for update
>> UPDATE **
>> *** (2) HOLDS THE LOCK(S):
>> RECORD LOCKS space id 0 page no 34769 n bits 88 table fcq/commands index
>> commandId trx id 0 112268876 lock_mode X locks rec but not gap
>> Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 83fdfeb0; asc
>> ....;; 1: len 6; hex 000006b1164c; asc .....L;;
>> Record lock, heap no 3 RECORD: info bits 0 0: len 4; hex 83fdfeb1; asc
>> ....;; 1: len 6; hex 000006b10fcf; asc ......;;
>> Suppressing further record lock prints for this page
>> *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
>> RECORD LOCKS space id 0 page no 78520 n bits 136 table fcq/tries index
>> tryId trx id 0 112268876 lock_mode X locks rec but not gap waiting
>> Record lock, heap no 36 RECORD: info bits 0 0: len 4; hex 82d869b2; asc
>> ..i.;; 1: len 6; hex 000006b08b2e; asc ......;;
>> *** WE ROLL BACK TRANSACTION (1)
>> ------------
>> TRANSACTIONS
>> ------------
>> Trx id counter 0 115265959
>> Purge done for trx's n:o < 0 115265364 undo n:o < 0 0
>> Total number of lock structs in row lock hash table 0
>> LIST OF TRANSACTIONS FOR EACH SESSION:
>> ---TRANSACTION 0 115265954, not started, process no 8253, OS thread id
>> 1278031
>> **
>> ---TRANSACTION 0 115265950, not started, process no 8197, OS thread id
>> 1097796 starting index read, thread declared inside InnoDB 500
>> mysql tables in use 1, locked 1
>> UPDATE **
>> ---TRANSACTION 0 115265888, not started, process no 25549, OS thread id
>> 950331
>> **
>> ---TRANSACTION 0 0, not started, process no 24601, OS thread id 507936
>> **
>> ---TRANSACTION 0 115265701, not started, process no 10195, OS thread id
>> 2261131
>> **
>> ---TRANSACTION 0 113682384, not started, process no 25550, OS thread id
>> 966716
>> **
>> ---TRANSACTION 0 115265264, not started, process no 24529, OS thread id
>> 213006
>> **
>> ---TRANSACTION 0 0, not started, process no 24531, OS thread id 245776
>> **
>> --------
>> 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
>> 43408 OS file reads, 980605 OS file writes, 12706 OS fsyncs
>> 0.00 reads/s, 0 avg bytes/read, 149000.00 writes/s, 4000.00 fsyncs/s
>> -------------------------------------
>> INSERT BUFFER AND ADAPTIVE HASH INDEX
>> -------------------------------------
>> Ibuf for space 0: size 1, free list len 3, seg size 5,
>> 1546 inserts, 1546 merged recs, 483 merges
>> Hash table size 7985381, used cells 2683537, node heap has 3207 
>> buffer(s)
>> 99400000.00 hash searches/s, 18064000.00 non-hash searches/s
>> ---
>> LOG
>> ---
>> Log sequence number 2 632102335
>> Log flushed up to   2 632051578
>> Last checkpoint at  2 392421868
>> 0 pending log writes, 0 pending chkp writes
>> 970504 log i/o's done, 142000.00 log i/o's/second
>> ----------------------
>> BUFFER POOL AND MEMORY
>> ----------------------
>> Total memory allocated 2210424456; in additional pool allocated 4874624
>> Buffer pool size   123136
>> Free buffers       37398
>> Database pages     82531
>> Modified db pages  8719
>> Pending reads 0
>> Pending writes: LRU 0, flush list 0, single page 0
>> Pages read 75602, created 6929, written 48273
>> 0.00 reads/s, 0.00 creates/s, 15000.00 writes/s
>> Buffer 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
>> ============================
>

Thread
CPU 100% + crashes ...Dilipan Sebastiampillai15 Dec
  • Re: CPU 100% + crashes ...Gleb Paharenko15 Dec
Re: CPU 100% + crashes ...Heikki Tuuri15 Dec
  • Re: CPU 100% + crashes ...Dilipan Sebastiampillai16 Dec
    • Re: CPU 100% + crashes ...Heikki Tuuri16 Dec
Re: CPU 100% + crashes ...Heikki Tuuri17 Dec
  • Re: CPU 100% + crashes ...Dilipan Sebastiampillai20 Dec