Not the answer you need?
Register and ask your own question!

Performance Comparison AWS RDS Maria DB Vs Percona XtraDB Cluster on i3 NVMe SSD

percona_user_1981percona_user_1981 EntrantCurrent User Role Beginner
Hi,
I am currently evaluating Percona Server to check it's fitment to replace our MariaDB RDS instance. Under no load, I executed many queries to understand which is more performant and my results below. Both RDS and EC2 instances have the same configuration (32 vCPUs and 250 GB RAM). Even when I use sysbench's read only test, I see Maria DB is faster than Percona XtraDB Cluster hosted on EC2.


Scenario
Time taken by Percona XtraDBCluster (sec)
Time taken by MariaDB (sec)
Maria DB is faster than Percona XtraDB Cluster by (%)


Basic count operation on 70K records
0.594
0.453
23.73737374


Joining 3 tables and aggregation on 70k records
3.234
2.703
16.41929499


Joins 5 tables and does aggregation on 27 Million records
39.234
10.657
72.83733496


Aggregation on a sub query that fetches 70k records. The subquery request is inserted into a internal temp table by MySQL (InnoDB is the storage Engine for internal Temp Table)
3.672
3.125
14.89651416


Fetch 5K records after grouping acroos 70k records - joining 5 tables
6.344
3.859
39.17087011


Fetch 5K records after grouping acroos 27Million records - joining 5 tables
77.766
7.39
90.49713242


Aggregation on a sub query that fetches 5k after grouping on 70k records. The subquery request is inserted into a internal temp table by MySQL (InnoDB is the storage Engine for internal Temp Table)
4.453
4.375
1.751628116


Aggregation on a sub query that fetches 27 Million records. The subquery request is inserted into a internal temp table by MySQL (InnoDB is the storage Engine for internal Temp Table)
55.75
21.235
61.9103139


Aggregation on a sub query that fetches 5k after grouping on 27Million records. The subquery request is inserted into a internal temp table by MySQL (InnoDB is the storage Engine for internal Temp Table)
75.688
35.078
53.65447627


Aggregation on multiple sub queris that fetches 5k after grouping on 27Million records. This is the fixed version of the slow query reported by the NYL customer. The subquery request is inserted into a internal temp table by MySQL (InnoDB is the storage Engine for internal Temp Table)
208.922
68.484
67.22030231

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello, and welcome to the Forum.

    Can I just check - are you running MariaDB as part of a galera cluster too? We would need to see the my.cnf file for Percona XtraDB Cluster, to be able to know if any optimization has been attempted.

    This is especially relevant if you are comparing an unoptimized Percona XtraDB Cluster with an optimized-for-production clone of MariaDB then you won't be seeing the right kind of comparison to be able to draw any conclusions.

    Hope this helps, and please feel free to upload your my.cnf files. Thanks!
  • percona_user_1981percona_user_1981 Entrant Current User Role Beginner
    lorraine.pocklington Hi, Maria DB in production is an AWS RDS Engine and does not run on the cluster. I have identified two reasons for this difference in performance

    1) The Maria DB optimizer chooses better execution plans (This was verified by installing Maria DB in EC2 and ran the same queries). This is the root cause for the scenarios where you see a performance difference of more than 30%
    2) When Temp Tables are created it's very slow.

    Given below is the my.cnf file

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

    symbolic-links=0

    max_allowed_packet=100M
    net_buffer_length=4M
    net_read_timeout=300
    net_write_timeout=300
    innodb_buffer_pool_size=215G
    innodb_buffer_pool_instances=64
    innodb_log_file_size=12G
    innodb_log_buffer_size=128M
    innodb_flush_log_at_trx_commit=0
    innodb_thread_concurrency=64
    innodb_flush_method=O_DIRECT
    innodb_file_per_table=ON
    binlog_format=ROW
    innodb_io_capacity=10000# benchmark and change
    innodb_io_capacity_max=20000
    query_cache_size=100M
    sync_binlog=0
    innodb_thread_sleep_delay=10000

    innodb_write_io_threads=64 #2x of cores
    innodb_read_io_threads=64 #2x of cores
    innodb_sort_buffer_size=10M #perthread buffer
    innodb_spin_wait_delay=3 #half the standard value
    innodb_lock_wait_timeout=10
    innodb_lru_scan_depth=2048
    innodb_monitor_enable='all'
    innodb_old_blocks_pct=20
    innodb_old_blocks_time=1000
    innodb_page_cleaners=64
    innodb_print_all_deadlocks=ON
    innodb_purge_threads =32
    innodb_purge_batch_size = 2000
    innodb_flush_neighbors =0 #good for ssds
    innodb_flush_sync =true
    innodb_use_native_aio =true
    innodb_random_read_ahead =true
    innodb_checksum_algorithm=crc32
    innodb_io_capacity=400 # if thread
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello percona_user_1981 I think one of the issues is that you need to compare like with like. If your MariaDB is not in a cluster, then you can't really compare it with the performance of Percona XtraDB Cluster (PXC), especially not 'out of the box', since PXC is providing for a different kind of need to a standalone DB.

    I can understand that you might be looking to move in order to access the features offered by PXC, but in terms of comparing performance to measure like-for-like in a benchmark-type exercise, it's not a good/fair comparison. https://www.percona.com/software/mysql-database/percona-xtradb-cluster

    In this scenario, comparing with Percona Server with MariaDB is a better fit and would make for a better comparison of performance. https://www.percona.com/software/mysql-database/percona-server

    Could you let me know whether you are looking at PXC specifically to gain the HA availability advantages of that product, or if maybe PS might be the better start point? Then I will get one of the technical team to advise.

    Thanks!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.