Results 1 to 6 of 6

Thread: mysql Tuning

  1. #1
    Join Date
    Oct 2016
    Location
    England
    Posts
    175

    mysql Tuning

    Hi Everyone,

    I've been reviewing TOP on one of our servers and I'm noticing mysql using a lot of resources. Therefore I decided to try and tune it a little better to help reduce the spikes. I'm ran mysqltuner and its giving me a lot of technical suff I've never had to deal with. Any advice would be really appreciated

    Code:
    General recommendations:
        Restrict Host for user@% to user@SpecificDNSorIp
        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
        Set thread_cache_size to 4 as a starting value
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        Beware that open_files_limit (65000) variable
        should be greater than table_open_cache (2000)
        Performance should be activated for better diagnostics
        Consider installing Sys schema from https://github.com/mysql/mysql-sys
        Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
    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)
        thread_cache_size (start at 4)
        table_open_cache (> 2000)
        performance_schema = ON enable PFS
        innodb_buffer_pool_size (>= 945M) 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)
    Some of these look straightforeward, but i don't want to mess with them till I'm sure it won't cause problems
    Nathan Platt
    Solidblueliquid LTD
    https://solblu.uk
    Shared Hosting/Reseller Hosting/VPS, North East UK Based

  2. #2
    Join Date
    Mar 2003
    Location
    /root
    Posts
    23,981
    Optimizing mysql will depend on what kind of sites you are running and the server specs you have.

    Might need to check it first.

    Specially 4 U
    Reseller Hosting: Boost Your Websites | Fully Managed KVM VPS: 3.20 - 5.00 Ghz, Pure Dedicated Power
    JoneSolutions.Com is on the net 24/7 providing stable and reliable web hosting solutions, server management and services since 2001
    Debian|Ubuntu|cPanel|DirectAdmin|Enhance|Webuzo|Acronis|Estela|BitNinja|Nginx

  3. #3
    Join Date
    Oct 2016
    Location
    England
    Posts
    175
    Its a shared hosting server, so every sort of site. The server specs are;

    Xeon E5-2620v4
    64 GBDDR4 Ram
    SSD Raid (single parity)
    Nathan Platt
    Solidblueliquid LTD
    https://solblu.uk
    Shared Hosting/Reseller Hosting/VPS, North East UK Based

  4. #4
    I'd first check exactly what MySQL is doing before you tune anything.

    It's possible you have some slow queries eating resources and verify if the current running queries are genuine requests and nothing that got stuck.

    Anything that has been running for more than a 30 seconds will be suspect, a server of that spec should process many hundred's of requests a second with ease.

  5. #5
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,681
    Can you post the entire output from mysqltuner?
    Steven Ciaburri | Industry's Best Server Management - Rack911.com
    Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
    Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
    FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance

  6. #6
    If anyone share a step by step checklist would be great.

Similar Threads

  1. MySQL Tuning
    By ripdepot in forum Systems Management Requests
    Replies: 2
    Last Post: 12-08-2007, 06:55 PM
  2. plesk 7.5 CP & mysql tuning problems?
    By carpman in forum Hosting Software and Control Panels
    Replies: 2
    Last Post: 08-24-2007, 08:30 AM
  3. MySQL tuning - HW or SW
    By MongoHosting in forum Hosting Security and Technology
    Replies: 3
    Last Post: 05-20-2004, 12:36 PM
  4. MySQL tuning, need expert suggestions
    By Jori in forum Hosting Security and Technology
    Replies: 9
    Last Post: 01-06-2004, 02:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •