Re: Thoughts on partitioning by hour - and which partition type?

[Adrian Snow]

Hi, and thanks for your interest in this issue.

As Phil has pointed out, the reason I chose InnoDB was that it only does locking at row level, not table level.

Now, perhaps I've made a mistake here, but my application has many inserters and only a few selectors - if you know what I mean.

Currently I am inserting in the order of 10 million rows per hour, so about 2.7K rows per second.

I must admit that I'm running into performance issues and with the number of inserts set to only increase I'm wondering what to do next!

Over the last few weeks I've suffered problems where I can't acutally delete the data as fast as it is coming in! Partitioning the table into hour_parts has really helped me drop redundant data very quickly, at the moment I'm not actually archiving any of it, but the plan is to keep between 3-6 hours of live data, and perhaps archive off a weeks worth.

I'm now looking into lower level improvements on performnce, but having just upgraded our servers to Centos 5.1 64bit, MySQL 5.1.24 on Dual 3GHz DL360s with 32Gbytes of RAM running suicide RAID-0, it's all getting a bit tricky!

See my load averages below!

top - 22:17:59 up 16 days, 7:07, 3 users, load average: 4.28, 4.22, 4.26
Tasks: 128 total, 2 running, 126 sleeping, 0 stopped, 0 zombie
Cpu(s): 74.8%us, 4.8%sy, 0.0%ni, 16.8%id, 0.7%wa, 0.2%hi, 2.8%si, 0.0%st
Mem: 32962188k total, 32800004k used, 162184k free, 212420k buffers
Swap: 2031608k total, 74704k used, 1956904k free, 1295444k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12479 mysql 15 0 29.1g 28g 8796 S 115 90.8 591:46.52 mysqld
30379 root 25 0 411m 364m 9772 R 100 1.1 335:04.38 rcs_probe
13766 root 20 0 1364m 379m 8292 S 50 1.2 248:50.43 java
8973 root 15 0 206m 171m 9.8m S 34 0.5 144:40.91 rcs_probe
15871 root 15 0 194m 163m 9976 S 20 0.5 90:15.53 rcs_probe

The rcs_probe processes are gathering data, this is funnelled through the java process where it is inserted into the database. The top above is taken from our busiest server, one thing I seem to have noticed is that since introducing MySQL 5.1.24 and using paritions the MySQL process 'seems' to be consuming more CPU, however, unfortunately I don't have any real metrics to prove this, it's just a gut feeling.

best
Ade