Hello. Have a look here: http://lists.mysql.com/mysql/194596 http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
If you feel uncomfortable with 10G ibdata size, you may want to switch to per-file tablespace: http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html Shaun Adams wrote: > 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 > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
