0

this is my first question here in DBA.

I wish to do a performance comparison between MySQL and PostgreSQL using same database on the basis of total time taken to parse + execute + send result back to client.

For an accurate measurement in MySQL, I have set profiling ON and uses SHOW PROFILES. What is the similar option in PostgreSQL? I don't need Explain Analyze as it doesn't include the time taken for I/O.

(Based on the time displayed in console, MySQL is performing better than PostgreSQL.)

--------------------------------------------------------------------------------------------------------------

One more question!

For finding out the faster DBMS, is it better to take the total time or just the execution time(excluding the time taken to send to client).

I wholeheartedly appreciate all your valuable answers for the first question and suggestions for the second question :) TIA

closed as too broad by a_horse_with_no_name, Paul White, Mark Storey-Smith, RLF, Max Vernon Nov 26 '14 at 16:33

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • X-posted from stackoverflow.com/q/27142330/398670 – Craig Ringer Nov 26 '14 at 8:32
  • 1
    This question is way too broad as stated. You haven't even said which Engine you are using for MySQL, which will have a huge impact on performance. – John Powell Nov 26 '14 at 8:45
  • 1
    Simply timing some queries is not a valid benchmark to begin with. You are completely ignoring concurrent access with that test. If you have multiple transactions reading and writing to the same tables this makes a huge difference. Benchmarking specific queries in a single user environment only makes sense if that's how the DB will be used later. – a_horse_with_no_name Nov 26 '14 at 11:51
  • 1
    Plus you probably don't exploit specific features of a DBMS. A query using window functions in Postgres will most probably be really slow when re-written for MySQL. I once migrated a stored procedure that did an aggregation in MySQL and ran for about 1 hour to a single UPDATE statement in Postgres that finished in 10 minutes. – a_horse_with_no_name Nov 26 '14 at 11:55
  • Could you show us your testcases? – Frank Heikens Nov 26 '14 at 15:41
2

Take a look here. It's a tool which genuinely tries to compare like with like in terms of database benchmarking!

1

in PostgreSQL you should use a tool called pgbench. it allows you to run custom scripts with an arbitrary number of concurrent requests and all that. it is also able to create random values for your script and all that - it is really powerful. for the test: make sure you got a proper setting for -j (number of threads used by pgbench) so that pgbench is able to create the load. also make sure the system is properly tuned (especially synchronous_commit set to identical values, checkpoint_segments, shared_buffers). make sure you got proper fillfactor settings on those tables and ensure that prepared plans are used (pgbench can do that for TPC-B). if you got it right, it expect MySQL to be beaten by a wide margin in a high-concurrency read / write test (transactional of course).

  • 2
    While I agree that pgbench is a valuable tool, you can't use it to compare two different DBMS (unless you can re-create the exact same workload with a different tool on the other DBMS) – a_horse_with_no_name Nov 26 '14 at 11:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.