GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MyISAM slow creating new index, lots of (small) pwrite/preads

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MyISAM slow creating new index, lots of (small) pwrite/preads

    System is Linux (2.6.9, SMP 8-core), MySQL is 6.0.3 lx86_64 glibc23.

    I have 32 gb of system memory. I set the myisam_sort_buffer_size to 28 gb. There are no users connected to this instance of the database other than me.

    I'm trying to create two indexes in my 50GB (1 bln rows) MyISAM database. After a while, the CPU utilization of mysqld drops to ~1%. At this point, the memory footprint of the daemon is 20gb. However, the index creation is progressing _very_ slowly (it's been running for over 24 hours now).

    The database files (and temp files) live on a PanFS filesystem (NFS). I'm almost certain the fact that my database lives on a NAS device is the cause of the slowness. However...

    I was inspecting mysqld LWPs and I'm seeing LOTS of calls to pread/pwrite (see below) in one of the threads. The reads/writes are small (1Kb each) which is probably trashing the NAS optimized for bulk ops.

    1) Is there a way to adjust the read/write buffer to a larger size?

    2) Would using mmap feature help in this case?

    Thank you


    pwrite(33, "\3rC6OC8WFSNHD\17\256\201\0\0\32C<{C6OC8WFSNH"..., 1024, 17206342656) = 1024 <0.000011>
    pread(33, "\2\3721B208vYmvGU\17\253\1\0\0\4%\361\2171B208vYm vG"..., 1024, 2657943552) = 1024 <0.000010>
    pwrite(33, "\3\256C7S03WdxOrN\17\256\241\0\0\32L\203\246C7S03 WdxOr"..., 1024, 17206326272) = 1024 <0.000011>
    pread(33, "\3\362vYdjvV\0\0\17\3347[vYdjvV\0\0\17\335\267\'vYdjvV"..., 1024, 10466302976) = 1024 <0.031036>
    pwrite(33, "\2\256WOOSto\0\0\17\332\17.WOOSto\0\0\17\333~^WOO Sto"..., 1024, 10396731392) = 1024 <0.000015>
    pwrite(33, "\2\336WMLeMF\0\0\0316X\201WMLeMF\0\0\0318\236KWML eMF"..., 1024, 17159898112) = 1024 <0.000016>
    pwrite(33, "\3\352C6007WEoWEN\17\256\201\0\0\32C<\0C6007WEoWE"..., 1024, 17058997248) = 1024 <0.000012>
    pwrite(33, "\3\"C60S6WEitOT\17\256A\0\0\0321\361[C60S6WEitO"..., 1024, 17230129152) = 1024 <0.000012>
    pwrite(33, "\3nWEQUDn\0\0\22od[WEQUDn\0\0\22y\16\360WEQUDn"..., 1024, 5174621184) = 1024 <0.000015>
    pwrite(33, "\2\252C0716VOErOB\17\257\1\0\0\32iw>C0716VOVap"..., 1024, 17254358016) = 1024 <0.000023>
    pwrite(33, "\3\252VrEJXM\0\0\n\357]wVrEJXM\0\0\n\362c\374VrEJXM"..., 1024, 13116532736) = 1024 <0.000012>
    pwrite(33, "\3\206CA632VoTfEu\17\256a\0\0\32:I\230CA632VoTfE" ..., 1024, 17099184128) = 1024 <0.000013>
    pwrite(33, "\3\256C6013VmJoOQ\17\255!\0\0\32\25\264\322C6013V mJoO"..., 1024, 17058935808) = 1024 <0.000011>
    pwrite(33, "\3\302VmYEBv\0\0\6c\271\367VmYEBv\0\0\6f\211lVmYE Bv"..., 1024, 2624415744) = 1024 <0.000012>
    pwrite(33, "\3\252VleqtD\0\0\0\322*!VleqtD\0\0\0\325U\347Vleq tD"..., 1024, 5898042368) = 1024 <0.000012>
    pwrite(33, "\3rCA624VMQHRC\17\255\1\0\0\32\22\232\2CA624VMQHR "..., 1024, 17058927616) = 1024 <0.000012>
    pwrite(33, "\2\346CA641VlnNBu\17\256\201\0\0\32C5\4CA641VlnNB "..., 1024, 17182015488) = 1024 <0.000012>
    pwrite(33, "\3bVjthzL\0\0\6\331C{VjthzL\0\0\6\334O\345VjthzL" ..., 1024, 303576064) = 1024 <0.000011>
    pread(33, "\3\266vdWtOu\0\0\23~B\220vdWtOu\0\0\23\217\246\tv dWtOu"..., 1024, 14297678848) = 1024 <0.117056>
    pwrite(33, "\2\256VghekP\0\0\32\22\237CVghekP\0\0\32\25\262~V ghekP"..., 1024, 16869562368) = 1024 <0.000013>
    pwrite(33, "\3nVfemDR\0\0\32#\300\261VfemDR\0\0\32*\206dVfemD R"..., 1024, 7683888128) = 1024 <0.000012>
    pwrite(33, "\3\346VedtYD\0\0\21\36\321\25VedtYD\0\0\21\37\10\ 244VedtYD"..., 1024, 11206996992) = 1024 <0.000015>

  • #2
    This looks like rebuilding indexes via keycache.
    make sure to set myisam_max_sort_file_size so indexes are rebuilt by sorting.

    Comment

    Working...
    X