Results 1 to 7 of 7
Thread: my.cnf
-
08-31-2018, 06:11 PM #1New Member
- Join Date
- Aug 2018
- Posts
- 2
my.cnf
Dear folks,
Hope my post finds you well....
Actually Im running I7 Dedicated server with 32GB RAMs.
Im facing lots of spikes and server load....
Here is my.cnf
Kindly advice:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#ignore_builtin_innodb
#default_storage_engine=MyISAM
key_buffer_size = 64M
#
query_cache_limit =2M
query_cache_size = 256M
query_cache_type = 1
thread_concurrency = 8
thread_cache = 8
thread_stack = 192k
max_allowed_packet=32M
wait_timeout = 14400
delayed_insert_timeout=20
interactive_timeout = 200
net_read_timeout=300
net_write_timeout=300
max_connect_errors = 100000
tmp_table_size = 256M
max_heap_table_size = 256M
tmpdir=/dev/shm
table_open_cache= 50000
max_connections = 250
skip-grant-tables
skip-name-resolve
bind-address = 127.0.0.1
long_query_time = 40
#max_seeks_for_key = 9000
join_buffer_size = 64M
read_buffer_size = 2048K
sort_buffer_size = 64M
read_rnd_buffer_size = 2048k
slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow
log_queries_not_using_indexes
####INNODB
thread_handling = pool-of-threads
thread_pool_size = 32
innodb_buffer_pool_size = 26G
innodb_log_file_size = 2G
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_instances = 10
#innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=300
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Thanks and regards.
-
08-31-2018, 07:25 PM #2Web Hosting Master
- Join Date
- Feb 2003
- Location
- Dallas, TX
- Posts
- 1,498
Which i7 processor? Is it recent
The max_connections may be low and may be causing a bottleneck, but we need more info. For example:
- How many concurrent visitors?
- What type of site?
- Why do you think MySQL is the problem?
- How are the "spikes" manifesting? Slow responses? High loads? Bandwidth?
That should help diagnose.TailorMadeServers.Com - Dallas Dedicated Servers since 2003
joseq@tailormadeservers.com | Skype (TailorMadeServers) | http://twitter.com/tailoredservers
-
08-31-2018, 08:44 PM #3The Linux Specialist
- Join Date
- Mar 2003
- Location
- /root
- Posts
- 23,991
Moved > Hosting Security and Technology.
-
08-31-2018, 08:47 PM #4Web Hosting Master
- Join Date
- Oct 2012
- Location
- Canada
- Posts
- 3,064
Is this I7 dedicated for one niche type of service? or just shared hosting customers??
█ TrentaHost INC. || Fully Managed DDoS Protected Services Globally (NA - EU - Asia)
█ Reseller Hosting- Pure SSD | Litespeed | Imunify360 | CloudLinux | 24x7 Support | Mailchannels
█ Linux & Windows DDoS Protected SSD VPS - cPanel / WHM | DDoS Protection | Let's Encrypt | Pure-SSD
█ DDoS Protected Locations : Portland, OR (North America) | Amsterdam, NL (Europe) | Singapore (Asia)
-
08-31-2018, 11:01 PM #5Web Hosting Master
- Join Date
- Mar 2007
- Posts
- 2,050
First step should be checking if the slow log is empty, if it is not empty you should resolve all issues listed there. Any queries being run should be very quickly if they are initiated by website visitors. Anything longer than a second should be run as a batch process against a slave database and cached.
Are you using redis to store and serve hot data to users instead of hitting the database all the time?
Are you caching pages or elements that have been queried by users?
Do you have enough ram to store your entire database set in RAM?
What is the read/write throughput to the database?
What type of disks are you using (SSDs, HDDs, etc.)?
What else is running on the server?
Have you thought about moving your database to another server or servers so you can read from a slave database and write to the master database?
What type of database tables are you using?
Are you using indexes on your database?
How fast are your queries?
Have you run the queries manually to see how fast they run?
Are the same queries being run over and over cached by the database server?
-
09-01-2018, 02:16 AM #6New Member
- Join Date
- Aug 2018
- Posts
- 2
Thanks for kind reply....
1: According to google anayltics, we have from 100 to 200 vistors @ one time, The load spikes starts when they start to be above 140 vistors.
2: Its mybb forum (1.6.18)
3: If not mysql so what could be?
4: Spikes are high load for few secs or few mins, dependes, thus freezing, the number of online members drops down, again server become happy.
@ HelpOps
Its 32 GB RAM, max Usage of RAM is 50%
Using SSD's
For all others questions, I think its easier if you kindly have a team viewer session to check all these....
Best regards.Last edited by rabihhammoud; 09-01-2018 at 02:21 AM.
-
09-02-2018, 09:44 PM #7Junior Guru
- Join Date
- Mar 2017
- Posts
- 191
MySQL may not be the only bottleneck. Firstly, switch to MariaDB 10.
Are you utilizing any caching? Definitely look at file caching and database caching with Memcached or Redis.
You can also try CloudFlare or another CDN.
Finally, switch to Nginx if you haven't already. It's a big improvement over Apache.HostaPolis : Awesome & Affordable hosting plans with 24/7/365 Support
Similar Threads
-
mySQL config my.cnf on cPanel server
By MattF in forum Hosting Security and TechnologyReplies: 2Last Post: 01-07-2003, 06:13 PM -
Can't Modify my.cnf on My Dedicated Server!
By LancerForums in forum Hosting Security and TechnologyReplies: 3Last Post: 10-28-2002, 01:53 PM -
MySQL my.cnf file
By clocker1996 in forum Hosting Security and TechnologyReplies: 1Last Post: 04-24-2002, 03:02 AM -
MySQL - How to setup my.cnf
By Jason King in forum Hosting Security and TechnologyReplies: 3Last Post: 12-08-2001, 02:04 PM -
Optimize MySQL my.cnf
By MattF in forum Hosting Security and TechnologyReplies: 1Last Post: 07-15-2001, 01:12 PM