MySQL Forums
Forum List  »  Performance

write/insert performance problem
Posted by: scott fagg
Date: April 07, 2005 11:38PM

Most of my mysql servers (3.23.58 & 5.0.03) exhibit reasonable write performance, with the exception of one box. Bulk inserts (importing a dump from mysqldump) take 24s on most boxes but upto 24 minutes on one box. The box in question is actually the newest, fastest box, and is fast with all other operations.

Running bonnie++ on all the servers in questions produces reasonable results, including on the box with the write/insert problem.

For example, comparing 2 servers, running my import script, produces the following results. The various results are for the case of using innodb tables vs myisam tables, and running with and without binary logs enabled. Innodb imparts a modest overhead on the good server, and enabling logging seems to have little impact. On the problematic server, the myisam import runs faster, as to be expected with the better hardware, but switching to innodb imparts an incredible performance penalty! The kb/s rates from running 'vmstat 1' while the script was running.

Servers were running FC2 & FC3 linux. Switching between the FC3 kernel and a vanilla kernel did not help the mis-behaving server. Switching between mysql3 and mysql5 had little impact.

So why is innodb so slow on one particular server ? bonnie++ suggest that the server is quite capable of sustaining writes to disc at around 44,000kb/s.

On the well-behaved server:

- myisam, no bin-log = 61 seconds, kb/s varied a lot
- myisam, bin-log = 69 seconds, kb/s varied a lot
- innodb, no bin-log = 230 seconds, 3000kb/s
- innodb, bin-log = 230 seconds, 3000kb/s

On the mis-behaving server:

- myisam, no bin-log = 24 seconds, 5000kb/s
- myisam, bin-log = 24 seconds, 5000kb/s
- innodb, no bin-log = 24 minutes, 800kb/s
- innodb, bin-log = 24 minutes, 800kb/s

Options: ReplyQuote

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.