Something is very wrong with disk caching in 7.0

[Martin Nilsson]

I'm playing with 7.0 on a brand new dual core P4 box.
Boot disk 80GB ATA with OS
Data disk 6*36GB SCSI RAID5 MegaRAID 320-2e (8x PCI-e,this one rocks!)
CPU: Pentium D 820 Dual core: 2*2.8GHz/1MB cache.
Motherboard: Intel D945GNT
Memory: 512MB DDR-II
NIC: Broadcom 5751 on 1x PCI-e card.


I tested how long it it takes to load a 3GB mysql database from a sql file.

The mysql daemon takes about 320MB memory with my config.
To my surprise the OS pages out the mysqld to use memory for caching the 
  file I/O. After the import is done I have 320MB of swap usage and all 
processes have reallay small RES valiues in top.

Next try: I add 2*1GB sticks so memory is not so constrained.

I increase the buffers of mysqld (new footprint 715MB) and also add 
innodb support with a 10GB innodb file as default.

When starting mysql the 10GB file is created in the meantime the OS 
swaps out all the processes including the mysqld, the systhem pages a 
lot ( to the ATA disk) as the file is created on the MegaRAID array.

Following are a couple of top snapshots of the process.

last pid:   858;  load averages:  0.14,  0.06,  0.11               up 
0+00:22:44  08:13:34
31 processes:  1 running, 30 sleeping
CPU states:  0.0% user,  0.0% nice, 50.5% system, 18.9% interrupt, 30.6% 
idle
Mem: 419M Active, 1898M Inact, 136M Wired, 40M Cache, 112M Buf, 3656K Free
Swap: 999M Total, 7336K Used, 992M Free, 380K Out

   PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
   858 mysql       4  20    0   615M   608M kserel 1   0:07  0.00% mysqld



last pid:   858;  load averages:  0.53,  0.16,  0.15               up 
0+00:22:56  08:13:46
31 processes:  1 running, 30 sleeping
CPU states:  0.2% user,  0.0% nice, 50.2% system, 13.9% interrupt, 35.7% 
idle
Mem: 2568K Active, 2288M Inact, 136M Wired, 67M Cache, 112M Buf, 3656K Free
Swap: 999M Total, 132M Used, 867M Free, 13% Inuse, 4K In, 11M Out

   PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
   858 mysql       4  20    0   615M   485M kserel 1   0:11  0.00% mysqld



last pid:   859;  load averages:  0.99,  0.28,  0.19               up 
0+00:23:12  08:14:02
32 processes:  2 running, 30 sleeping
CPU states:  0.1% user,  0.0% nice, 58.0% system, 17.3% interrupt, 24.6% 
idle
Mem: 3540K Active, 2317M Inact, 136M Wired, 37M Cache, 112M Buf, 3656K Free
Swap: 999M Total, 218M Used, 781M Free, 21% Inuse, 328K In, 25M Out

   PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
   859 root        1  -8    0  1780K   892K biord  0   0:00  1.40% systat
   858 mysql       4 101    0   615M   397M RUN    1   0:18  0.00% mysqld



last pid:   859;  load averages:  1.76,  0.57,  0.30               up 
0+00:23:55  08:14:45
32 processes:  1 running, 31 sleeping
CPU states:  0.3% user,  0.0% nice, 50.2% system, 15.6% interrupt, 33.9% 
idle
Mem: 2860K Active, 2283M Inact, 136M Wired, 72M Cache, 112M Buf, 3656K Free
Swap: 999M Total, 615M Used, 384M Free, 61% Inuse, 536K In, 48K Out

   PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
   821 root        1  96    0  2416K   580K CPU1   0   0:00  0.20% top
   858 mysql       4  20    0   615M  1440K kserel 1   0:29  0.00% mysqld



last pid:   862;  load averages:  0.18,  0.46,  0.31               up 
0+00:27:03  08:17:53
34 processes:  1 running, 33 sleeping
CPU states:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100% 
idle
Mem: 18M Active, 2264M Inact, 135M Wired, 75M Cache, 112M Buf, 3656K Free
Swap: 999M Total, 613M Used, 386M Free, 61% Inuse

   PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
   858 mysql       6  20    0   687M 22920K kserel 0   0:42  0.00% mysqld



This can't be rigt, why are active processes swapped out just to use the 
memory for caching a file that is written to once! The box have 2.5GB 
memory, it should be plenty for this!

I can't test under 5.4 on this box, the ATA controller (ICH7R) is not 
recognised at all so I have no OS disk!

Regards,
Martin Nilsson