I) An upgrade which hasn’t gone as planned

One of our customer needed help after trying to upgrade their slave from Percona Server 5.6 to Percona Server 5.7. (here 5.7.17 is used)
Indeed, from time to time, the replication lag was linearly growing, which could be quite annoying…

(good guess, 5.7 is the red dashed line!)

Their use case is quite interesting :

• a lot of Queries Per Second (read & write, nearly 10k queries/s on this slave)
• a lot of tables (about 600k spread across different databases)
• a lot of writes
• 24 CPUs, and 386G of RAM
• A big buffer pool (270G)
• not that fast SSD disks.

Because we have a high QPS but also a lot of writes, we need to fine tune the innodb settings.
…

I) What is parallel replication?

Parallel replication is a nice feature introduced in MariaDB 10.0, and which allows to run in parallel some queries on a slave. Basically, by default (the conservative mode), it runs in parallel transactions that couldn’t have any conflict. Note you have to enable GTID to use this feature.

Starting with MariaDB 10.1.3 new parallel modes have been introduced : optimistic & aggressive mode. Those modes try to increase the parallelism by executing queries, and handles conflicts with rollback and retry.

This feature is known to work well on InnoDB, but what’s happening with an engine like TokuDB?

II) Let’s try it on TokuDB!

To enable the parallel replication, you …

I) Why using TokuDB?

I have been playing with TokuDB since 2013. Although it’s a really good storage engine, it’s sometimes full of surprises.

Initially I migrated one of my customer from InnoDB to TokuDB because it was solving all their issues : their DB has been growing to more then 1TB, and they were facing insert performances issues.

After the migration, there DB size dropped to 100GB which was really amazing, without loss in performances compared to InnoDB, and way better insert performances.

II) Read Free Replication

At this time I was using the TokuDB enterprise MariaDB 5.5.41 custom version, with Read Free Replication enabled on a slave.

To enable it, you need to have row-based replication enabled on the master and to set the following variables on the slave :

I – What is a query cache?

The MySQL query cache was introduced as part of version 4.0 of MySQL. Its main function is simple: it is a giant hash table that associates bulk SQL queries with a group of results.

It signifies that if you add a space or change a capital letter in your SQL query, it will be different from the query cache point of view.
This simple approach also signifies that with a small modification/writing in a table, all the query cache entries concerning the table in question will be invalidated.
In applications that use the reading in an intensive fashion, without a lot of writing, it seems that enabling the query cache is a good idea.

II – Why disable the Query Cache?

With the democratization of multi-cpu/multi-core systems, the scalability problems are more and more critical from a MySQL perspective.
The query cache is …

I) The Best Performances Ever ? Let’s have a closer look

MariaDB 10.1 and MySQL 5.7 have been officially GAed for a few weeks now, and each company claims to have awesome performances.

MariaDB claims that the biggest improvement on its new version is scalability on massively multithreaded hardware, reaching more than 1 million queries per second on a simplified OLTP benchmark.

On the MySQL side, the same type of improvement is achieved on mixed OLTP_RO benchmark.

II) The Real-World Performances

OLTP benchmarks are great, but what is the behaviour of MySQL / MariaDB on real …

I) What’s new in PrestaShop 1.6.1.0?

A lot of things have been improved on PrestaShop 1.6.1.0 :

• Design
• Invoices
• Search
• Performance : we have done a lot of work to improve the overall performances of PrestaShop, and reached in some cases a whooping 10x improvement.

Let’s now dig into the optimizations we have put in place, and do some benchmarks.

II) Which parts of PrestaShop are faster? 1) Faster single-threaded performances

Thanks to our internal tools (to be publicly released soon!), we quickly identified the bottlenecks of PrestaShop which were impacting directly the generation time of the pages, and hence the SEO and the user satisfaction.

At the PHP level, without the cache activated, after launching a crawl on a real shop, we collected all the datas we needed in a few minutes :

…

I – Table Statistics – Why?

When you execute a SQL query that uses an index, creates a join, or another complex operation, MySQL will read the statistics linked to these tables, which will allows it to chose the optimal plan of execution.
For InnoDB for example, this behaviour is controlled by innodb_stats_% type variables:

show variables LIKE 'Innodb_stats_%'; 
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | OFF         |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_sample_pages            | 8           |
| innodb_stats_transient_sample_pages  | 8           | …