Percona vs MariaDB vs MySQL in 2017

Slavik

XenForo moderator
Staff member
The latest versions of MariaDB (10.2+) have been putting out very strong out the box performance simply by throwing an 8gb buffer pool at it.
 

Brent W

Well-known member
We moved from Percona 5.7 to MariaDB 10.1 and haven't had any issues. Performance seems negligible.
 

eva2000

Well-known member
The latest versions of MariaDB (10.2+) have been putting out very strong out the box performance simply by throwing an 8gb buffer pool at it.
You using MariaDB 10.2.4 RC release https://community.centminmod.com/threads/mariadb-10-2-4-rc-now-available.10503/ ?

I am using MariaDB 10.1 on latest Centmin Mod beta LEMP stack installers and working fine as @BamaStangGuy has stated for large forums :) Will be testing MariaDB 10.2 once it's stable as Centmin Mod LEMP stack already has support in latest beta for MariaDB 10.2 just disabled for now.

Performance wise with would be eventually be between MariaDB 10.1/10.2, Percona 5.7, Oracle MySQL 5.7 for most folks.

It's not what you use but how you use those 3 that matter :D So just switching from one version/fork of MySQL won't really help that much without tuning and understanding your MySQL usage loads etc.
 

Slavik

XenForo moderator
Staff member

Brent W

Well-known member
Code:
MariaDB [(none)]> Show status like 'InnoDB_buffer%';
+-----------------------------------------+----------------------------------------+
| Variable_name                           | Value                                  |
+-----------------------------------------+----------------------------------------+
| Innodb_buffer_pool_bytes_data           | 70736953344                            |
| Innodb_buffer_pool_bytes_dirty          | 0                                      |
| Innodb_buffer_pool_dump_status          | Dumping buffer pool(s) not yet started |
| Innodb_buffer_pool_load_status          | Loading buffer pool(s) not yet started |
| Innodb_buffer_pool_pages_data           | 4317441                                |
| Innodb_buffer_pool_pages_dirty          | 0                                      |
| Innodb_buffer_pool_pages_flushed        | 45916683                               |
| Innodb_buffer_pool_pages_free           | 778418                                 |
| Innodb_buffer_pool_pages_lru_flushed    | 0                                      |
| Innodb_buffer_pool_pages_made_not_young | 19899055                               |
| Innodb_buffer_pool_pages_made_young     | 195696553                              |
| Innodb_buffer_pool_pages_misc           | 146957                                 |
| Innodb_buffer_pool_pages_old            | 1592430                                |
| Innodb_buffer_pool_pages_total          | 5242816                                |
| Innodb_buffer_pool_read_ahead           | 906822                                 |
| Innodb_buffer_pool_read_ahead_evicted   | 0                                      |
| Innodb_buffer_pool_read_ahead_rnd       | 0                                      |
| Innodb_buffer_pool_read_requests        | 634732054981                           |
| Innodb_buffer_pool_reads                | 3227448                                |
| Innodb_buffer_pool_wait_free            | 0                                      |
| Innodb_buffer_pool_write_requests       | 221343964
Looking forward to trying 10.2 out then. Currently have 70 Gigabytes sitting in the InnoDB Buffer Pool.
 

eva2000

Well-known member
Yes, ive been running it for imports, have found that it is giving incredible performance, havent honestly looked to see what theyve changed but all ive been doing it giving it a buffer pool of >=8gb and the time for imports is measurably better than 10.1
sweet :cool:
 

HWS

Well-known member
Having a lot of fun and reliable performance on Percona Cluster. There are a lot of useful new features in 5.7. For single databases we are very satisfied with MariaDB 10.1. I can only recommend both of them. We do not use Oracle MySQL any more.
 

Anthony Parsons

Well-known member
My understanding of the main performance difference between the three from looking at 2017 benchmarks are:
  1. MySQL 5.7 is out performing MariaDB 10 without caching, but once caching is implemented, MariaDB smacked it.
  2. MariaDB is feature rich balanced with performance. It's actively built and fixed, with modern requirements added.
  3. Percona is a performance version of MySQL, and tends to stay forked with MySQL, streamlined on performance. They did write the book on MySQL performance after all. More enterprise level from my understanding.
I was just reading a whole bunch on this the other day from the guy who fixed and forward managed TwitPic infrastructure. So I went out researching more and did some reading. Redis won! :)

MariaDB is super acceptable for the majority of websites nowadays, allowing forward growth. SSD's are making life much easier with these softwares... and NGINX is super awesome as the web server to MariaDB. NGINX + MariaDB 10.x + PHP-FPM 7.x + SSD = super fast setup with easy tweaking.
 

XxUnkn0wnxX

Active member
@Slavik i'm thinking of upgrading soon, maybe in few days

to 10.1, thats more stable right? via WHM Cpanel

now i am on percona server atm will it work OK? or do i need to revert back to mysql version on cpanel 1st?

also do i need to change anything in my.cnf before upgrade:
i know that issues can arise with incorrectly configured my.cnf or depreciated syntax...

so if you could point out anything that should be removed...

Code:
[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 600M
max-connections                = 500
thread-cache-size              = 128
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 32G
innodb_lock_wait_timeout = 180

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# Other #
max_allowed_packet=268M
and any Tips on a well optimized version of this file for mariaDB?
 

Anthony Parsons

Well-known member
Yer... been running mariaDB 10.2 on my servers for quite a while now. Love it. The table compression set by default is a nice touch too.
 

Mouth

Well-known member
MariaDB is better for advanced features, the latest security and adapting to new tech. Percona excels at database performance and diagnostics. Either one will be fine for XF.
 

XxUnkn0wnxX

Active member
@Slavik

no issues with install, had to shutdown and remove percona server 1st conzz install had conflicting packages..

also when i add this to my my.cnf file from the my.cnf.rpmnew

[client-server]
!includedir /etc/my.cnf.d

and try restarting mysql it takes for ever i let it load for 5 min and decided to cancel it...

should i even need this?

restarts fine without

[client-server]
!includedir /etc/my.cnf.d

EDIT:

Code:
[client]
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock
!includedir /etc/my.cnf.d
works if i have it like this,

[client-server] so slow....

also on a side note, i ran mysqltuner.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[!!] Key buffer size / total MyISAM indexes: 32.0M/35.8M
[!!] Read Key buffer hit rate: 85.5% (76 cached / 11 reads)
[!!] Write Key buffer hit rate: 50.0% (14 cached / 7 writes)

i'm not sure if i should be worried with MyISAM?

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 32.0G/777.6M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.125 %): 512.0M * 2/32.0G should be equal 25% <-- i am worried about this? how do set this right?
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Read buffer efficiency: 67.99% (1984 hits/ 2918 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

^^^^ anything else i should be worried about?

also should i convert any of these tables to innoDB? since they are MyISAM


Screen Shot 2017-05-23 at 5.10.41 pm.jpg
xf_search_index
xf_session
xf_session_admin
 
Last edited:
Top