MySQL 5.6 slower than MariaDB 10.2

August 22, 2017 01:46

Site performance is dependent on multiple things. No doubt you must have an optimized database, proper server resources, use technologies like nginx, litespeed etc.. as per your website requirements. make sure your server is optimized for resource usages. You need to evaluate all these things after you got your website live, you will have to analyse and monitor the performance.

0

24x7server

August 22, 2017 05:37

Currently installed: MySQL 5.6 but I am thinking Upgrade to MariaDB 10.2. Please any one can let me know that what are recommended for site speed? About Site performance .... ?

If you want to upgrade to MariaDB, you can do this in one click through the WHM... However, I would suggest you take a complete database backup before upgrading to MariaDB.. Also, after you upgrade, you will need to analyze the MySQL queries and processes to get the best out of the database performance.. There is no hard and fast rule that you can use and it will be 100% work for your infrastructure.. You can use MySQL tunner and MySQL primer to your advantage, but that will only be guidelines.. Although you might optimized database, but still see the website to perform not that optimal, in such cases, you might also have to consider webserver optimization completely, which may included things like configuring Nginx, LiteSpeed, varnish, etc..

0

Nirjonadda

August 22, 2017 09:31

You can use MySQL tunner

OK I am doing using MySQLTuner-perl. How to fixing Recommendations ?

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.37
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ----------------------------------------------                                                                                                                                                             --------------------
[--] Log file: ./na.mysite.com.err(0B)
[!!] Log file ./na.mysite.err doesn't exist
[!!] Log file ./na.mysite.err isn't readable.

-------- Storage Engine Statistics ---------------------------------------------                                                                                                                                                             --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                                                                                                                                                             ISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 126M (Tables: 20)
[--] Data in InnoDB tables: 8G (Tables: 378)
[--] Data in MEMORY tables: 2M (Tables: 5)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------                                                                                                                                                             --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations ------------------------------------------                                                                                                                                                             --------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics ---------------------------------------------------                                                                                                                                                             --------------------
[--] Up for: 14h 59m 7s (1M q [26.366 qps], 82K conn, TX: 17G, RX: 1G)
[--] Reads / Writes: 78% / 22%
[--] Binary logging is disabled
[--] Physical Memory     : 49.0G
[--] Max MySQL memory    : 5.7G
[--] Other process memory: 1.2G
[--] Total buffers: 169.0M global + 1.1M per thread (5000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 203.9M (0.41% of installed RAM)
[OK] Maximum possible memory usage: 5.7G (11.55% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory                                                                                                                                                              available
[OK] Slow queries: 0% (11/1M)
[OK] Highest usage of available connections: 0% (31/5000)
[OK] Aborted connections: 0.00%  (3/82448)
[!!] name resolution is active : a reverse name resolution is made for each new                                                                                                                                                              connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 883K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (28 temp sorts / 237K sorts)
[!!] Joins performed without indexes: 101532
[!!] Temporary tables created on disk: 47% (73K on disk / 156K total)
[OK] Thread cache hit rate: 99% (31 created / 82K connections)
[OK] Table cache hit rate: 92% (868 open / 937 opened)
[OK] Open file limit used: 0% (115/50K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- Performance schema ----------------------------------------------------                                                                                                                                                             --------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ----------------------------------------------------                                                                                                                                                             --------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics --------------------------------------------------------                                                                                                                                                             --------------------
[!!] Key buffer used: 23.6% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/39.7M
[OK] Read Key buffer hit rate: 98.4% (25K cached / 416 reads)
[!!] Write Key buffer hit rate: 9.3% (10K cached / 942 writes)

-------- InnoDB Metrics --------------------------------------------------------                                                                                                                                                             --------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/8.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.                                                                                                                                                             0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.43% (732389133 hits/ 736573007 total)
[OK] InnoDB Write log efficiency: 98.32% (3228968 hits/ 3284157 total)
[!!] InnoDB log waits: 0.06% (31 waits / 55189 writes)

-------- AriaDB Metrics --------------------------------------------------------                                                                                                                                                             --------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics --------------------------------------------------------                                                                                                                                                             --------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics --------------------------------------------------------                                                                                                                                                             --------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics -------------------------------------------------------                                                                                                                                                             --------------------
[--] RocksDB is disabled.

-------- Spider Metrics --------------------------------------------------------                                                                                                                                                             --------------------
[--] Spider is disabled.

-------- Connect Metrics -------------------------------------------------------                                                                                                                                                             --------------------
[--] Connect is disabled.

-------- Galera Metrics --------------------------------------------------------                                                                                                                                                             --------------------
[--] Galera is disabled.

-------- Replication Metrics ---------------------------------------------------                                                                                                                                                             --------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations -------------------------------------------------------                                                                                                                                                             --------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configurat                                                                                                                                                             ion with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 8G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files                                                                                                                                                              size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)
    innodb_log_buffer_size (>= 8M)
[root@na mysqltuner]#

0

cPanelMichael

August 22, 2017 14:35

Hello, You could try adding/modifying the values in your /etc/my.cnf file based on the "Variables to adjust" output. You may also want to consult with a system administrator if you would like additional help tuning the MySQL configuration: System Administration Services | cPanel Forums Thank you.

0

MySQL 5.6 slower than MariaDB 10.2

Comments

Didn't find what you were looking for?