1

I migrated my eCommerce website database (Magento) from MySQL to MariaDB (10.0.25-MariaDB) and my site isn't performing as well as it did with MySQL.

I've gone through my.cnf several times but to be honest I'm not sure about some of the settings but suspect there is one or more that are hurting performance since I had taken taken them over from my old my.cnf.

The server is a bit older with 2 x Xeon E5620 (16 cores total hyper-threaded) and 118GB RAM.

my.cnf

[mysqld]
local-infile = 1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

CACHES AND LIMITS

back_log = 200
interactive_timeout = 7200
wait_timeout = 7200
net_read_timeout = 120
net_write_timeout = 300
sort_buffer_size = 2M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
join_buffer_size = 8M
tmp_table_size = 128M 
max_heap_table_size = 128M 
query_cache_type = 1 
query_cache_size = 96M 
query_cache_limit = 2M 
max_connections = 70
thread_cache_size= 32
open_files_limit = 65535
table_definition_cache = 4000
table_open_cache = 15000 
max_allowed_packet = 32M
key_buffer_size = 512M # up from 256#

INNODB_

innodb_thread_concurrency = 0 
innodb_lock_wait_timeout = 7200
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_buffer_pool_size = 26G 

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

MYSQL TUNER REPORT

Storage Engine Statistics 
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 96K (Tables: 163)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in InnoDB tables: 7M (Tables: 954)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 2K (Tables: 33)
[!!] Total fragmented tables: 913

Performance Metrics 
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 26.7G global + 8.3M per thread (70 max threads)
[OK] Maximum possible memory usage: 27.3G (25% of installed RAM)
[OK] Slow queries: 0% (14K/1B)
[OK] Highest usage of available connections: 47% (33/70)
[OK] Key buffer size / total MyISAM indexes: 512.0M/862.4M
[OK] Key buffer hit rate: 100.0% (17B cached / 7M reads)
[OK] Sorts requiring temporary tables: 0% (42K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 2417569
[OK] Temporary tables created on disk: 24% (3M on disk / 15M total)
[OK] Thread cache hit rate: 99% (33 created / 1M connections)
[!!] Table cache hit rate: 0% (1K open / 225K opened)
Use of uninitialized value $myvar{"table_cache"} in concatenation (.) or string
    at mysqltuner.pl line 868, <> line 2 (#1)
[OK] Open file limit used: 0% (69/65K)
[OK] Table locks acquired immediately: 99% (653M immediate / 653M locks)
[OK] InnoDB data size / buffer pool: 7.4M/26.0G

Recommendations 
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Upgrade MySQL to version 4+ to utilize query caching
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Upgrade to MySQL 4.1+ to use concurrent MyISAM inserts
Variables to adjust:
join_buffer_size (> 8.0M, or always use indexes with joins)
table_cache (> )

Are there any settings that are incorrect, missing or that should be changed?

  • Is it a current version of mysqltuner? It should know MariaDB but it writes something about MySQL 4.1 which seems the server version was not properly identified and so some stats may be off. Do you really have only 7.4M in InnoDB tables? If yes then the buffer pool is just a waste of memory. Otherwise the config usually is not the reason of bad performance, queries is. Enable the slow query log, set it to log queries over 1 second and analyze it after a day (use for example pt-query-digest). – jkavalik Jun 25 '16 at 14:46
  • @jkavalik mysqltuner may be confused by the version ('10.1') which as a string orders before '4.1' and '5'. – ypercubeᵀᴹ Jun 25 '16 at 17:43
  • @ypercubeᵀᴹ It works for me with 10.0 so I suppose 10.1 would work too, but did not check explicitly. – jkavalik Jun 25 '16 at 17:50
  • Looks like I used an older version of the mysqltuner, but I'm more concerned with any incorrect settings in my.cnf that would harm performance. – sr_1436048 Jun 27 '16 at 9:54
  • I don't see anything directly harming in there (other than the query cache which is a bit too big and imho should be disabled) but you should definitely check how much data is stored in MyISAM and InnoDB tables respectively - these values are very important to decide the right size of the key buffer and buffer pool. – jkavalik Jun 27 '16 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.