Re: Slow performance using 3.23 on RH 8.0
On Wed, Dec 04, 2002 at 05:18:26PM -0500, Adam Nelson wrote: Don't let this list fool you. SQL Server is a very good product. It is far superior to Mysql in every way except cost and the fact that it doesn't run on unix. This smells a lot like flaim bait on a MySQL list... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 14 days, processed 440,492,778 queries (343/sec. avg) - 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
RE: Slow performance using 3.23 on RH 8.0
From: DELETED TO PROTECT THE GUILTY [Four original lines and SEVERAL HUNDRED quoted lines deleted] PLEASE folk, trim up your quotes a bit! Nothing spells newbie so much as mindlessly including everything anyone has ever written on a topic. And when two of you go at it with each other, it's essentially unreadable to those who get the list in digest format. The polite, thoughtful thing to do is to take a moment and pull out just those lines that you are addressing. Your few moments of thoughtfulness saves several thousand people several thousand moments of scrolling through the mess you make when you mindlessly include the entire thread in your reply. Thanks in advance for your thoughtfulness! SQL SQL SQL QSL SOL -- : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Buy My Step Van! http://www.Bytesmiths.com/van - 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
Re: Slow performance using 3.23 on RH 8.0
-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:[EMAIL PROTECTED] 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- - 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
Re: Slow performance using 3.23 on RH 8.0
On Wed, Dec 04, 2002 at 05:06:54PM -0500, Ledet, Mike wrote: I tried your TOP suggestion but didn't see anything out of the ordinary. Swap size was constant (6400 or so) and didn't increase or decrease under load. Familiarize yourself with vmstat if you aren't already and then run `vmstat 1` and run your queries. See how the blocked/running processes look, how much CPU time is in use by the system/user processes and how many blocks per second the disk is reading/writing. SQL -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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
Re: Slow performance using 3.23 on RH 8.0
Hi Mike, i'm a newbie in optimizing the inner structure of a db - so i stick to the os-part where i might have a vague clue ;-) did you take a look at the memory / swap-space mysqld used when doing the query? (maybe use TOP (add swap to the view by hitting f and then o), look at the swapspace used by the mysqld threads, usage of kswapd, loadaverage...) I once had a lot of performance problems with mysqld 3.23.4x on suse-linux 7 when suddenly the os startet to assign hd-swap-space to some of the mysqld threads, which slowed down the action dramatically. changing my.cnf didn't really change the situation - but for testing you could just do swapoff -a and compare the results. goodluck, niko - Original Message - From: Ledet, Mike [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 5:00 PM Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - 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 - 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
RE: Slow performance using 3.23 on RH 8.0
The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - 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
RE: Slow performance using 3.23 on RH 8.0
Oh, and what's up with the thread_concurrency being 6? That doesn't make any sense unless you have a tri-processor setup. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - 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
RE: Slow performance using 3.23 on RH 8.0
I tried your TOP suggestion but didn't see anything out of the ordinary. Swap size was constant (6400 or so) and didn't increase or decrease under load. I'll try the swap-off idea when I get a chance. -Original Message- From: Nikolas Samios [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 3:37 PM To: Ledet, Mike; [EMAIL PROTECTED] Subject: Re: Slow performance using 3.23 on RH 8.0 Hi Mike, i'm a newbie in optimizing the inner structure of a db - so i stick to the os-part where i might have a vague clue ;-) did you take a look at the memory / swap-space mysqld used when doing the query? (maybe use TOP (add swap to the view by hitting f and then o), look at the swapspace used by the mysqld threads, usage of kswapd, loadaverage...) I once had a lot of performance problems with mysqld 3.23.4x on suse-linux 7 when suddenly the os startet to assign hd-swap-space to some of the mysqld threads, which slowed down the action dramatically. changing my.cnf didn't really change the situation - but for testing you could just do swapoff -a and compare the results. goodluck, niko - Original Message - From: Ledet, Mike [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 5:00 PM Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - 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 - 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
RE: Slow performance using 3.23 on RH 8.0
Good question... I have no idea. I'll change it to 4. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:36 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Oh, and what's up with the thread_concurrency being 6? That doesn't make any sense unless you have a tri-processor setup. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - 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
RE: Slow performance using 3.23 on RH 8.0
Actually it is hardware Raid 0, not software. I knew about the safety issue but I had been told that from a performance stand-point that Raid 0 was the fastest. I've watched the disk activity on the IDE drive and there is next to none, but I guess it's possible something is going on there. Gnome is because linux is very new to me... I found the GUI to be comfortable coming from a Windoze world. Since I first installed I've had a crash course in doing it from the command line (I'm managing a web and mail server as well) so at some point I could probably undo it. I'll try the variables when I get a chance. Just as an aside I had a friend running SQL Server on a 2000 box that is a pretty similar configuration... he added the same 3 columns to a table with 5 keys and 3 times as many columns in less than 2 minutes. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:35 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - 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
RE: Slow performance using 3.23 on RH 8.0
Don't let this list fool you. SQL Server is a very good product. It is far superior to Mysql in every way except cost and the fact that it doesn't run on unix. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:12 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Actually it is hardware Raid 0, not software. I knew about the safety issue but I had been told that from a performance stand-point that Raid 0 was the fastest. I've watched the disk activity on the IDE drive and there is next to none, but I guess it's possible something is going on there. Gnome is because linux is very new to me... I found the GUI to be comfortable coming from a Windoze world. Since I first installed I've had a crash course in doing it from the command line (I'm managing a web and mail server as well) so at some point I could probably undo it. I'll try the variables when I get a chance. Just as an aside I had a friend running SQL Server on a 2000 box that is a pretty similar configuration... he added the same 3 columns to a table with 5 keys and 3 times as many columns in less than 2 minutes. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:35 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com
RE: Slow performance using 3.23 on RH 8.0
Well, I've got an MSDN subscription so I have access to a legal copy.. the non-unix thing is a downside but there are number of scp command line utilities for Windoze that I can use to move the data back and forth as needed I'm seriously thinking of biting the bullet and going that way. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:18 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Don't let this list fool you. SQL Server is a very good product. It is far superior to Mysql in every way except cost and the fact that it doesn't run on unix. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:12 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Actually it is hardware Raid 0, not software. I knew about the safety issue but I had been told that from a performance stand-point that Raid 0 was the fastest. I've watched the disk activity on the IDE drive and there is next to none, but I guess it's possible something is going on there. Gnome is because linux is very new to me... I found the GUI to be comfortable coming from a Windoze world. Since I first installed I've had a crash course in doing it from the command line (I'm managing a web and mail server as well) so at some point I could probably undo it. I'll try the variables when I get a chance. Just as an aside I had a friend running SQL Server on a 2000 box that is a pretty similar configuration... he added the same 3 columns to a table with 5 keys and 3 times as many columns in less than 2 minutes. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:35 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M
RE: Slow performance using 3.23 on RH 8.0
Go for it. Whatever works works. Caveat, MSDN is not an actual license for production use, only development. If you ever get audited and are using it on a production machine, you will have to pay the license. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:23 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Well, I've got an MSDN subscription so I have access to a legal copy.. the non-unix thing is a downside but there are number of scp command line utilities for Windoze that I can use to move the data back and forth as needed I'm seriously thinking of biting the bullet and going that way. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:18 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Don't let this list fool you. SQL Server is a very good product. It is far superior to Mysql in every way except cost and the fact that it doesn't run on unix. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:12 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Actually it is hardware Raid 0, not software. I knew about the safety issue but I had been told that from a performance stand-point that Raid 0 was the fastest. I've watched the disk activity on the IDE drive and there is next to none, but I guess it's possible something is going on there. Gnome is because linux is very new to me... I found the GUI to be comfortable coming from a Windoze world. Since I first installed I've had a crash course in doing it from the command line (I'm managing a web and mail server as well) so at some point I could probably undo it. I'll try the variables when I get a chance. Just as an aside I had a friend running SQL Server on a 2000 box that is a pretty similar configuration... he added the same 3 columns to a table with 5 keys and 3 times as many columns in less than 2 minutes. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:35 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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. 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. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M
RE: Slow performance using 3.23 on RH 8.0
Yeah, I know. At this point we are still in 'proof of concept mode'. At the point we decide to go live I'll buy a copy. I really don't need more than 2-3 concurrent connections so it shouldn't set me back too much. Bottom line is I'm probably 'spending' more on wasted people time than I would on licenses. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:26 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Go for it. Whatever works works. Caveat, MSDN is not an actual license for production use, only development. If you ever get audited and are using it on a production machine, you will have to pay the license. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:23 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Well, I've got an MSDN subscription so I have access to a legal copy.. the non-unix thing is a downside but there are number of scp command line utilities for Windoze that I can use to move the data back and forth as needed I'm seriously thinking of biting the bullet and going that way. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:18 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Don't let this list fool you. SQL Server is a very good product. It is far superior to Mysql in every way except cost and the fact that it doesn't run on unix. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:12 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Actually it is hardware Raid 0, not software. I knew about the safety issue but I had been told that from a performance stand-point that Raid 0 was the fastest. I've watched the disk activity on the IDE drive and there is next to none, but I guess it's possible something is going on there. Gnome is because linux is very new to me... I found the GUI to be comfortable coming from a Windoze world. Since I first installed I've had a crash course in doing it from the command line (I'm managing a web and mail server as well) so at some point I could probably undo it. I'll try the variables when I get a chance. Just as an aside I had a friend running SQL Server on a 2000 box that is a pretty similar configuration... he added the same 3 columns to a table with 5 keys and 3 times as many columns in less than 2 minutes. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:35 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 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. 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. 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
