Results 1 to 6 of 6
Thread: mysql Tuning
-
10-20-2017, 06:27 AM #1Junior Guru
- 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)
Nathan Platt
Solidblueliquid LTD
https://solblu.uk
Shared Hosting/Reseller Hosting/VPS, North East UK Based
-
10-20-2017, 06:40 AM #2The Linux Specialist
- 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
-
10-20-2017, 06:42 AM #3Junior Guru
- 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
-
10-20-2017, 08:50 AM #4Newbie
- Join Date
- Oct 2017
- Posts
- 12
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.
-
10-21-2017, 05:33 PM #5Problem Solver
- 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
-
10-22-2017, 12:16 AM #6Newbie
- Join Date
- May 2012
- Posts
- 19
If anyone share a step by step checklist would be great.
Similar Threads
-
MySQL Tuning
By ripdepot in forum Systems Management RequestsReplies: 2Last Post: 12-08-2007, 06:55 PM -
plesk 7.5 CP & mysql tuning problems?
By carpman in forum Hosting Software and Control PanelsReplies: 2Last Post: 08-24-2007, 08:30 AM -
MySQL tuning - HW or SW
By MongoHosting in forum Hosting Security and TechnologyReplies: 3Last Post: 05-20-2004, 12:36 PM -
MySQL tuning, need expert suggestions
By Jori in forum Hosting Security and TechnologyReplies: 9Last Post: 01-06-2004, 02:09 AM