Re: Benchmark shows very slow bulk delete

On 01/27/10 14:28, Thom Brown wrote:
> Had a quick look at a benchmark someone put together of MySQL vs
> PostgreSQL, and while PostgreSQL is generally faster, I noticed the bulk
> delete was very slow:
> http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html

I wish that, when people got the idea to run a simplistic benchmark like
this, they would at least have the common sense to put the database on a
RAM drive to avoid problems with different cylinder speeds of rotational
media and fragmentation from multiple runs.

Here are some typical results from a desktop SATA drive:

ada0
512 # sectorsize
500107862016 # mediasize in bytes (466G)
976773168 # mediasize in sectors
969021 # Cylinders according to firmware.
16 # Heads according to firmware.
63 # Sectors according to firmware.
6QG3Z026 # Disk ident.

Seek times:
Full stroke: 250 iter in 5.676993 sec = 22.708 msec
Half stroke: 250 iter in 4.284583 sec = 17.138 msec
Quarter stroke: 500 iter in 6.805539 sec = 13.611 msec
Short forward: 400 iter in 2.678447 sec = 6.696 msec
Short backward: 400 iter in 2.318637 sec = 5.797 msec
Seq outer: 2048 iter in 0.214292 sec = 0.105 msec
Seq inner: 2048 iter in 0.203929 sec = 0.100 msec
Transfer rates:
outside: 102400 kbytes in 1.229694 sec = 83273 kbytes/sec
middle: 102400 kbytes in 1.446570 sec = 70788 kbytes/sec
inside: 102400 kbytes in 2.446670 sec = 41853 kbytes/sec

This doesn't explain the 4-orders-of-magnitude difference between MySQL
and PostgreSQL in bulk_delete() (0.02 vs 577) but it does suggest that
some other results where the performance is close, might be bogus.

It's tough to benchmark anything involving rotational drives :)