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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
