Andy,
thank you for your benchmark :). I was also going to measure these,
but have not had time yet. But what parameters you used in
my.cnf? You have a lot of RAM in your system, and you could make
the Innobase buffer pool big.
>I've benchmarked a few scenarios to load mysqldumped data into an
>innobase-table (about 27 columns, mostly int's and float's) with about
>150.000 rows with primary key and an additional unique key. The mysqldumped
>data was generated one insert per row (not dumped with --extended-inserts).
>1. cat test.sql | mysql innobase => 306 seconds
>2. cat test.sql | mysql innobase, but with autocommit=0 and all inserts
>between BEGIN/COMMIT => 124 seconds
If you use autocommit = 1, then Innobase has to flush the log to disk after
each individual insert, which makes it slow. It is better to use autocommit = 0,
if you have enough space in your tablespace for the rollback segment.
>3. same as 1. but with index creation after the inserts. => 264 seconds +
>additional 59 seconds for index recreation.
>4. same as 2. but with index creation after the inserts. => 75 seconds +
>additional 59 seconds for index recreation.
How do you create the index? With ALTER TABLE?
>I noticed that in 3. and 4. the machine load was constantly at 100% during
>the inserts.
>but during index recreation the load springs wildley between 0 and 100%, so
>the machine is not maxed out in terms of CPU utilization here (in other
Innobase has to write to the log which causes some disk i/o, reducing
CPU utilization. Also, when the database has written the log files
full, Innobase has to make a checkpoint, that is, flush written pages
from the buffer pool. You could try making your log files very big, say
150 MB in total, to get a better CPU utilization, and also the buffer pool
bigger, if it is not yet big.
>words, the machine is waiting for something to do :-). The same applies to
>2. when commit() was send.
>I tested also 1. and 3. with a myisam-table. Index recreation draws
>constantly 100% load from the machine here, therfore it's faster.
>results:
>5. same as 1. but with myisam type => 116 seconds
>6. same as 3. but with myisam type => 65 seconds + additional 33 seconds for
>index recreation.
>system is a celeron 466 / 66MHz FSB, 512 MB RAM, Linux 2.4.2, mysql-3.23.35
>If the numbers are not useful to anybody, think of this posting as spam and
>forget myself...
Totally the opposite of spam! I got useful information from this!
Best regards,
Heikki Tuuri
Innobase Oy
>--Andy
You could add to the campaign below MS Word documents :).
>/"\
>\ / ASCII RIBBON CAMPAIGN
> X AGAINST HTML EMAIL
>/ \ AND POSTINGS
---------------------------------------------------------------------
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
