Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL 5.6 slower than MariaDB 10.2

Discussion in 'Database Discussions' started by Nirjonadda, Aug 21, 2017.

  1. Nirjonadda

    Nirjonadda Well-Known Member

    Joined:
    May 8, 2013
    Messages:
    416
    Likes Received:
    10
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    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 .... ?
     
  2. Eminds

    Eminds Well-Known Member

    Joined:
    Nov 10, 2016
    Messages:
    204
    Likes Received:
    12
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    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.
     
  3. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,447
    Likes Received:
    56
    Trophy Points:
    28
    Location:
    India
    cPanel Access Level:
    Root Administrator
    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..
     
  4. Nirjonadda

    Nirjonadda Well-Known Member

    Joined:
    May 8, 2013
    Messages:
    416
    Likes Received:
    10
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    OK I am doing using MySQLTuner-perl. How to fixing Recommendations ?


    Code:
    [--] 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]#
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,165
    Likes Received:
    1,371
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page