Skip to content

Instantly share code, notes, and snippets.

@kennethkalmer

kennethkalmer/gist:333794

Created March 16, 2010 09:40
Show Gist options
  • Save kennethkalmer/333794 to your computer and use it in GitHub Desktop.
Save kennethkalmer/333794 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
100316 11:24:35 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
----------
BACKGROUND THREAD
----------
fsync callers: 15 buffer pool, 0 other, 1 checkpoint, 6 log aio, 23 log sync, 0 archive
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 140, signal count 69
Mutex spin waits 47, rounds 940, OS waits 0
RW-shared spins 247, OS waits 90; RW-excl spins 5, OS waits 45
Spin rounds per wait: 20.00 mutex, 20.00 RW-shared, 200.00 RW-excl
--------
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 (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
I/O thread 14 state: waiting for i/o request (write thread)
I/O thread 15 state: waiting for i/o request (write thread)
I/O thread 16 state: waiting for i/o request (write thread)
I/O thread 17 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
383 OS file reads, 100 OS file writes, 45 OS fsyncs
0.25 reads/s, 16384 avg bytes/read, 2.87 writes/s, 1.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
16 inserts, 16 merged recs, 11 merges
Hash table size 553253, used cells 13987, node heap has 15 buffer(s)
120371.70 hash searches/s, 1489.81 non-hash searches/s
---
LOG
---
Log sequence number 83 811005804
Log flushed up to 83 811005804
Last checkpoint at 83 811005804
Max checkpoint age 7310501
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
34 log i/o's done, 0.62 log i/o's/second, 23 syncs, 7 checkpoints
log sync callers: 74 buffer pool, background 86 sync and 0 async, 0 internal, checkpoint 7 sync and 0 async, 0 archive, commit 12 sync and 0 async
log sync syncers: 4 buffer pool, background 6 sync and 0 async, 0 internal, checkpoint 1 sync and 0 async, 0 archive, commit 12 sync and 0 async
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 0; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2462932 (2213012 + 249920)
Page hash 69572
Dictionary cache 1713420 (1660884 + 52536)
File system 41792 (41336 + 456)
Lock system 171876 (166436 + 5440)
Recovery system 0 (0 + 0)
Threads 41876 (41348 + 528)
innodb_io_pattern 0 (0 + 0)
Buffer pool size 8192
Buffer pool size, bytes 134217728
Free buffers 7623
Database pages 554
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 553, created 1, written 74
0.25 reads/s, 0.12 creates/s, 2.25 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 29041, id 2730666864, state: sleeping
Number of rows inserted 5, updated 7, deleted 0, read 6433525
0.00 inserts/s, 0.25 updates/s, 0.00 deletes/s, 121831.90 reads/s
------------
TRANSACTIONS
------------
Trx id counter 0 3058352645
Purge done for trx's n:o < 0 3058352591 undo n:o < 0 0
History list length 1
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 29041, OS thread id 2752908144
MySQL thread id 18, query id 646 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 3058352589, not started, process no 29041, OS thread id 2688445296
MySQL thread id 7, query id 608 Has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION 0 3058352561, not started, process no 29041, OS thread id 2687363952
MySQL thread id 10, query id 536 192.168.0.40 courier
---TRANSACTION 0 3058352434, not started, process no 29041, OS thread id 2686417776
MySQL thread id 17, query id 377 192.168.0.40 courier
---TRANSACTION 0 3058352607, not started, process no 29041, OS thread id 2686552944
MySQL thread id 16, query id 598 192.168.0.40 courier
---TRANSACTION 0 3058352630, not started, process no 29041, OS thread id 2686688112
MySQL thread id 15, query id 644 192.168.0.40 courier
---TRANSACTION 0 3058352446, not started, process no 29041, OS thread id 2686823280
MySQL thread id 14, query id 388 192.168.0.40 courier
---TRANSACTION 0 3058352581, not started, process no 29041, OS thread id 2686958448
MySQL thread id 13, query id 561 192.168.0.40 courier
---TRANSACTION 0 3058352430, not started, process no 29041, OS thread id 2687093616
MySQL thread id 12, query id 373 192.168.0.40 courier
---TRANSACTION 0 3058352572, not started, process no 29041, OS thread id 2687228784
MySQL thread id 11, query id 550 192.168.0.40 courier
---TRANSACTION 0 3058352426, not started, process no 29041, OS thread id 2687499120
MySQL thread id 9, query id 369 192.168.0.40 courier
---TRANSACTION 0 3058352443, not started, process no 29041, OS thread id 2687769456
MySQL thread id 5, query id 384 192.168.0.40 courier
---TRANSACTION 0 3058352560, not started, process no 29041, OS thread id 2687634288
MySQL thread id 8, query id 534 192.168.0.40 courier
---TRANSACTION 0 3058352643, not started, process no 29041, OS thread id 2687904624
MySQL thread id 4, query id 643 192.168.0.40 courier
---TRANSACTION 0 3058352564, not started, process no 29041, OS thread id 2688039792
MySQL thread id 3, query id 539 192.168.0.40 courier
---TRANSACTION 0 3058352440, not started, process no 29041, OS thread id 2688174960
MySQL thread id 2, query id 381 192.168.0.40 courier
---TRANSACTION 0 3058352644, not started, process no 29041, OS thread id 2688310128
MySQL thread id 1, query id 645 192.168.0.40 courier
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| have_innodb | YES |
| innodb_additional_mem_pool_size | 16777216 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 134217728 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_io_capacity | 200 |
| innodb_ibuf_max_size | 67108864 |
| innodb_ibuf_active_contract | 0 |
| innodb_ibuf_accel_rate | 100 |
| innodb_flush_neighbor_pages | 1 |
| innodb_read_ahead | both |
| innodb_enable_unsafe_group_commit | 0 |
| innodb_adaptive_checkpoint | none |
| innodb_read_io_threads | 8 |
| innodb_write_io_threads | 8 |
| innodb_use_sys_malloc | ON |
| innodb_fast_recovery | OFF |
| innodb_thread_concurrency_timer_based | OFF |
| innodb_extra_rsegments | 0 |
| innodb_dict_size_limit | 0 |
| innodb_io_pattern_trace | 0 |
| innodb_io_pattern_trace_running | 0 |
| innodb_io_pattern_size_limit | 0 |
| innodb_use_legacy_cardinality_algorithm | ON |
+-----------------------------------------+------------------------+
57 rows in set (0.00 sec)
Raw
gistfile2.txt
InnoDB: The InnoDB memory heap is disabled
InnoDB: use atomic builtins.
100316 11:23:52 InnoDB: Started; log sequence number 83 810993550
100316 11:23:52 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
100316 11:23:52 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.90-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.90-r2
100316 11:23:52 [Note] Slave SQL thread initialized, starting replication in log 'mysql-0.000047' at position 3063691, relay log './mysqld-relay-bin.001047' position: 2568046
100316 11:23:52 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysql-0.000047' at position 3063691
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment