Slow performance after upgrade from 4.0.16 to 5.0.16
am 24.11.2005 22:36:41 von PgmHelmi
Hello!
I wanted to upgrade from 4.0.16 to 5.0.16, but already notized a slow
performance on restoring data.
When dumping a table with mysqldump (4.0.16) and then restoring it with
mysql << 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.
Why is it slower, in the dump file are only simple INSERTS, and is there
some option to make
it faster in 5.0 as it was in 4.0?
Thank you for your answer in advance!
Helmuth
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Re: Slow performance after upgrade from 4.0.16 to 5.0.16
am 24.11.2005 23:07:53 von PgmHelmi
Hello!
First: Thank you for your answer!
OK not the overall performance is slow for me up to now because I have
not tested it.
But for me the first thing to do when upgrading from 4.0 to 5.0 was
dumping in 4.0 and
restoring in 5.0. and I noticed a slower performance from 58 - 80 % for
these tables.
As restoring contains almost only of INSERTS my conclusion was that the
INSERTS become much
slower and I was asking me why and if I can get performance of 4.0
again. I have not found
anything in the documentation or on the web page that INSERTS are now
slower.
I know that applications do not consist only of INSERTS, but the ones
which have much of them
will get slower except seek and other operations are now at least as
fast or even faster
than INSERTS are slower.
If INSERTS are now safer in 5.0, were they not in 4.0 and where is this
written? I understand if something in a new version is corrected or
improved but the performance loss in this case is so much and I am
wondering why.
Sure applications can be improved by fixing bottlenecks, but a INSERT is
a INSERT.
So this answer is not satisfying for me, but nevertheless thanks for it
again.
Helmuth
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Re: Slow performance after upgrade from 4.0.16 to 5.0.16
am 25.11.2005 14:19:49 von Daniel da Veiga
This is not a good way to test the database, there are many variables
involved and you can't tell from the time a dump takes to load that a
version is slower than another, it may simply deal with data
differently. If you use your app with the new database, you may find
out that it performs the same or even faster after the upgrade,
because an application is not a sequence of inserts, instead doying
several operations. Another issue is that inserts were made safer in
MySQL 5 (I've read it somewhere), so, it adds to stability and
reliability, while may affect performance.
If you want to get a real benchmark, locate and fix bottlenecks, use this:
http://dev.mysql.com/doc/refman/5.0/en/optimize-overview.htm l
Also check this:
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
Good luck,
On 11/24/05, PgmHelmi wrote:
> Hello!
>
> I wanted to upgrade from 4.0.16 to 5.0.16, but already notized a slow
> performance on restoring data.
>
> When dumping a table with mysqldump (4.0.16) and then restoring it with
> mysql << the duration increases.
> For test reasons I have tried it with 2 tables and different mysql
> versions:
>
> test_01
> ==================== =====
==================== =3D
>
> CREATE TABLE test_01 (
> ID int(6) unsigned NOT NULL auto_increment,
> Name varchar(60) NOT NULL default '',
> PRIMARY KEY (ID)
> ) TYPE=3DMyISAM;
>
> 1.284.256 rows, dump file size 69 MB
>
> test_02
> ==================== =====
==================== =3D
>
> 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=3DMyISAM;
>
> 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=3Dlatin1
> set-variable =3D key_buffer=3D16M
> set-variable =3D max_allowed_packet=3D1M
> set-variable =3D thread_stack=3D128K
> set-variable =3D flush_time=3D1800
>
>
> 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 improvemen=
t.
> I have noticed that it is not a matter of index, but a matter of data
> volume which is restored.
>
> Why is it slower, in the dump file are only simple INSERTS, and is there
> some option to make
> it faster in 5.0 as it was in 4.0?
>
> Thank you for your answer in advance!
>
> Helmuth
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Ddanieldaveiga@gma=
il.com
>
>
--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org