Skip site navigation (1) Skip section navigation (2)

Re: Benchmark shows very slow bulk delete

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Benchmark shows very slow bulk delete
Date: 2010-01-27 14:23:59
Message-ID: hjpi9k$qe1$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-performance
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 :)


In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2010-01-27 14:49:06
Subject: Re: Benchmark shows very slow bulk delete
Previous:From: Thom BrownDate: 2010-01-27 13:28:09
Subject: Benchmark shows very slow bulk delete

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group