Re: Probable faq: need some benchmarks of pgsql vr.s mysql

On 10/29/2010 02:58 PM, Brian Hurt wrote:
>
> My company is currently hitting a problem with mysql/innodb having
> really slow insert performance (we're seeing ~1K rows/second). My boss
> wants to go and spend a bunch of money on the Tokutek backend. I'd
> rather we save the money and go to postgres instead. We're not heavily
> invested in mysql at this point (fixing our queries to switch from mysql
> to postgres would take about five minutes). But my boss wants to see
> some benchmarks.

hmm 1k/s sounds very slow for MySQL/innodb if you are batching your
inserts (either multi-value inserts or larger transactions) - is there
anything special to that data(very wide, enourmous number of indexes etc)?

>
> I've googled around for a while, but all the benchmarks I've found
> commit one or more "fatal flaws", which render the benchmark pointless
> at best:
>
> 1) Comparing Postgres to MyISAM. Transactions are not an option for us,
> so it doesn't matter if MyISAM is a hundred times faster. I want to
> compare Postgres to InnoDB (bonus points for Postgres vr.s Tokutek).

never seen a comparison against Tokutek so you might have to benchmark
for yourself.

>
> 2) Using the default configurations. Be serious- is there any one who
> cares the least about performance who uses the default configuration?
>
> 3) Using old versions of Postgres. I'd like the survey to at least use
> the 8.x series, bonus points for it being 9.x.
>
> 4) Not using COPY for inserts. We would, of course, be using the copy
> command for inserts.
>
> Here's the thing. I have personally seen postgres 8.1 insert 30K
> rows/second, in to a real table, on crappy hardware (single slow IDE
> drive, old crappy hardware). I would be shocked if I can't improve on
> the InnoDB numbers by at least an order of magnitude. I'm whipping
> together a personal benchmark to show this. But I need a "professional
> looking" benchmark, with pretty charts and graphs and etc., to back me up.

Not sure what you would consider "professional" - but I did some testing
back in the 8.4 days here:
http://www.kaltenbrunner.cc/blog/index.php?/archives/27-Benchmarking-8.4-Chapter-2bulk-loading.html
- not sure if that actually matches your workload(but I guess you could
easily test yourself if it is that simple to convert your app).

>
> Help?

I don't think advocacy is actually the right list to ask maybe you would
get a wider audience on -performance or -general.

Stefan