List:General Discussion« Previous MessageNext Message »
From:Mirek Novak Date:December 5 2002 10:05am
Subject:Re: Slow performance using 3.23 on RH 8.0
View as plain text  
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ledet, Mike wrote:
| I'm running Mysql 3.23.52 on a Redhat 8.0 installation booting to Gnome.
| The machine is a dual AMD 1800, 1 gig of ram, one Ultra ATA IDE drive, and 2
| 18 gig scsi 10,000 RPM drives on a RAID controller running Raid 0.
|
| I've got everything except /db on the IDE drive, /db is the only thing on
| the raid array.
|
| I've got a couple of smallish tables and one larger table with about 7 gigs
| of data.  The larger table is a fixed row format table with each row being
| 462 bytes wide.  I have a primary auto increment int column and a unique
| index on a varchar 60.  Pack keys is off, delayed key writes on.
|

I'm afraid, you table row  isn't fixed-length row, as far as you'll be using varchar your
rows will not be considered to be a
fixed length rows. try to change it to char(60) (BINARY if you don't care for U/L case)
this may help. it will be more helpful if
you provide table structure for us...

I have table with approx 6M rows with fixed length, used for site statistics with approx.
++250k rows/day. There are extensive
writes (UPDATE DELAYED) and minor reads and I'm not having problems... As for my config
its 2x 1GHz PIII + 1GB RAM + 2 channel
onboard SCSI on 64-bit PCI WITHOUT RAID, 2x 16GB 10,000 RPM SCSI-HDD one disc is used for
db data only. As for RAID 0 it is not
very good idea to have databases stored in that manner (safety - if one disk fails, you
will lost everything ...).

| With this kind of hardware I was expecting pretty good performance, but I
| haven't seen it yet.  I finally decided something was wrong when I had to
| run an alter table on the 7 gig table, adding 3 columns, a varchar 12, a
| varchar 50, and a datetime columm.... and it took over 10 HOURS to complete.
|

As I wrote, to keep table fixed-length you must not use varchar. As for datetime I'm using
UNSIGNED int(11) and unix_timestamp to
store date and time.

| That seems way too slow to me...
|
| I've included relevant portions (the uncommented portions) from my.cnf, the
| OS installation was fairly vanilla, using defaults for just about
| everything.  The file system is ext3.

ext-3 is so-called journaling file system. it has small performance slow-down (no flame,
please ;-))

|
| Any suggestions or things I haven't included that you need?  Sorry if I'm
| doing something really stupid here... relatively new to Linux after a lot of
| years of windoze.

everyone had been newbie. Keep trying :)

TIPS:
	- check settings for your RAID controller -
		are you using native driver for SCSI/RAID controller ?
		# of TCQ (tagged commands queuing)
		elevator sorting - should be ON
	- do you really need ext3?
	- rethink structure of your table (R. M. Ryordan Designing relational database, 
ISBN:073560634X  - it is MS oriented, but
theoretical parts are very good and helpful for any platform)

|
| Thanks in advance
|
| Mike

- --
Mirek Novak
Anima Publishers, s.r.o.
Prilucka 360, Zlin 760 01
Czech Republic
tel/fax: 067/721 91 32
jabber:mirek@stripped
ICQ:119499448
GSM:+420603807837

AUTO.CZ
http://www.auto.cz

NEWS.AUTO.CZ
http://news.auto.cz

FORMULE1.CZ
http://www.formule1.cz
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAj3vJNoACgkQz+tW1WzgrpSjSACcD1R30nPOyUUgjmg//61aQaBX
ltsAmwTEHf+A3eZo5kNKnF6F+qJs8Keb
=53Lx
-----END PGP SIGNATURE-----

Thread
Slow performance using 3.23 on RH 8.0Mike Ledet4 Dec
  • Re: Slow performance using 3.23 on RH 8.0Nikolas Samios4 Dec
  • RE: Slow performance using 3.23 on RH 8.0Adam Nelson4 Dec
  • RE: Slow performance using 3.23 on RH 8.0Adam Nelson4 Dec
  • Re: Slow performance using 3.23 on RH 8.0Mirek Novak5 Dec
RE: Slow performance using 3.23 on RH 8.0Mike Ledet4 Dec
  • Re: Slow performance using 3.23 on RH 8.0Michael T. Babcock5 Dec
RE: Slow performance using 3.23 on RH 8.0Mike Ledet4 Dec
RE: Slow performance using 3.23 on RH 8.0Mike Ledet4 Dec
  • RE: Slow performance using 3.23 on RH 8.0Adam Nelson4 Dec
    • Re: Slow performance using 3.23 on RH 8.0Jeremy Zawodny5 Dec
RE: Slow performance using 3.23 on RH 8.0Mike Ledet4 Dec
  • RE: Slow performance using 3.23 on RH 8.0Adam Nelson4 Dec
RE: Slow performance using 3.23 on RH 8.0Mike Ledet4 Dec
RE: Slow performance using 3.23 on RH 8.0Jan Steinman5 Dec