Hardware: --------- IBM eServer xSeries 225 2 x Xeon 2,8 GHz (HT on) 2048 MB ECC-RAM IBM ServeRAID-5i with 3 x IBM-ESXS MAP3735M as RAID5 Windows 2003 Server Standard Edition, SP1 all available Fixes installed. # MySQL Server Instance Configuration File # ---------------------------------------------------------------------- # ---------------------------------------------------------------------- # SERVER SECTION # ---------------------------------------------------------------------- [mysqld] port = 3306 basedir = "C:/Programme/MySQL/MySQL Server 5.0/" datadir = "D:/MySQL Datafiles/" tmpdir = "D:/MySQL Datafiles/temp/" default-character-set = latin1 default-collation = latin1_german2_ci default-storage-engine = INNODB sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI" max_connections = 100 max_allowed_packet = 16M max_heap_table_size = 64M sort_buffer_size = 8M join_buffer_size = 8M query_cache_size = 16M query_cache_limit = 1M table_cache = 512 tmp_table_size = 64M thread_cache_size = 8 #********** Log Specific options ********** log_slow_queries long_query_time=2 #********** MyISAM Specific options ********** key_buffer_size = 4M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G #********** INNODB Specific options ********** innodb_data_home_dir = "D:/MySQL Datafiles/" innodb_additional_mem_pool_size = 4M innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_buffer_pool_size = 512M innodb_log_file_size = 256M innodb_file_io_threads = 4 innodb_thread_concurrency = 0 innodb_lock_wait_timeout = 10 innodb_support_xa = 0 ===================================== 060808 16:18:45 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 23 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 10664, signal count 10657 Mutex spin waits 43653, rounds 114767, OS waits 1191 RW-shared spins 15630, OS waits 7772; RW-excl spins 1463, OS waits 972 ------------ TRANSACTIONS ------------ Trx id counter 0 3613440 Purge done for trx's n:o < 0 3612960 undo n:o < 0 0 History list length 22 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 2992 MySQL thread id 635, query id 278025 localhost 127.0.0.1 root show innodb status ---TRANSACTION 0 3613332, not started, OS thread id 3168 MySQL thread id 629, query id 277707 schreiner.intern.ipro.de 172.16.1.18 Schrei ner ---TRANSACTION 0 3613029, not started, OS thread id 3576 MySQL thread id 627, query id 277854 volkmann.intern.ipro.de 172.16.6.17 Volkman n ---TRANSACTION 0 3613180, not started, OS thread id 2972 MySQL thread id 626, query id 277948 duerrwaechterpc.intern.ipro.de 172.16.222.5 7 D³rrwõchter ---TRANSACTION 0 3613286, not started, OS thread id 3548 MySQL thread id 624, query id 277573 koehnlei.intern.ipro.de 172.16.222.24 K÷hnl ein ---TRANSACTION 0 3613439, not started, OS thread id 660 MySQL thread id 619, query id 278024 dokument1.intern.ipro.de 172.16.222.144 Dok ument ---TRANSACTION 0 3613216, not started, OS thread id 3116 MySQL thread id 615, query id 277435 lenhardt.intern.ipro.de 172.16.7.1 Lenhardt ---TRANSACTION 0 3612980, not started, OS thread id 2760 MySQL thread id 601, query id 277802 kleinpc.intern.ipro.de 172.16.3.9 Klein ---TRANSACTION 0 3613229, not started, OS thread id 3880 MySQL thread id 600, query id 277456 ge-laptop.intern.ipro.de 172.16.222.142 Gen tner ---TRANSACTION 0 3613255, not started, OS thread id 3460 MySQL thread id 585, query id 277515 akillilar.intern.ipro.de 172.16.7.4 Akillil ar ---TRANSACTION 0 3613045, not started, OS thread id 1360 MySQL thread id 583, query id 277860 frankenhauserpc.intern.ipro.de 172.16.222.1 19 Frankenhauser ---TRANSACTION 0 3600701, not started, OS thread id 1324 MySQL thread id 581, query id 245526 volkmann.intern.ipro.de 172.16.6.17 Volkman n ---TRANSACTION 0 3613265, not started, OS thread id 1908 MySQL thread id 578, query id 277527 BERLING-MOBIL 172.16.222.61 Berling ---TRANSACTION 0 3613407, not started, OS thread id 1444 MySQL thread id 576, query id 277926 gilke.intern.ipro.de 172.16.3.2 Gilke ---TRANSACTION 0 3613016, not started, OS thread id 3976 MySQL thread id 570, query id 277828 rolandw.intern.ipro.de 172.16.222.153 Walte r ---TRANSACTION 0 3613188, not started, OS thread id 416 MySQL thread id 562, query id 277964 sinnpc.intern.ipro.de 172.16.222.1 Sinn ---TRANSACTION 0 3612039, not started, OS thread id 1880 MySQL thread id 560, query id 277776 knoetig.intern.ipro.de 172.16.222.81 Kn÷tig ---TRANSACTION 0 3613221, not started, OS thread id 3092 MySQL thread id 559, query id 277594 hl1.ns.intern.ipro.de 172.16.222.35 K÷rner ---TRANSACTION 0 3612944, not started, OS thread id 3940 MySQL thread id 558, query id 277719 fiedler.intern.ipro.de 172.16.7.2 Fiedler ---TRANSACTION 0 3613129, not started, OS thread id 2476 MySQL thread id 555, query id 277932 kloseneu.intern.ipro.de 172.16.7.55 Klose-R ein ---TRANSACTION 0 3613077, not started, OS thread id 3796 MySQL thread id 554, query id 277896 zikeli.intern.ipro.de 172.16.6.4 Zikeli ---TRANSACTION 0 3613261, not started, OS thread id 396 MySQL thread id 553, query id 277523 hoerster.intern.ipro.de 172.16.3.22 H÷rster -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (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 122432 OS file reads, 119933 OS file writes, 28158 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 2.78 writes/s, 1.91 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 158 inserts, 158 merged recs, 154 merges Hash table size 2212699, used cells 130848, node heap has 134 buffer(s) 7.39 hash searches/s, 2.83 non-hash searches/s --- LOG --- Log sequence number 16 1089451908 Log flushed up to 16 1089451908 Last checkpoint at 16 1089449876 0 pending log writes, 0 pending chkp writes 22153 log i/o's done, 1.74 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 579110226; in additional pool allocated 1187712 Buffer pool size 32768 Free buffers 0 Database pages 32634 Modified db pages 7 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 122422, created 71439, written 94157 0.00 reads/s, 0.00 creates/s, 0.96 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 id 1292, state: sleeping Number of rows inserted 2806430, updated 1445, deleted 3260, read 2095962815 1.65 inserts/s, 0.00 updates/s, 0.00 deletes/s, 217594.41 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 060808 16:24:19 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 25 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 10696, signal count 10689 Mutex spin waits 43758, rounds 115257, OS waits 1199 RW-shared spins 15668, OS waits 7791; RW-excl spins 1463, OS waits 972 ------------ TRANSACTIONS ------------ Trx id counter 0 3614032 Purge done for trx's n:o < 0 3613625 undo n:o < 0 0 History list length 28 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 2992 MySQL thread id 635, query id 279739 localhost 127.0.0.1 root show innodb status ---TRANSACTION 0 3613678, not started, OS thread id 3168 MySQL thread id 629, query id 279732 schreiner.intern.ipro.de 172.16.1.18 Schrei ner ---TRANSACTION 0 3613703, not started, OS thread id 3576 MySQL thread id 627, query id 279466 volkmann.intern.ipro.de 172.16.6.17 Volkman n ---TRANSACTION 0 3613725, not started, OS thread id 2972 MySQL thread id 626, query id 279558 duerrwaechterpc.intern.ipro.de 172.16.222.5 7 D³rrwõchter ---TRANSACTION 0 3613665, not started, OS thread id 3548 MySQL thread id 624, query id 279714 koehnlei.intern.ipro.de 172.16.222.24 K÷hnl ein ---TRANSACTION 0 3614031, not started, OS thread id 660 MySQL thread id 619, query id 279726 dokument1.intern.ipro.de 172.16.222.144 Dok ument ---TRANSACTION 0 3613629, not started, OS thread id 3116 MySQL thread id 615, query id 279623 lenhardt.intern.ipro.de 172.16.7.1 Lenhardt ---TRANSACTION 0 3613693, not started, OS thread id 2760 MySQL thread id 601, query id 279452 kleinpc.intern.ipro.de 172.16.3.9 Klein ---TRANSACTION 0 3613639, not started, OS thread id 3880 MySQL thread id 600, query id 279646 ge-laptop.intern.ipro.de 172.16.222.142 Gen tner ---TRANSACTION 0 3613650, not started, OS thread id 3460 MySQL thread id 585, query id 279696 akillilar.intern.ipro.de 172.16.7.4 Akillil ar ---TRANSACTION 0 3613708, not started, OS thread id 1360 MySQL thread id 583, query id 279472 frankenhauserpc.intern.ipro.de 172.16.222.1 19 Frankenhauser ---TRANSACTION 0 3600701, not started, OS thread id 1324 MySQL thread id 581, query id 245526 volkmann.intern.ipro.de 172.16.6.17 Volkman n ---TRANSACTION 0 3613659, not started, OS thread id 1908 MySQL thread id 578, query id 279708 BERLING-MOBIL 172.16.222.61 Berling ---TRANSACTION 0 3613731, not started, OS thread id 1444 MySQL thread id 576, query id 279435 gilke.intern.ipro.de 172.16.3.2 Gilke ---TRANSACTION 0 3613849, not started, OS thread id 3976 MySQL thread id 570, query id 279560 rolandw.intern.ipro.de 172.16.222.153 Walte r ---TRANSACTION 0 3614007, not started, OS thread id 416 MySQL thread id 562, query id 279635 sinnpc.intern.ipro.de 172.16.222.1 Sinn ---TRANSACTION 0 3612039, not started, OS thread id 1880 MySQL thread id 560, query id 279436 knoetig.intern.ipro.de 172.16.222.81 Kn÷tig ---TRANSACTION 0 3613634, not started, OS thread id 3092 MySQL thread id 559, query id 279629 hl1.ns.intern.ipro.de 172.16.222.35 K÷rner ---TRANSACTION 0 3613683, not started, OS thread id 3940 MySQL thread id 558, query id 279738 fiedler.intern.ipro.de 172.16.7.2 Fiedler ---TRANSACTION 0 3613720, not started, OS thread id 2476 MySQL thread id 555, query id 279552 kloseneu.intern.ipro.de 172.16.7.55 Klose-R ein ---TRANSACTION 0 3613713, not started, OS thread id 3796 MySQL thread id 554, query id 279497 zikeli.intern.ipro.de 172.16.6.4 Zikeli ---TRANSACTION 0 3613660, not started, OS thread id 396 MySQL thread id 553, query id 279702 hoerster.intern.ipro.de 172.16.3.22 H÷rster -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (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 122432 OS file reads, 120286 OS file writes, 28362 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.44 writes/s, 0.20 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 158 inserts, 158 merged recs, 154 merges Hash table size 2212699, used cells 130849, node heap has 134 buffer(s) 0.36 hash searches/s, 0.20 non-hash searches/s --- LOG --- Log sequence number 16 1089471702 Log flushed up to 16 1089471702 Last checkpoint at 16 1089471702 0 pending log writes, 0 pending chkp writes 22315 log i/o's done, 0.12 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 579110226; in additional pool allocated 1214080 Buffer pool size 32768 Free buffers 0 Database pages 32634 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 122422, created 71439, written 94327 0.00 reads/s, 0.00 creates/s, 0.28 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 id 1292, state: waiting for server activity Number of rows inserted 2806563, updated 1451, deleted 3260, read 2113916110 0.08 inserts/s, 0.00 updates/s, 0.00 deletes/s, 11127.39 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ mysql> show global status; +-----------------------------------+------------+ | Variable_name | Value | +-----------------------------------+------------+ | Aborted_clients | 18 | | Aborted_connects | 43 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 100151120 | | Bytes_sent | 934335801 | | Com_admin_commands | 161 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 17 | | Com_change_db | 6 | | Com_change_master | 0 | | Com_check | 210 | | Com_checksum | 0 | | Com_commit | 17306 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 3260 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 1 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 19607 | | 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 | 210 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 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_revoke_all | 0 | | Com_rollback | 11 | | Com_savepoint | 0 | | Com_select | 79766 | | Com_set_option | 34728 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 4 | | Com_show_column_types | 0 | | Com_show_create_db | 2 | | Com_show_create_table | 225 | | Com_show_databases | 0 | | Com_show_errors | 0 | | Com_show_fields | 1525 | | Com_show_grants | 0 | | Com_show_innodb_status | 1032 | | Com_show_keys | 725 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_ndb_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 1 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1035 | | Com_show_storage_engines | 4 | | Com_show_tables | 46 | | Com_show_triggers | 105 | | Com_show_variables | 10 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_unlock_tables | 1 | | Com_update | 1758 | | Com_update_multi | 0 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 636 | | Created_tmp_disk_tables | 4873 | | Created_tmp_files | 5 | | Created_tmp_tables | 6994 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 41577 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 16231 | | Handler_read_key | 2213230 | | Handler_read_next | 2849751 | | Handler_read_prev | 0 | | Handler_read_rnd | 386115 | | Handler_read_rnd_next | 2125848088 | | Handler_rollback | 10 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 3126712 | | Innodb_buffer_pool_pages_data | 32634 | | Innodb_buffer_pool_pages_dirty | 7 | | Innodb_buffer_pool_pages_flushed | 94480 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_latched | 1 | | Innodb_buffer_pool_pages_misc | 134 | | Innodb_buffer_pool_pages_total | 32768 | | Innodb_buffer_pool_read_ahead_rnd | 1500 | | Innodb_buffer_pool_read_ahead_seq | 354 | | Innodb_buffer_pool_read_requests | 321741348 | | Innodb_buffer_pool_reads | 35743 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 13352068 | | Innodb_data_fsyncs | 28608 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 2008993792 | | Innodb_data_reads | 122496 | | Innodb_data_writes | 120666 | | Innodb_data_written | 111793152 | | Innodb_dblwr_pages_written | 94480 | | Innodb_dblwr_writes | 3112 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 2664106 | | Innodb_log_writes | 20083 | | Innodb_os_log_fsyncs | 22369 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 1309593088 | | Innodb_page_size | 16384 | | Innodb_pages_created | 71441 | | Innodb_pages_read | 122486 | | Innodb_pages_written | 94480 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | | Innodb_rows_deleted | 3260 | | Innodb_rows_inserted | 2806741 | | Innodb_rows_read | 2138566588 | | Innodb_rows_updated | 1458 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 3586 | | Key_blocks_used | 476 | | Key_read_requests | 2443 | | Key_reads | 490 | | Key_write_requests | 0 | | Key_writes | 0 | | Last_query_cost | 0.000000 | | Max_used_connections | 27 | | Not_flushed_delayed_rows | 0 | | Open_files | 1 | | Open_streams | 0 | | Open_tables | 30 | | Opened_tables | 2600 | | Qcache_free_blocks | 1828 | | Qcache_free_memory | 9248200 | | Qcache_hits | 117788 | | Qcache_inserts | 75412 | | Qcache_lowmem_prunes | 2986 | | Qcache_not_cached | 6407 | | Qcache_queries_in_cache | 5049 | | Qcache_total_blocks | 11980 | | Questions | 281562 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 18 | | Select_range | 4373 | | Select_range_check | 0 | | Select_scan | 18226 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 6 | | Sort_merge_passes | 0 | | Sort_range | 20053 | | Sort_rows | 398717 | | Sort_scan | 4781 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 180502 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 5 | | Threads_connected | 22 | | Threads_created | 43 | | Threads_running | 2 | | Uptime | 96129 | +-----------------------------------+------------+ 245 rows in set (0.00 sec) Win2003 Task-Manager -------------------- Handles: 14701 Threads: 40