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
