Hello.
> As you have not seen my first questions you did not know that I >restored a file which was created with mysqldump and there are single >INSERTS in it, but I do not want to have to change such dump files to >make it faster. You do not have to change your existing dump files, you should just use mysqldump from the fresh distribution, which produces bulk-inserts automatically. BTW, using single inserts is a bit deprecated with latest releases, bulk-inserts is recommended, so if you're not going to follow official recommendations, most probably you won't get optimal results. PgmHelmi wrote: > > Hallo! > > Thank all of you for your answers! > > As my answers are not posted to my questions, I copy the whole text > here. I am not subscribed to the mailing list and therefore not reply > but send my answers with same subject. This worked the last time I > answered to a mail. Perhaps someone can tell my why it this time did not > work? > > Now to my answers: > > To Gleb Paharenko: > > As you have not seen my first questions you did not know that I restored > a file which was created with mysqldump and there are single INSERTS in > it, but I do not want to have to change such dump files to make it faster. > > To SGreen: > > Sorry! I did not know that only volunteers are on this list. I thought > that the developers are reading this postings too and answers some > questions. If not many of you have changed to 5.0 than of course you can > not notice my experience. For the developers I think such a simple > performance test like dumping and restoring again a table should be > standard and therefore such a performance loss should be noticed. But I > can imagine that it may be that if you have many gigabyte of RAMs you > have no performance loss. Also if a benchmark test mixes seeking and > INSERTS, the performance loss of INSERTS may be hided if the seeking got > faster. So the only thing left for me to do is watch the mailing lists > checking if some other has the same problem and gets a corresponding > answer. > > Thanks again for your replies! > > Helmuth > > ------------------------------------------------------------------------------- > > > This was my first message: > > Hello! > > I wanted to upgrade from 4.0.16 to 5.0.16, but already notized a slow > performance on restoring data with INSERTS. > > When dumping a table with mysqldump (4.0.16) and then restoring it with > mysql <<, which consists only of > INSERTS, the duration increases. > For test reasons I have tried it with 2 tables and different mysql > versions: > > test_01 > ============================================= > > CREATE TABLE test_01 ( > ID int(6) unsigned NOT NULL auto_increment, > Name varchar(60) NOT NULL default '', > PRIMARY KEY (ID) > ) TYPE=MyISAM; > > 1.284.256 rows, dump file size 69 MB > > test_02 > ============================================= > > CREATE TABLE test_02 ( > ID int(6) unsigned NOT NULL auto_increment, > Freq int(5) unsigned NOT NULL default '0', > UK0 tinyint(2) NOT NULL default '0', > UK1 tinyint(2) NOT NULL default '0', > UK2 tinyint(2) NOT NULL default '0', > UK3 tinyint(2) NOT NULL default '0', > UK4 tinyint(2) NOT NULL default '0', > UK5 tinyint(2) NOT NULL default '0', > UG1 tinyint(2) NOT NULL default '0', > UG2 tinyint(2) NOT NULL default '0', > UG3 tinyint(2) NOT NULL default '0', > UG4 tinyint(2) NOT NULL default '0', > UG5 tinyint(2) NOT NULL default '0', > UG6 tinyint(2) NOT NULL default '0', > HK0 tinyint(2) NOT NULL default '0', > HK1 tinyint(2) NOT NULL default '0', > HK2 tinyint(2) NOT NULL default '0', > HK3 tinyint(2) NOT NULL default '0', > HK4 tinyint(2) NOT NULL default '0', > HK5 tinyint(2) NOT NULL default '0', > HG1 tinyint(2) NOT NULL default '0', > HG2 tinyint(2) NOT NULL default '0', > HG3 tinyint(2) NOT NULL default '0', > HG4 tinyint(2) NOT NULL default '0', > HG5 tinyint(2) NOT NULL default '0', > HG6 tinyint(2) NOT NULL default '0', > WK0 tinyint(2) NOT NULL default '0', > WK1 tinyint(2) NOT NULL default '0', > WK2 tinyint(2) NOT NULL default '0', > WK3 tinyint(2) NOT NULL default '0', > WK4 tinyint(2) NOT NULL default '0', > WK5 tinyint(2) NOT NULL default '0', > WG1 tinyint(2) NOT NULL default '0', > WG2 tinyint(2) NOT NULL default '0', > WG3 tinyint(2) NOT NULL default '0', > WG4 tinyint(2) NOT NULL default '0', > WG5 tinyint(2) NOT NULL default '0', > WG6 tinyint(2) NOT NULL default '0', > PRIMARY KEY (ID) > ) TYPE=MyISAM; > > 1.284.256 rows, dump file size 127 MB > > > As you can see no other indexes than PRIMARY KEY are used. > > My configuration: > AMD-1200 > 768 MB RAM > Windows 2000 > Connection via named pipe > table type MyISAM > used server is mysqld-nt from no-install zip version > Start server with: mysqld-nt --standalone > > Important options from my.ini for 4.0 and 4.1 which I used: > ----------------------------------------------------------- > > [mysqld] > enable-named-pipe > skip-locking > default-character-set=latin1 > set-variable = key_buffer=16M > set-variable = max_allowed_packet=1M > set-variable = thread_stack=128K > set-variable = flush_time=1800 > > > Important options from my.ini for 5.0 which I used: > --------------------------------------------------- > > I took my-large.ini as base. > > enable-named-pipe > skip-locking > skip-safemalloc > skip-networking > skip-external-locking > skip-innodb > > I switched off all logs. > > Comparing: > ---------------------------- > > Version - test_01 - test_02 > > 4.0.16 - 24 sec - 1:55 min > 4.0.25 - 25 sec - 1:53 min > > 4.1.03b - 34 sec - 2:19 min > 4.1.15 - 31 sec - 2:14 min > > 5.0.03b - 34 sec - 3:28 min > 5.0.15 - 37 sec - 3:26 min > 5.0.16 - 38 sec - 3:28 min > > I tried out some other adviced performance tunings, but got no improvement. > I have noticed that it is not a matter of index, but a matter of data > volume which is restored. > > So my questions are why became INSERTS slower and is there some option > to make them faster in 5.0 as they were in 4.0? > > Thank you for your answer in advance! > > Helmuth > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
