What are the problems you've been experiencing? Did you convert all tables? How big is the database?
On 2/9/06, Shaun Adams <[EMAIL PROTECTED]> wrote: > > 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) > > >
