MySQL Forums
Forum List  »  MyISAM

Re: 5.1 to 5.5 upgrade causes significant write slowdown (MyISAM + indexes)
Posted by: Ben Wallach
Date: January 24, 2013 12:16PM

* 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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 5.1 to 5.5 upgrade causes significant write slowdown (MyISAM + indexes)
2050
January 24, 2013 12:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.