Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.

I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests comparing with MySQL too. We observe that MySQL is twice as fast as PostgreSQL.

I did a tangible performance test:

  • Same columns in table with equivalent column datatypes.
  • Same number of rows.
  • Same indexes in both (primary key included).
  • The CPU load are idle and Postgres machine it's significantly better.
  • And the same query (obviously).

What am I doing wrong?

P.S: I read many "howtos" on performance tuning for database engines.
P.S(2): We're using InnoDB (one file per table) on the MySQL database.


Hi Mat!

I did the three common select (and hardest) queries.

The question about disk, certainly it's not the same; In Postgres it's a SSD (almost three time fastest).

MySQL cache data:

+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 16777216             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 64                   |
| thread_cache_size            | 8                    |
+------------------------------+----------------------+

I don't know how to view this in PostgreSQL.

Thanks in advance.

share|improve this question

closed as too broad by dezso, bluefeet, RolandoMySQLDBA, Max Vernon, Mat Oct 4 '13 at 5:25

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Sorry for my English –  Javier Valencia Apr 30 '13 at 14:52
    
(Your English is fine.) Did you do load tests, or just individual queries? Could you show the database settings you used (especially things like cache sizes)? (Same disks in both cases I presume?) –  Mat Apr 30 '13 at 15:03
1  
Can you post the query and the Postgres execution plan using explain analyze. To make it easier to read, you can upload the plan to explain.depesz.com –  a_horse_with_no_name Apr 30 '13 at 16:17
1  
If Postgres is running on a SSD you almost certainly have to tune postgresql.conf –  a_horse_with_no_name Apr 30 '13 at 16:45
    
thanks a lot for your help!!! –  Javier Valencia Apr 30 '13 at 21:11

1 Answer 1

up vote 5 down vote accepted

MySQL and PostgreSQL are quite difference performance-wise. InnoDB and PostgreSQL tables are optimized for different sorts of queries. Understanding these differences is important to understanding how to get good performance out of either.

As an example, let's look at the most obvious difference.

PostgreSQL vs MySQL/InnoDB Table Structure and What This Means for Performance

In general, on complex work-loads, PostgreSQL will be faster, but on simple primary key lookups MySQL with InnoDB will be faster.

PostgreSQL tables are heap tables. There is no option to build a table which is not a heap table. The cluster command simply rewrites the heap ordered by a specified index. Indexes then provide heap locations for tuples with various values. Indexes cannot be traversed in physical order, only logical order so they have a lot of random disk I/O while reading a table sequentially usually means a lot of sequential disk I/O, since you can read a table in physical order. Sequential disk I/O gets to use read-ahead cache and some other OS-level optimization.

What this means is that if you need a significant portion of records or over a few pages, it is usually faster to just read the pages from disk. On the other hand, a primary key lookup for a table requires hitting the index, lookup up the location in the file then hit the heap table and pull the record. This means a number of pieces of random disk I/O.

InnoDB uses a different approach. With InnoDB, the table is a b-tree index with the actual data in the index payload. This means that a primary key lookup already gets to pull the data from the leaf page, and so less random disk I/O is required for this. At the same time, an index scan requires traversing two indexes instead of one, meaning use of any index other than the primary key ends up being slower and sequential scans are slower still.

Getting Diagnoses in PostgreSQL

I think you want to use something like:

 EXPLAIN (analyse, buffers, verbose)
 [query];

That will give you the query plan, initial estimates, actual times, buffer usage, and much more.

share|improve this answer

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