List:General Discussion« Previous MessageNext Message »
From:Justin Swanhart Date:July 14 2004 8:18pm
Subject:Re: Mysql growing pains, 4 days to create index on one table!
View as plain text  
Indexes can generate vast amounts of random i/o. 
Because of rotational latency, random i/o can really
slow you down, especially if you are using IDE or SATA
disks because they can't do tagged queueing like SCSI
disks can.

If you have the budget for it, I would consider
getting some solid state disks.  Because they have
extremely low latency you will be able to get full i/o
bandwidth on your reads.  If you can't afford those,
consider adding more disks to your RAID array so that
you can spread the reads over more spindles, which
will help performance.


--- matt ryan <matt_lists@stripped> wrote:
> Udikarni@stripped wrote:
> 
> >You may want more indexes but you might be getting
> killed because you already have too many.
> >
> >To test - try loading into a table without indexes
> and see if it makes a difference.
> >
> >At the very least - check to see if the primary
> index which starts with 'dic' can make your special
> 'dic' index superfluous.
> >
> >If write speed is a bottleneck you might consider
> Raid-1 instead of Raid-5.
> >
> >Reading lots of rows via index is a killer.
> Depending on your hardware it may be cheaper to
> table scan 50 rows than to read 1 via index.
> However, this requires partitioning of the data
> based on some column which appears in every query
> and acts as an initial filter. If you are lucky
> enough to be in that situation - consider a MERGE
> table.
> >
> >
> >  
> >
> 
> These tables are merged, the total table size is
> huge, on this 
> particular table, it's , 45,449,534 rows, however,
> all the merge tables 
> combined are 258,840,305 records
> 
> perhaps I should reorder the pimary key, putting the
> longest most unique 
> record up front, and the least unique at the end,
> would that speed up 
> the key check?   I can tell that almost everything
> is read IO, very 
> little write IO
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/mysql?unsub=1
> 
> 

Thread
Mysql growing pains, 4 days to create index on one table!matt ryan30 Jun
  • Re: Mysql growing pains, 4 days to create index on one table!Egor Egorov1 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!matt ryan14 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Tim Brody15 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
      • RE: Mysql growing pains, 4 days to create index on one table!Donny Simonton15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Tim Brody15 Jul
RE: Mysql growing pains, 4 days to create index on one table!Lopez David E-r9374c30 Jun
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan14 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udikarni1 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan14 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart14 Jul
      • Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
        • Re[2]: Mysql growing pains, 4 days to create index on one table!Pete McNeil15 Jul
          • Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
            • Re[2]: Mysql growing pains, 4 days to create index on one table!adburne15 Jul
              • Re[3]: Mysql growing pains, 4 days to create index on one table!Alejandro D. Burne15 Jul
Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart15 Jul
  • RE: Mysql growing pains, 4 days to create index on one table!Lachlan Mulcahy16 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udi.S.Karni15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart15 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udi.S.Karni15 Jul
Re: Mysql growing pains, 4 days to create index on one table!matt ryan16 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udi.S.Karni16 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
Re: Mysql growing pains, 4 days to create index on one table!matt ryan16 Jul
RE: Mysql growing pains, 4 days to create index on one table!Lopez David E-r9374c16 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!gerald_clark21 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart21 Jul
      • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
        • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart21 Jul
          • Re: Mysql growing pains, 4 days to create index on one table!matt ryan22 Jul