The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Database Configuration For Image Hosting Site

Discussion in 'Workarounds and Optimization' started by Binay Kumar, Aug 25, 2016.

Tags:
  1. Binay Kumar

    Binay Kumar Registered

    Joined:
    Aug 25, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hi

    I have a dedicated server Intel E3-1241v3, Software(mdadm) RAID, CentOS 6 64bit, 8GB RAM.

    I am running an image hosting script (Zamfi PHP Image Hosting Script) on this server and two Wordpress blog. On Image hosting site there are max 100 users at a time currently and on wordpress blog there are 20-30 user are online max.

    Some time mysql load average goes up-to 6.

    Here is my.cnf file details. I think that I am missing something in my.cnf File.
    Code:
    [mysqld]
    performance-schema=0
    local-infile=0
    
    innodb_ft_min_token_size=2
    ft_min_word_len=2
    
    #innodb_force_recovery = 2
    
    #innodb_flush_log_at_trx_commit=0
    
    max_connections=3000
    max_user_connections = 300
    datadir=/var/lib/mysql
    socket  = /var/lib/mysql/mysql.sock
    
    key_buffer_size = 1024M
    
    #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
    innodb_log_file_size=250M
    innodb_log_buffer_size=16M
    #slow_query_log=1
    #long_query_time=0.5
    
    open_files_limit=50000
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 6M
    tmp_table_size=128M
    read_rnd_buffer_size=4M
    max_heap_table_size=64M
    myisam_sort_buffer_size=64M
    thread_cache_size=8
    query_cache_limit=12M
    query_cache_size=256M
    thread_concurrency = 8
    #log-slow-queries = /var/log/mysqlslowqueries.log
    max_allowed_packet=268435456
    
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    innodb_buffer_pool_size = 2048M
    [isamchk]
    key_buffer =64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysqld]
    interactive_timeout=60
    wait_timeout=60
    connect_timeout=60
    
    [mysqlhotcopy]
    interactive-timeout
    [mysqld_safe]
    
    log-error=/var/log/mysqld.log
    
    As you can see that there is an entry - #innodb_flush_log_at_trx_commit=0

    When I remove # from beginning everything is under control. But I read most of the places it cause some errors in future or something else. As I am not a professional so I just put # at beginning.
     
  2. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello :),

    For the mysql optimization I will suggest you please try with MySQLTuner-perl by major scripts. Please try this scripts when your mysql server uptime is more than 24 hours
     
  3. Binay Kumar

    Binay Kumar Registered

    Joined:
    Aug 25, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    India
    cPanel Access Level:
    Root Administrator
    I already mentioned that I am not an expert.

    Can any one tell me what happened if I enable this entry in my.cnf file - #innodb_flush_log_at_trx_commit=0

    I tried to do this. I created a folder tool in root and I uploaded both files from centos_whm to tool. After that I run this command
    chmod +x mysqlmymonlite.sh and after this
    ./mysqlmymonlite.sh run 2>/dev/null > mysqlmymonlite_stats.txt and finally run this command
    ./mysqlmymonlite.sh mysqltuner

    It generates 0 byte file.

    When I run only this command -
    ./mysqlmymonlite.sh

    It gives below error message
    awk: cmd. line:1: fatal: cannot open file `/etc/init.d/nginx' for reading (No such file or directory)
    awk: cmd. line:1: fatal: cannot open file `/etc/init.d/nginx' for reading (No such file or directory)
    awk: cmd. line:1: fatal: cannot open file `/etc/init.d/nginx' for reading (No such file or directory)

    Is there is something goes wrong. If it how can I fix this.

    Thanks and waiting for your reply.
     
    #4 Binay Kumar, Aug 27, 2016
    Last edited by a moderator: Aug 27, 2016
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page