Re: 5.1 to 5.5 upgrade causes significant write slowdown (MyISAM + indexes)

[Ben Wallach]

* Dumped and re-imported data (did not replace binaries)

* Tried removing and then adding indexes back in...same perfromance results..This is happening across the board on various tables. We have heavy write jobs (inserts/updates..) that run at night. We are seeing a 30%-40% slowdown across the board as compared to before the upgrade. Reads are fine ( faster actually)

* We only changed a couple things in my.cnf (since we doubled our RAM from 8GB to 16GB):
- doubled the INNODB Buffer Pool
(we have a seperate INNODB database as well..but this is not being written to with the jobs in question)
- doubled the MYISM Key Buffer
- doubled the innodb log file size (from 256MB to 512MB)..We are using 4.

Here is our my.cnf:
-------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

max_connections = 2048
back_log = 128
max_connect_errors = 1000
connect_timeout = 10
wait_timeout = 1800
max_allowed_packet = 16M
net_buffer_length = 8K
log_short_format
table_cache = 4096
join_buffer_size = 8M
tmp_table_size = 64M
sort_buffer_size = 8M

thread_cache_size = 64
thread_concurrency = 8
thread_stack = 192K

key_buffer_size = 4096M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_repair_threads = 2

innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 32M
innodb_log_files_in_group = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 32

log_bin = mysql-bin
server_id = 1
binlog-ignore-db=myDB1
binlog-ignore-db=myDB2
expire-logs-days = 3

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 256M
sort_buffer = 256M
read_buffer = 64M
write_buffer = 64M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


* Here is the table defintion for one of the tables in question:

CREATE TABLE MY_TABLE

( MY_TABLE_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
FIELD_1 char(1) NOT NULL,
FIELD_2 int(11) NOT NULL,
FIELD_3 varchar(64) NOT NULL,
FIELD_4 varchar(64) DEFAULT NULL,
FIELD_5 varchar(64) DEFAULT NULL,
FIELD_6 varchar(8) DEFAULT NULL,
FIELD_7 varchar(64) DEFAULT NULL,
FIELD_8 varchar(64) DEFAULT NULL,
FIELD_9 varchar(64) DEFAULT NULL,
FIELD_10 varchar(16) DEFAULT NULL,
FIELD_11 varchar(124) DEFAULT NULL,
FIELD_12 varchar(124) DEFAULT NULL,
FIELD_13 varchar(124) DEFAULT NULL,
FIELD_14 varchar(32) DEFAULT NULL,
FIELD_15 varchar(32) DEFAULT NULL,
FIELD_16 varchar(64) DEFAULT NULL,
FIELD_17 varchar(64) DEFAULT NULL,
FIELD_18 varchar(64) DEFAULT NULL,
FIELD_19 varchar(64) DEFAULT NULL,
FIELD_20 char(1) DEFAULT NULL,
FIELD_21 text,
FIELD_22 text,
FIELD_23 text,
FIELD_24 varchar(32) DEFAULT NULL,
FIELD_25 varchar(64) DEFAULT NULL,
FIELD_26 varchar(16) DEFAULT NULL,
FIELD_27 varchar(64) DEFAULT NULL,
FIELD_28 varchar(64) DEFAULT NULL,
FIELD_29 text,
FIELD_30 int(11) NOT NULL,
FIELD_31 varchar(16) DEFAULT NULL,
FIELD_32 varchar(16) DEFAULT NULL,
CREATION_DATE int(11) NOT NULL,
MODIFICATION_DATE timestamp NULL DEFAULT NULL,
PRIMARY KEY (MY_TABLE_ID),
KEY I_FIELD_4 (FIELD_4),
KEY I_FIELD_2 (FIELD_2),
KEY I_FIELD_3 (FIELD_3) ) ENGINE=MyISAM DEFAULT CHARSET=latin1