List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 2 2000 5:27pm
Subject:How MYSQL drives Hard Drive
View as plain text  
Hi!

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

>> 
>> > Does anyone have any ideas which raid chunk will be the best in general
Peter> for
>> > MYSQL on hardware raid 0 system ?  Does anyone had any experience with
Peter> it ?
>> 
>> No, It's not MySQL dependant I think. MySQL optimizes reads to be as big
>> as reasonable. Usually MySQL reads in 1megabyte chunks, so it's no big
>> difference, is RAID reads by 4 or 16kbytes.

To be more precise:

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

The indexes is usually done in 1K blocks which are read on demand if
they are not in the key cache (no prefetch of key blocks).

Peter> Well.  I just was going to ask a question which you almost answered... The
Peter> thing is your answer does not explain my result.
Peter> I only MYSQLD runnning on this machine. Mostly I do inserts and rang-index
Peter> scans with join. Here is MY SCSI drive statistics:

Peter> (scsi0:0:0:0)
Peter>   Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31
Peter>   Transinfo settings: current(10/31/1/0), goal(10/31/1/0), user(10/127/1/0)
Peter>   Total transfers 86472198 (56683615 reads and 29788583 writes)
Peter>              < 2K      2K+     4K+     8K+    16K+    32K+    64K+   128K+
Peter>    Reads:       5       0 45164838 3375906 3622661 1739799 2780406       0
Peter>   Writes:       0       0 25822915 2908288  302002  103326  652052       0

Peter> As You See 80% of transfers which is done are 4K - rather small block (size
Peter> of EXT2 block used). This may explain slow mysql operation found then
Peter> optimizing table during simultaneosely other operation and which is more
Peter> important table scan on index.... Additionaly I am not shure how mysql does
Peter> flushing of keyblocks - does it use random write or uses self queue
Peter> optimization or scatter-gather writes ?

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..

The above is of course done per file.

Peter> ?ould anyone explain this or may be has any solution ideas.

Peter> Other important problem is DATA fragmentation. Well this should be the
Peter> mostly FS problem but I do not think it is solved much in EXT2 for example.
Peter> I have about 20.000 of which most are continualy grothin simultaneosely. In
Peter> this case standart space allocation mechanisms will produce a  hard dara
Peter> fragmentation if I'm not mistaken.

Peter> The nice thing would be allowing to specify with create table 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
information so it wouldn't be that hard to fix this..)

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