Search Postgresql Archives

Re: clustered index benchmark comparing Postgresql vs Mariadb

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Wed, Aug 30, 2017 at 10:03 PM, 유상지 <y0212@xxxxxxxxx> wrote:

 

I want to get help with Postgresql.

I investigated that Postgresql could be rather fast in an environment using a secondary index. but It came up with different results on benckmark.

The database I compared was mariadb, and the benchmark tool was sysbench 1.0.8 with the postgresql driver.

Server environment: vmware, Ubuntu 17.04, processor: 4, RAM: 4 GB, Harddisk: 40 GB, Mariadb (v10.3), PostgreSQL (v9.6.4)

 

The created sysbench progress statement is as follows.

Sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver = mysql --mysql-host = 127.0.0.1 --mysql-port = 3306 --mysql-user = root --mysql-password = ajdajddl75 - Mysql-db = sysbench --tables = 3 --table_size = 100000 --report-interval = 10 --secondary = on --time = 60 

 

Used options

Select only, Num of threads = 1, num of tables = 3, table-size = 100000 and Table-size = 1000000, secondary index select instead of primary key.

 

 

 

 My hypothesis was that  selecting by secondary index in postgresql is faster than in Mariadb. However, the results depend on table size.

 

Postgresql was faster than Mariadb when the table size was 1000000, but slower at 100000.

 

Cluster secondary indexes were faster than those without cluster indexes in pg, but slower than mariadb.

 

I'd like to see the difference in architecture rather than optimization, so every benchmark executed with default options except for clustered index.

 I wonder if there are any settings I missed.

 

I would be very pleased if someone could explain why these results came up.


>Postgresql was faster than Mariadb when the table size was 1000000, but slower at 100000.

You made a general statement, but you left out a lot of important information.

A. Did you do an ANALYZE table_name BEFORE running your test?
B. Did you verify the index was being used with EXPLAIN your_query?
C. What was the exact query you used?
D. Most important, what is the structure of the table and index?
E. How much system memory is there?
F. In postgresql.conf What are the settings for
    1. shared_memory
    2. work_memory
    3. All Planner Cost Constants values, All Genetic Query Optimizer values
--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux