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
  • 2

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
  • 78,038
  • 5
  • 47
  • 113
  • I had no hopes for any kind of large improvement in performance from switching db engines. I was more interested to find out if my almost 100% read app might perform better with MariaDb or Percona. I was however intrigued by your comment on 'carelessly increasing values in my.cnf'. Could you please elaborate ? – christerk Apr 19 '17 at 16:39
  • If you set various sizes too large, it could lead to MySQL/MariaDB/Percona using more RAM than exists. This causes swapping, which is terrible for performance. – Rick James Apr 19 '17 at 16:59
  • innodb_buffer_pool_size is 2GB, total memory used by MySQL is well within available RAM. I will disable caching and try to isolate a few of the slowest queries as soon as i can. – christerk Apr 19 '17 at 17:44
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
  • Although this advice is generally helpful, it doesn't directly answer the question, and so should be posted as a comment. Once you have enough reputation, you'll be able to comment on anyone's posts. For now, you will only be able to comment on your own posts. Take the [tour] if you haven't already! And welcome! – Hannah Vernon Apr 19 '17 at 18:26