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 :)