Results 1 to 7 of 7

Thread: my.cnf

  1. #1

    Question 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.

  2. #2
    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

  3. #3
    Join Date
    Mar 2003
    Location
    /root
    Posts
    23,991
    Moved > Hosting Security and Technology.

  4. #4
    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)

  5. #5
    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?

  6. #6
    Quote Originally Posted by TMS - JoseQ View Post
    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.
    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.

  7. #7
    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

  1. mySQL config my.cnf on cPanel server
    By MattF in forum Hosting Security and Technology
    Replies: 2
    Last Post: 01-07-2003, 06:13 PM
  2. Can't Modify my.cnf on My Dedicated Server!
    By LancerForums in forum Hosting Security and Technology
    Replies: 3
    Last Post: 10-28-2002, 01:53 PM
  3. MySQL my.cnf file
    By clocker1996 in forum Hosting Security and Technology
    Replies: 1
    Last Post: 04-24-2002, 03:02 AM
  4. MySQL - How to setup my.cnf
    By Jason King in forum Hosting Security and Technology
    Replies: 3
    Last Post: 12-08-2001, 02:04 PM
  5. Optimize MySQL my.cnf
    By MattF in forum Hosting Security and Technology
    Replies: 1
    Last Post: 07-15-2001, 01:12 PM

Posting Permissions

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