Hello all, first of all I would like to acknowledge this isn't really anything to do with WHM or cPanel and it's regarding the my.conf file. Recently I tried making a backup of my sql database to find the download very slow and making a error on my site indicating too many sql connections.
My site is a phpbb forum and is on a dedicated server with the following spec:
Intel Xeon E3110, 3.0GHz Dual-Core
RAM: 8GB
HDD: 1 x 500GB SATA HDDs
Operating System: Linux - CentOS 6.0 (64bit)
(there's only one web site being hosted on the server which is a phpbb forum)
I downloaded the my.conf file to find it's contents very basic (2 lines basic):
[mysqld]
innodb_file_per_table=1
This leads me onto configuring the file and my knowledge in this area is next to none! I've had a look at example configurations and I understand the settings to add are really based on the servers capability.
I know this is a bit cheeky, but is there any out there that might be able to point me towards an idiot proof guide or assist with a configuration?
Also, do the edit have to be made through SSH or can I adjust via sftp and notepad++ and then restart mysql through WHM?
Really appreciate any help with this, I know there are a few similar threads of this subject around the forum looking for help but I'm hoping someone will take pitty on me!
Thanks for any help! :D
---Edit, please find added in a mystl tuner report:
My site is a phpbb forum and is on a dedicated server with the following spec:
Intel Xeon E3110, 3.0GHz Dual-Core
RAM: 8GB
HDD: 1 x 500GB SATA HDDs
Operating System: Linux - CentOS 6.0 (64bit)
(there's only one web site being hosted on the server which is a phpbb forum)
I downloaded the my.conf file to find it's contents very basic (2 lines basic):
[mysqld]
innodb_file_per_table=1
This leads me onto configuring the file and my knowledge in this area is next to none! I've had a look at example configurations and I understand the settings to add are really based on the servers capability.
I know this is a bit cheeky, but is there any out there that might be able to point me towards an idiot proof guide or assist with a configuration?
Also, do the edit have to be made through SSH or can I adjust via sftp and notepad++ and then restart mysql through WHM?
Really appreciate any help with this, I know there are a few similar threads of this subject around the forum looking for help but I'm hoping someone will take pitty on me!
Thanks for any help! :D
---Edit, please find added in a mystl tuner report:
Code:
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 329M (Tables: 218)
[--] Data in InnoDB tables: 3M (Tables: 91)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 124K (Tables: 1)
[!!] Total fragmented tables: 17
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 24d 6h 57m 31s (65M q [31.099 qps], 3M conn, TX: 70B, RX: 20B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (7% of installed RAM)
[OK] Slow queries: 0% (16/65M)
[!!] Highest connection usage: 100% (152/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/401.2M
[OK] Key buffer hit rate: 99.9% (5B cached / 4M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts)
[OK] Temporary tables created on disk: 17% (444K on disk / 2M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 4% (400 open / 9K opened)
[OK] Open file limit used: 54% (556/1K)
[OK] Table locks acquired immediately: 99% (53M immediate / 53M locks)
[OK] InnoDB data size / buffer pool: 3.1M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 400)
Last edited:
