0

I have a MySQL application (MySQL 5.5) running on a virtual Ubuntu 16.04 (64bit) server with 2 kernels and 8 GB RAM. I consists of a few, small RW tables and appr. 30 read-only InnoDb tables with 100-150k rows in each (totaling ~ 1-2 GB data + indexes). Almost all queries gather data from several of the read-only tables (lots of different joins).

Wanting to increase overall performance, should I try to:

  • switch to MariaDb and Aria tables ?
  • switch to Percona and XtraDB ?
  • add more RAM (kernels) ?
  • or something else ?
christerk
  • 9
  • 4

2 Answers2

2

The overall performance of MySQL/MariaDB/Percona and InnoDB/XtraDB etc, is minimal. There are specific cases where one shines over another. You should not expect a magical improvement.

Instead... You should look at what is specifically causing trouble and tackle that.

High CPU: Add composite index / revise query -- but first locate the slowly running query.

High I/O: Same as high CPU, plus poor Joins, excessively sized datatypes (eg, BIGINT where SMALLINT UNSIGNED would suffice), etc.

Swapping: This is terrible -- probably caused by carelessly increasing values in my.cnf

Come back with worst query, together with SHOW CREATE TABLE.

Adding RAM won't help -- your dataset is significantly smaller than RAM. But do check innodb_buffer_pool_size.

Rick James
  • 80,479
  • 5
  • 52
  • 119
1

Blindly making changes to improve 'overall performance' rarely works very well. Have you run into any performance issues? Are you experiencing some slow queries? Can you post the explain statements for these queries? Is the server running out of memory and swapping?

If you post some answers to these questions I'm sure you'll get some specific help.

Also, there's a chapter in O'Reilly's High Performance MySQL which deals with diagnosing CPU-bound, memory-bound and IO-bound workloads, along with lots of other information about fixing performance issues. If you have time and inclination, I'd highly recommend giving that a read.

FraserES
  • 111
  • 2