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.
-
Is there a way to adjust the read/write buffer to a larger size?
-
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\2171B208vYmvG”…, 1024, 2657943552) = 1024 <0.000010>
pwrite(33, “\3\256C7S03WdxOrN\17\256\241\0\0\32L\203\246C7S03WdxOr”…, 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~^WOOSto”…, 1024, 10396731392) = 1024 <0.000015>
pwrite(33, “\2\336WMLeMF\0\0\0316X\201WMLeMF\0\0\0318\236KWMLeMF”…, 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\322C6013VmJoO”…, 1024, 17058935808) = 1024 <0.000011>
pwrite(33, “\3\302VmYEBv\0\0\6c\271\367VmYEBv\0\0\6f\211lVmYEBv”…, 1024, 2624415744) = 1024 <0.000012>
pwrite(33, “\3\252VleqtD\0\0\0\322*!VleqtD\0\0\0\325U\347VleqtD”…, 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\tvdWtOu”…, 1024, 14297678848) = 1024 <0.117056>
pwrite(33, “\2\256VghekP\0\0\32\22\237CVghekP\0\0\32\25\262~VghekP”…, 1024, 16869562368) = 1024 <0.000013>
pwrite(33, “\3nVfemDR\0\0\32#\300\261VfemDR\0\0\32*\206dVfemDR”…, 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>