Hi, I'm having some serious performance issues that are stemming from MySQL writing data to tables. MySQL seems to cache writes, then write the block all at once. The major issue I'm having with this is that it when it does this it stops processing other requests until it's finished. I have listed my variables and .cnf file below in hopes that someone can point out what's causing this, or at least point me in the right direction. (I've been Google-ing/reading/testing/experimenting for the past 3 days to no avail).
This is all running on a dedicated server running Red Hat 8, with Dual 2.5GHz Xeons, 2GB ram, 50GB SCSI hard drive.... Variables: +---------------------------------+ | Variable_name | Value | +---------------------------------+ | back_log | 50 | | basedir | /usr/local/mysql/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set | latin1 | | character_sets | latin1 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 | /usr/local/mysql/var/ | | 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_min_word_len | 4 | | ft_max_word_len | 254 | | ft_max_word_len_for_sort | 20 | | ft_stopword_file | (built-in) | | have_bdb | NO | | have_crypt | YES | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | DISABLED | | have_openssl | NO | | have_query_cache | YES | | init_file | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | 0 | | innodb_fast_shutdown | ON | | innodb_flush_method | | | innodb_lock_wait_timeout | 50 | | innodb_log_arch_dir | ./ | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 637534208 | | language | /usr/local/mysql/share/mysql/english/ | | large_files_support | ON | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_update | OFF | | log_bin | ON | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | OFF | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_table_names | OFF | | max_allowed_packet | 1047552 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connections | 250 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_user_connections | 0 | | max_tmp_tables | 32 | | max_write_lock_count | 4294967295 | | myisam_max_extra_sort_file_size | 268435456 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | OFF | | myisam_sort_buffer_size | 134217728 | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | open_files_limit | 0 | | pid_file | /usr/local/mysql/var/spamnitdb.pid | | log_error | | | port | 3306 | | protocol_version | 10 | | read_buffer_size | 1044480 | | read_rnd_buffer_size | 262144 | | rpl_recovery_rank | 0 | | query_cache_limit | 1048576 | | query_cache_size | 0 | | query_cache_type | ON | | server_id | 1 | | slave_net_timeout | 3600 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slow_launch_time | 2 | | socket | /tmp/mysql.sock | | sort_buffer_size | 1048568 | | sql_mode | 0 | | table_cache | 1250 | | table_type | MYISAM | | thread_cache_size | 16 | | thread_stack | 262144 | | tx_isolation | REPEATABLE-READ | | timezone | EST | | tmp_table_size | 33554432 | | tmpdir | /tmp/ | | version | 4.0.11a-gamma-log | | wait_timeout | 28800 | +---------------------------------+ my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking set-variable = key_buffer=608M set-variable = max_allowed_packet=1M set-variable = table_cache=1250 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=128M set-variable = thread_cache=16 set-variable = thread_stack=256k #set-variable = max_heap_table_size=1200M set-variable = max_connections=250 #set-variable = delayed_insert_limit=10 #set-variable = delayed_insert_timeout=30 #set-variable = delayed_queue_size=100 set-variable = thread_concurrency=8 log-bin server-id = 1 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout Thanks, -- Jason H. West Software Engineer Softek Software International, Inc. 813 Pavilion Ct. McDonough, GA 30253 678-583-5718 [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php