List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 3 2000 11:07am
Subject:Re: How MYSQL drives Hard Drive
View as plain text  
Hi!

>>>>> "Peter" == Peter Zaitsev <pz@stripped> writes:

>> When scanning tables, MYSQL reads everything in 'record_buffer'
>> hunks. When using keys MySQL just reads the needed row from the data.


Peter> And here is the problem located: If you have lange table (several millions
Peter> of records) and need to do s range-index scan producing let's say 10000 of
Peter> record used in join or that ever. In my task the data is "clusterized" on
Peter> index as the index is timestamp and the records are only inserted to the end
Peter> of the table.
Peter> So MYSQL works like reading a key block (or finding it in the cashe) then
Peter> reading comparetively small peice of data and then searching for the key
Peter> blocks again. So I have a huge amount of reads of data (and less keyblocks)
Peter> instead of several big reads of data. This of couse slow everything much
Peter> then this is not the only task working and so the disk head has to fly a
Peter> lot. That's why I vas asking about configuring read-ahead on linux - I can
Peter> provide enough cache memory to have for example 128K read-aheads.
Peter> Now I'm thinking of buying hardware raid  which has it's own read-ahead so
Peter> should help a bit this problem additionaly to spreading requests to the
Peter> disks.


Peter> The other question I didn't found the answer yet - does mysql flushes (by
Peter> calling fsync() or that ever) data to the shurely disk or just does fflush()
Peter> or what ever to flush data into OS cache ?

I did mail an answer to this a while ago.  It just puts things into
the OS cache (if you are not using the --flush startup option)
I have even updated the manual about this :)

>> Before MySQL 3.23.12 MySQL , when flushing key blocks, did collect
>> 2000 blocks at a time, sorted these and wrote these on disk.
>> In MySQL 3.23.12 we changed that to sort and write all disk blocks and
>> only use the 2000 blocks as a fallback if there isn't enough memory to
>> to do the sort on everything..

Peter> Thank you this is really nice change.

Peter> The nice thing would be allowing to specify with create table
Peter> something like
Peter> "groth chunk" which may decrease fragmentation.

>> The problem with the above is mainly that this would confuse myisamchk
>> a lot.  We had this in an earlier ISAM release but removed this a
>> couple of years ago because we thought we wouldn't need that for what
>> we where doing.  It may be time to rethink that and do it again.
>> (The MyISAM table format has actually reserved space for relocation

Peter> I think it would be the nice feature allowing to reduce fragmentation in
Peter> several cases much.

I agree.

Peter> By now I thing I'll rewrite an application to more use huge multeple inserts
Peter> so file will grow in a few bigger peices. (Now I use not so huge but
Peter> multiple inserts).

Regards,
Monty
Thread
Hardware RAIDPeter Zaitsev1 Mar
  • Re: Hardware RAIDTonu Samuel1 Mar
  • How MYSQL drives Hard DrivePeter Zaitsev2 Mar
    • How MYSQL drives Hard DriveMichael Widenius3 Mar
      • Re: How MYSQL drives Hard DriveScott Hess3 Mar
        • Re: How MYSQL drives Hard Drivesinisa4 Mar
          • Re: How MYSQL drives Hard DriveScott Hess6 Mar
            • Re: How MYSQL drives Hard Drivesinisa6 Mar
        • Re: How MYSQL drives Hard DriveMichael Widenius6 Mar
          • Re: How MYSQL drives Hard DriveScott Hess6 Mar
            • Re: How MYSQL drives Hard DriveMichael Widenius8 Mar
              • Re: How MYSQL drives Hard DriveScott Hess9 Mar
                • Re: How MYSQL drives Hard DriveMichael Widenius9 Mar
                  • Re: How MYSQL drives Hard DriveScott Hess9 Mar
                    • Re: How MYSQL drives Hard DriveMichael Widenius9 Mar
          • Re: How MYSQL drives Hard DrivePeter Zaitsev9 Mar
            • Re: How MYSQL drives Hard DriveMichael Widenius9 Mar
            • Re: How MYSQL drives Hard DriveScott Hess9 Mar
  • Re: How MYSQL drives Hard DrivePeter Zaitsev3 Mar
    • Re: How MYSQL drives Hard DriveMichael Widenius3 Mar
Re: How MYSQL drives Hard DriveEd Carp3 Mar