Re: Should I switch to SCSI HD for mysql?
"mike thomas" <[EMAIL PROTECTED]> writes: > I'll have to think about this one. My application > just consists > of a huge table of domain names and users will be > able to search for > full strings and partial strings. So I'm not sure > how I could create > an intermediate results set from this table that > would speed up queries? Split on "."? If they're looking for mycompany's domains then splitting out the mycompany part would be good... -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire - - 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: Should I switch to SCSI HD for mysql?
Thanks for the info on tuning an IDE. I'll go ahead and do that. Regarding " Do you really need to do sub string matching all the time? Can you pre-build an intermediate results set that can be optimally queried?" I'll have to think about this one. My application just consists of a huge table of domain names and users will be able to search for full strings and partial strings. So I'm not sure how I could create an intermediate results set from this table that would speed up queries? Thanks everyone for the feedback. Mike -Original Message- From: Dave Hodgkinson [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 1:32 AM To: mike thomas Cc: Greg Cope; Mysql@Lists. Mysql. Com Subject: Re: Should I switch to SCSI HD for mysql? "mike thomas" <[EMAIL PROTECTED]> writes: > Greg, > > Well, I'm doing like "%stuff%" selects on a single column table > with 20 million records, so any speed gain would be great. Would > I be correct in saying that a select of this type will always do > a table scan? Pretty much. > I think I'll go with SCSI and increase the RAM so I can use a > heap table. A cheap first step might be to go with a couple of decent 7200 rpm, ATA100 (or whatever the hell they're calling themsevles this week) disks and RAID0 them. Striping Good. Also, make sure your IDE is spiffed up to the max. Do an: hdparm /dev/hda (or whatever). Make sure it's using DMA, unmasked IRQ, 32 bit i/o and, a multcount of 16. Benchmark your drive before and after, with: hdparm -t /dev/hda You should see >10MB per second after tuning up. However, I can't help thinking for your application There Must Be A Better Way. Do you really need to do substring matching all the time? Can you pre-build an intermediate results set that can be optimally queried? Dave. -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire - - 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: Should I switch to SCSI HD for mysql?
Dave Hodgkinson wrote: > > "mike thomas" <[EMAIL PROTECTED]> writes: > > > Greg, > > > > Well, I'm doing like "%stuff%" selects on a single column table > > with 20 million records, so any speed gain would be greatWould > > I be correct in saying that a select of this type will always do > > a table scan? > > Pretty much. > > > I think I'll go with SCSI and increase the RAM so I can use a > > heap table. > > A cheap first step might be to go with a couple of decent 7200 rpm, > ATA100 (or whatever the hell they're calling themsevles this week) > disks and RAID0 them. > > Striping Good. This still does not help the seek time of SCSI vs EIDE (ATA100 only helps the bus speed AFAIR). Some calc's I did a while back implied that SCSI seek time is 40% better (or less) - this was comparing IBM SCSI 10k drives to IBM EIDE. Agreed that striping is good tho. > > Also, make sure your IDE is spiffed up to the max. Do an: > > hdparm /dev/hda (or whatever). Redhat is very bad for not tweeking the UDMA - but they do not as they are afraid of damaging old drives that do not support it. > > Make sure it's using DMA, unmasked IRQ, 32 bit i/o and, a multcount of > 16. Benchmark your drive before and after, with: > > hdparm -t /dev/hda > > You should see >10MB per second after tuning up. Once you've tweaked it put the hdparm setting at the end of your /etc/rc.d/rc.local (if on redhat - your's may be in a different location) so that it gets loaded at each boot. You may also wish to mount your disks with no atime (each time your disks are access the access time of the file is update - if you do not need this then switch it off) eg an example line from my /etc/fstab: /dev/hda2 / ext2noatime,defaults 1 1 > > However, I can't help thinking for your application There Must Be A > Better Way. Do you really need to do substring matching all the time? > Can you pre-build an intermediate results set that can be optimally > queried? (Dave sometimes you worry me ;-) Dave's right - substring matching is bad performance wise. Greg > > Dave. > > -- > Dave Hodgkinson, http://www.hodgkinson.org > Editor-in-chief, The Highway Star http://www.deep-purple.com > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > - - 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: Should I switch to SCSI HD for mysql?
Hi, >> I think I'll go with SCSI and increase the RAM so I can use a >> heap table. > > A cheap first step might be to go with a couple of decent 7200 rpm, > ATA100 (or whatever the hell they're calling themsevles this week) > disks and RAID0 them. > > Striping Good. > > Also, make sure your IDE is spiffed up to the max. Do an: > > hdparm /dev/hda (or whatever). > > Make sure it's using DMA, unmasked IRQ, 32 bit i/o and, a multcount of > 16. Benchmark your drive before and after, with: > > hdparm -t /dev/hda > > You should see >10MB per second after tuning up. Yes, the performance of such a system is really not all that bad. I have a MySQL server running in just such a fashion that handles a fair load. It has two 20Gb ATA-66 drives (although only 5400 RPM) in a software RAID 0 array with tune-up options set using hdparm. BTW, there's a good guide on this at the O'Reilly site: http://www.oreillynet.com/pub/a/linux/2000/06/29/hdparm.html I recommend reading it thoroughly, as well as the hdparm man page. Personally, these are the settings I'm using: multcount= 16 (on) I/O support = 3 (32-bit w/sync) unmaskirq= 1 (on) using_dma= 1 (on) keepsettings = 0 (off) nowerr = 0 (off) readonly = 0 (off) readahead= 8 (on) geometry = 2491/255/63, sectors = 40031712, start = 0 Also, if you don't like the sound of software-controlled RAID (i.e. by the Linux kernel), then you could go for one of the plethora of recent motherboards sporting hardware IDE RAID controllers, or one of the Promise or Adaptec PCI adapters if you don't want to change motherboards. Regards, Basil Hussain ([EMAIL PROTECTED]) - 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: Should I switch to SCSI HD for mysql?
"mike thomas" <[EMAIL PROTECTED]> writes: > Greg, > > Well, I'm doing like "%stuff%" selects on a single column table > with 20 million records, so any speed gain would be great. Would > I be correct in saying that a select of this type will always do > a table scan? Pretty much. > I think I'll go with SCSI and increase the RAM so I can use a > heap table. A cheap first step might be to go with a couple of decent 7200 rpm, ATA100 (or whatever the hell they're calling themsevles this week) disks and RAID0 them. Striping Good. Also, make sure your IDE is spiffed up to the max. Do an: hdparm /dev/hda (or whatever). Make sure it's using DMA, unmasked IRQ, 32 bit i/o and, a multcount of 16. Benchmark your drive before and after, with: hdparm -t /dev/hda You should see >10MB per second after tuning up. However, I can't help thinking for your application There Must Be A Better Way. Do you really need to do substring matching all the time? Can you pre-build an intermediate results set that can be optimally queried? Dave. -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire - - 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: Should I switch to SCSI HD for mysql?
mike thomas wrote: > > Hi all > > I am running mysql on linux (redhat 6.2) using a regular 18Gb > EIDI hard drive. Does anyone know how much (ball park figure) a > SCSI hard drive would speed up selects and inserts? This is a how long is a piece of string question . EIDE disks have nearly the same data transfer rates as SCSI drives. but SCSI drives have nearly twice the seek-time performance - or their average seek time is twice as good. So it depends on how many seeks a query takes to complete that will determine any performance increase. Each query will be atleast two IIRC from the manual - which has a section on Disk performance. If you are serious about performance go SCSI. Greg > > Thanks everyone! > > Michael Thomas > abcXyz.com > [EMAIL PROTECTED] > > - > 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: Should I switch to SCSI HD for mysql?
One thing to keep in mind is IDE tends to eat up processor power where as off board scsi doesn't. Greg Cope wrote: > mike thomas wrote: > > > > Hi all > > > > I am running mysql on linux (redhat 6.2) using a regular 18Gb > > EIDI hard drive. Does anyone know how much (ball park figure) a > > SCSI hard drive would speed up selects and inserts? > > This is a how long is a piece of string question . > > EIDE disks have nearly the same data transfer rates as SCSI drives. > > but > > SCSI drives have nearly twice the seek-time performance - or their > average seek time is twice as good. > > So it depends on how many seeks a query takes to complete that will > determine any performance increase. Each query will be atleast two IIRC > from the manual - which has a section on Disk performance. > > If you are serious about performance go SCSI. > > Greg > > > > > Thanks everyone! > > > > Michael Thomas > > abcXyz.com > > [EMAIL PROTECTED] > > > > - > > 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 - 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: Should I switch to SCSI HD for mysql?
Greg, Well, I'm doing like "%stuff%" selects on a single column table with 20 million records, so any speed gain would be great. Would I be correct in saying that a select of this type will always do a table scan? I think I'll go with SCSI and increase the RAM so I can use a heap table. Thanks Michael Thomas > -Original Message- > From: greg [mailto:greg]On Behalf Of Greg Cope > Sent: Wednesday, February 07, 2001 4:09 PM > To: mike thomas > Cc: Mysql@Lists. Mysql. Com; [EMAIL PROTECTED] > Subject: Re: Should I switch to SCSI HD for mysql? > > > mike thomas wrote: > > > > Hi all > > > > I am running mysql on linux (redhat 6.2) using a regular 18Gb > > EIDI hard drive. Does anyone know how much (ball > park figure) a > > SCSI hard drive would speed up selects and inserts? > > This is a how long is a piece of string question . > > EIDE disks have nearly the same data transfer rates as > SCSI drives. > > but > > SCSI drives have nearly twice the seek-time > performance - or their > average seek time is twice as good. > > So it depends on how many seeks a query takes to > complete that will > determine any performance increase. Each query will > be atleast two IIRC > from the manual - which has a section on Disk performance. > > If you are serious about performance go SCSI. > > Greg > > > > > Thanks everyone! > > > > Michael Thomas > > abcXyz.com > > [EMAIL PROTECTED] > > > > > --- > -- > > 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 > ts.mysql.com> > > 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: Should I switch to SCSI HD for mysql?
Jeremy, My queries are disk bound (I think) from watching the Redhat system monitor when running a query. Mike > -Original Message- > From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 07, 2001 4:07 PM > To: mike thomas > Cc: Mysql@Lists. Mysql. Com; [EMAIL PROTECTED] > Subject: Re: Should I switch to SCSI HD for mysql? > > > On Wed, Feb 07, 2001 at 06:24:58PM -0800, mike thomas wrote: > > Hi all > > > > I am running mysql on linux (redhat 6.2) using a > regular 18Gb EIDI > > hard drive. Does anyone know how much (ball park > figure) a SCSI hard > > drive would speed up selects and inserts? > > That depends. Are your queryes CPU or disk bound now? > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 328-7878Fax: (408) 530-5454 > Cell: (408) 439-9951 > - 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: Should I switch to SCSI HD for mysql?
On Wed, Feb 07, 2001 at 06:24:58PM -0800, mike thomas wrote: > Hi all > > I am running mysql on linux (redhat 6.2) using a regular 18Gb EIDI > hard drive. Does anyone know how much (ball park figure) a SCSI hard > drive would speed up selects and inserts? That depends. Are your queryes CPU or disk bound now? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
