I currently have a customer who we switched over to INNODB from MyISM. It's a Dell Server with 4GB or Memory and RHEL4 64-bit. It's a fairly big database.... the size of the MyISM folders (before we converted to INNODB) was about 2-3Gigs. Questions: 1. The ibdata1 file size is 10GB. Does that sound right? Should this file be this big? 2. Once a week, I have to perform HUGE insert imports into the database. What is the recommended procedure for doing this? Clearing out memory with a FLUSH before I start the import, setting innodb_flush_log_at_trx_commit to 0... any other suggestions 3. Could you all recommend any tips you have used to increase performance using INNODB (from a system admin's perspective). 4. Finally, could you take a look at my settings below as well as the system variables and recommend any changes. Thanks. I would appreciate as many responses as possible. Shaun ===================================== 060207 19:04:03 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 11216, signal count 11214 Mutex spin waits 31985, rounds 62213, OS waits 135 RW-shared spins 21945, OS waits 10884; RW-excl spins 61, OS waits 14 ------------ TRANSACTIONS ------------ Trx id counter 0 110703900 Purge done for trx's n:o < 0 110703871 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 6297, OS thread id 1147570528 MySQL thread id 66, query id 10060 localhost root show INNODB status ---TRANSACTION 0 0, not started, process no 6297, OS thread id 1147771232 MySQL thread id 2, query id 2305 10.200.60.34 admin -------- 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 27717 OS file reads, 2528 OS file writes, 339 OS fsyncs 47.93 reads/s, 18477 avg bytes/read, 87.81 writes/s, 14.19 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 68, seg size 70, 755 inserts, 793 merged recs, 733 merges Hash table size 4980539, used cells 263653, node heap has 401 buffer(s) 6790.51 hash searches/s, 852.82 non-hash searches/s --- LOG --- Log sequence number 5 1118786578 Log flushed up to 5 1118786578 Last checkpoint at 5 1118786578 0 pending log writes, 0 pending chkp writes 553 log i/o's done, 9.31 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2761248304; in additional pool allocated 6053120 Buffer pool size 153600 Free buffers 94978 Database pages 58221 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 58209, created 12, written 1996 55.00 reads/s, 0.00 creates/s, 76.31 writes/s Buffer pool hit rate 998 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 6297, id 1147169120, state: waiting for server activity Number of rows inserted 171, updated 235, deleted 0, read 1793591 0.87 inserts/s, 1.00 updates/s, 0.00 deletes/s, 9788.39 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ============================ SHOW VARIABLES ============================
Variable_name Value back_log 50 basedir /usr/local/mysql-standard-4.0.26-unknown-linux-gnu-x86_64-glibc23/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 convert_character_set datadir /var/lib/mysql/ default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -><()~*:""&| ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_min_word_len 4 ft_stopword_file (built-in) have_bdb NO have_crypt YES have_innodb YES have_isam YES have_openssl NO have_query_cache YES have_raid NO have_symlink YES init_file innodb_additional_mem_pool_size 8388608 innodb_autoextend_increment 8 innodb_buffer_pool_size 2516582400 innodb_data_file_path ibdata1:2G:autoextend innodb_data_home_dir innodb_fast_shutdown ON innodb_file_io_threads 4 innodb_flush_log_at_trx_commit 2 innodb_flush_method O_DIRECT innodb_force_recovery 0 innodb_lock_wait_timeout 50 innodb_log_arch_dir ./ innodb_log_archive OFF innodb_log_buffer_size 8388608 innodb_log_file_size 268435456 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_table_locks ON innodb_thread_concurrency 8 interactive_timeout 28800 join_buffer_size 126976 key_buffer_size 402653184 language /usr/local/mysql-standard-4.0.26-unknown-linux-gnu-x86_64-glibc23/share/mysq l/english/ large_files_support ON license GPL local_infile ON locked_in_memory OFF log OFF log_bin ON log_error log_slave_updates OFF log_slow_queries OFF log_update OFF log_warnings 1 long_query_time 10 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 1 max_allowed_packet 134216704 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors 10 max_connections 100 max_delayed_threads 20 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 268435456 myisam_max_sort_file_size 9223372036854775807 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 214958080 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF open_files_limit 1134 pid_file /var/lib/mysql/PEMINIC-100.pid port 3306 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_size 67108864 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size 126976 read_only OFF read_rnd_buffer_size 258048 rpl_recovery_rank 0 server_id 100 skip_external_locking ON skip_networking OFF skip_show_database OFF slave_net_timeout 3600 slow_launch_time 2 socket /tmp/mysql.sock sort_buffer_size 524280 sql_mode 0 table_cache 512 table_type MYISAM thread_cache_size 20 thread_stack 196608 timezone EST tmp_table_size 33554432 tmpdir /tmp/ transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ version 4.0.26-standard-log version_comment Official MySQL-standard binary version_compile_os unknown-linux-gnu wait_timeout 28800 ============================ SHOW STATUS ============================ Variable_name Value Aborted_clients 0 Aborted_connects 0 Bytes_received 2243771 Bytes_sent 4155458 Com_admin_commands 2 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_change_db 699 Com_change_master 0 Com_check 0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_delete 0 Com_delete_multi 0 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 293 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 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 8951 Com_set_option 0 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 2 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 2 Com_show_slave_status 0 Com_show_status 1 Com_show_tables 1 Com_show_variables 3 Com_slave_start 0 Com_slave_stop 0 Com_truncate 0 Com_unlock_tables 0 Com_update 518 Com_update_multi 0 Connections 117 Created_tmp_disk_tables 87 Created_tmp_files 3 Created_tmp_tables 237 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 0 Handler_delete 0 Handler_read_first 355 Handler_read_key 1378655 Handler_read_next 3234153 Handler_read_prev 0 Handler_read_rnd 2025997 Handler_read_rnd_next 487643 Handler_rollback 113 Handler_update 2019640 Handler_write 2020645 Key_blocks_used 40 Key_read_requests 2019841 Key_reads 6 Key_write_requests 106 Key_writes 0 Max_used_connections 7 Not_flushed_delayed_rows 0 Not_flushed_key_blocks 0 Open_files 22 Open_streams 0 Open_tables 50 Opened_tables 56 Qcache_free_blocks 2 Qcache_free_memory 63613664 Qcache_hits 12764 Qcache_inserts 8891 Qcache_lowmem_prunes 0 Qcache_not_cached 60 Qcache_queries_in_cache 3037 Qcache_total_blocks 6103 Questions 23349 Rpl_status NULL Select_full_join 78 Select_full_range_join 0 Select_range 44 Select_range_check 0 Select_scan 328 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 3 Sort_merge_passes 0 Sort_range 1682 Sort_rows 6378 Sort_scan 267 Table_locks_immediate 10386 Table_locks_waited 0 Threads_cached 5 Threads_connected 3 Threads_created 8 Threads_running 3 Uptime 282
