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
