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.
[TABLE=“border: 0, cellpadding: 0, cellspacing: 0”]
[TR]
[TD=“width: 293”]Scenario[/TD]
[TD=“width: 141”]Time taken by Percona XtraDBCluster (sec)[/TD]
[TD=“width: 107”]Time taken by MariaDB (sec)[/TD]
[TD=“width: 130”]Maria DB is faster than Percona XtraDB Cluster by (%)[/TD]
[/TR]
[TR]
[TD=“width: 293”]Basic count operation on 70K records[/TD]
[TD]0.594[/TD]
[TD]0.453[/TD]
[TD]23.73737374[/TD]
[/TR]
[TR]
[TD=“width: 293”]Joining 3 tables and aggregation on 70k records[/TD]
[TD]3.234[/TD]
[TD]2.703[/TD]
[TD]16.41929499[/TD]
[/TR]
[TR]
[TD=“width: 293”]Joins 5 tables and does aggregation on 27 Million records[/TD]
[TD]39.234[/TD]
[TD]10.657[/TD]
[TD]72.83733496[/TD]
[/TR]
[TR]
[TD=“width: 293”]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)[/TD]
[TD]3.672[/TD]
[TD]3.125[/TD]
[TD]14.89651416[/TD]
[/TR]
[TR]
[TD=“width: 293”]Fetch 5K records after grouping acroos 70k records - joining 5 tables[/TD]
[TD]6.344[/TD]
[TD]3.859[/TD]
[TD]39.17087011[/TD]
[/TR]
[TR]
[TD=“width: 293”]Fetch 5K records after grouping acroos 27Million records - joining 5 tables[/TD]
[TD]77.766[/TD]
[TD]7.39[/TD]
[TD]90.49713242[/TD]
[/TR]
[TR]
[TD=“width: 293”]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)[/TD]
[TD]4.453[/TD]
[TD]4.375[/TD]
[TD]1.751628116[/TD]
[/TR]
[TR]
[TD=“width: 293”]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)[/TD]
[TD]55.75[/TD]
[TD]21.235[/TD]
[TD]61.9103139[/TD]
[/TR]
[TR]
[TD=“width: 293”]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)[/TD]
[TD]75.688[/TD]
[TD]35.078[/TD]
[TD]53.65447627[/TD]
[/TR]
[TR]
[TD=“width: 293”]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)[/TD]
[TD]208.922[/TD]
[TD]68.484[/TD]
[TD]67.22030231[/TD]
[/TR]
[/TABLE]
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!
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
- 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%
- 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
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. [url]https://www.percona.com/software/mysql-database/percona-xtradb-cluster[/url]
In this scenario, comparing with Percona Server with MariaDB is a better fit and would make for a better comparison of performance. [url]https://www.percona.com/software/mysql-database/percona-server[/url]
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!