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"
<[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
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
============================
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
