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

We just upgraded from MySQL 5.1 to 5.5. An insert script that used to take ~3 mins to insert ~200k rows into a MyISAM table with 3 indexes now takes ~8 mins! Removing the indexes solves the issue!?

Reads(selects) are fine (faster in 5.5 than 5.1)

Is something different about index behavior or could there be some oddball config parameter that may need to be tweaked?

Thanks in advance for any pointers!

System Info:

Fedora 17, Mysql 5.5.29-log, 16GB RAM

MyISAM Key Buffer = 4GB

MyISAM index size = ~800 MB

MyISAM table size = 4.4 GB

INNODB Buffer Pool = 8GB

INNODB table+index size = ~12GB

  • Here's the table definition per request:

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

Answers


Well, after lots of troubleshooting, the issue was narrowed down to bad drives. Not MySQL 5.5. Everything is fine now with different drives..


Need Your Help

Can't see XML response from PHP cURL api Call

php xml curl

I'm using a remote API with cURL and querystrings. The issue I'm having is that I can't see the data being returned to me (which is in XML format). To start, here's my code:

Using Omniture tagging with the jcycle plug-in

jquery adobe-analytics jcycle

Has anyone ever done omniture tagging with the jcycle plug-in? What I need to do is have the onmiture tags know what item was clicked without having a page refresh. So, if you were using the jcycle

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.