Hello,
My RDBMS environnent currently use MySQL 5.1.30 but I intend to upgrade MySQL to MariaDB or Percona server (5.5 branch) server.
I started with MariaDB 5.5.23 (will try Percona just after).
MySQL and MariaDB are on the same server, with different data dir but in the same partition. (mysqldump to import/export)
MariaDB use the same configuration file.
port = 3307
socket = /tmp/maria.sock
[mysqld]
port = 3307
socket = /tmp/maria.sock
max_allowed_packet = 67108864
table_cache = 1024
myisam_sort_buffer_size = 64M
thread_concurrency = 16
log-bin=mysql-bin
server-id = 1
max_connections = 4096
slow_query_log = 1
long_query_time = 2
binlog_format=mixed
expire_logs_days = 14
wait_timeout=1800
interactive_timeout=1800
join_buffer_size = 262144
key_buffer_size = 4096M
query_cache_limit = 8M
query_cache_size = 64M
query_prealloc_size = 16384
read_buffer_size = 4194304
read_rnd_buffer_size = 16777216
sort_buffer_size = 4194304
thread_cache_size = 64
tmp_table_size = 67108864
transaction_alloc_block_size = 16384
transaction_prealloc_size = 8192
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size = 1G
innodb_buffer_pool_size = 10G
default-storage-engine = innodb
group_concat_max_len = 1M
[mysqldump]
quick
max_allowed_packet = 67108864
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
I selected few queries from my application to try performance.
Unfortunately I was disappointed with the performance of Mariadb almost 1,5 times slower…
Databases contains ~200 Innodb tables and many triggers & stored procedures but no(or not many) transactions.
I use simple php script and / or profiling option to get these results.
Someone should have an idea? Maybe 5.1 branch is faster for my application than 5.5
PS:SQL_NO_CACHE is used to avoid cache and when I test mariadb, mysql is shutdown vice versa
EDIT:
For some query, EXPLAIN between MySQL and Maria is different for the same query (on column Extra)
Example
MySQL
±---------------------------------------------+
| Extra
±---------------------------------------------+
| Using where; Using temporary; Using filesort
| Using where
±---------------------------------------------+
2 rows in set (0.01 sec)
Mariadb
±---------------------------------------------------------- ----------+
| Extra
±---------------------------------------------------------- ----------+
| Using index condition; Using where; Using temporary; Using filesort
| Using where
±---------------------------------------------------------- ----------+
2 rows in set (0.01 sec)