I have a customer who has been in production for a few weeks now having converted from MyISM to INNODB. We have been experiencing a few problems with our application. With that said, could you take a look at the info below from my "show INNODB status" and let me know if you see any problems based off of the my.cnf configuration. Should I be worried about the "free buffers being at 0" Also, if you have any suggestions for modifying my.cnf, please let me know. Server is a Dell 1850, Dual P4 Processor with HT, RHEL4 64-bit, 4GB Memory. The only thing we have running on the server is MySQL v 4.0.26 (upgrading is not an option right now). The web is on another server. Our programmers are working on examining the code and queries. ======================================================== 060209 21:52:39 INNODB MONITOR OUTPUT ======================================================== Per second averages calculated from the last 58 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 32909, signal count 32897 Mutex spin waits 22210, rounds 96667, OS waits 3131 RW-shared spins 59775, OS waits 29579; RW-excl spins 122, OS waits 80 ------------ TRANSACTIONS ------------ Trx id counter 0 12549 Purge done for trx's n:o < 0 12265 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 7200, OS thread id 1147570528 MySQL thread id 40, query id 32699 localhost root show INNODB status -------- 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 10041 OS file reads, 98838 OS file writes, 28068 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, 65 inserts, 65 merged recs, 63 merges Hash table size 4980539, used cells 3352510, node heap has 8217 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2288366733 Log flushed up to 1 2288366733 Last checkpoint at 1 2288366733 0 pending log writes, 0 pending chkp writes 11189 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2761264906; in additional pool allocated 6054656 Buffer pool size 153600 Free buffers 0 Database pages 145383 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 49486, created 458759, written 822494 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 7200, id 1147169120, state: waiting for server activity Number of rows inserted 19473761, updated 1063, deleted 0, read 6117388 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ----------------------------
======================================================== MY. CNF ======================================================== # MySQL Server Instance Configuration File [mysqld] port=3306 socket=/tmp/mysql.sock basedir=/usr/local/mysql datadir=/var/lib/mysql default-character-set=latin1 lower_case_table_names=1 server-id=101 #log-slow-queries #long_query_time=1 #log max_allowed_packet=128M skip-bdb max_connections=100 thread_concurrency=10 open_files_limit=512 table_cache=512 thread_cache=20 query_cache_size=64M # replication #log-bin=/var/lib/mysql/binlog #server-id=90 #sync_binlog=1 # thread buffers read_buffer_size=128K read_rnd_buffer_size=256K sort_buffer_size=512K join_buffer_size=128K # myisam myisam_sort_buffer_size=205M key_buffer=384M # innodb innodb_additional_mem_pool_size=8M innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_buffer_pool_size=2400M innodb_log_file_size=512M innodb_data_file_path=ibdata1:2G:autoextend innodb_flush_method=O_DIRECT innodb_thread_concurrency=32 #*** Other Options *** [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash ======================================================== SHOW STATUS ======================================================== +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Aborted_clients | 0 | | Aborted_connects | 2 | | Bytes_received | 3049852512 | | Bytes_sent | 345306910 | | Com_admin_commands | 1 | | Com_alter_table | 832 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 98 | | Com_change_master | 0 | | Com_check | 0 | | Com_commit | 0 | | Com_create_db | 2 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 395 | | Com_delete | 99 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 396 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 3364 | | Com_insert_select | 4 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 391 | | Com_optimize | 0 | | 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 | 6621 | | Com_set_option | 14 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 0 | | Com_show_fields | 0 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1 | | Com_show_tables | 1 | | Com_show_variables | 0 | | Com_slave_start | 0 | | Com_slave_stop | 1 | | Com_truncate | 0 | | Com_unlock_tables | 391 | | Com_update | 684 | | Com_update_multi | 0 | | Connections | 40 | | Created_tmp_disk_tables | 4 | | Created_tmp_files | 6 | | Created_tmp_tables | 10 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 489 | | Handler_delete | 99 | | Handler_read_first | 78 | | Handler_read_key | 99081 | | Handler_read_next | 5752115 | | Handler_read_prev | 0 | | Handler_read_rnd | 5360754 | | Handler_read_rnd_next | 9179845 | | Handler_rollback | 30 | | Handler_update | 6306 | | Handler_write | 29036939 | | Key_blocks_used | 51313 | | Key_read_requests | 11973120 | | Key_reads | 274 | | Key_write_requests | 4325923 | | Key_writes | 54020 | | Max_used_connections | 1 | | Not_flushed_delayed_rows | 0 | | Not_flushed_key_blocks | 0 | | Open_files | 8 | | Open_streams | 0 | | Open_tables | 27 | | Opened_tables | 1357 | | Qcache_free_blocks | 1 | | Qcache_free_memory | 60040664 | | Qcache_hits | 19332 | | Qcache_inserts | 6556 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 48 | | Qcache_queries_in_cache | 6035 | | Qcache_total_blocks | 12091 | | Questions | 32696 | | Rpl_status | NULL | | Select_full_join | 1 | | Select_full_range_join | 0 | | Select_range | 16 | | Select_range_check | 0 | | Select_scan | 71 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 32 | | Sort_merge_passes | 44 | | Sort_range | 4 | | Sort_rows | 5360754 | | Sort_scan | 12 | | Table_locks_immediate | 8810 | | Table_locks_waited | 0 | | Threads_cached | 1 | | Threads_connected | 1 | | Threads_created | 2 | | Threads_running | 1 | | Uptime | 21073 | +--------------------------+------------+ 133 rows in set (0.02 sec)
