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.

Need help mysql optimization (image hosting site)

Discussion in 'Workarounds and Optimization' started by zakrami, Feb 1, 2015.

  1. zakrami

    zakrami Member

    Joined:
    Nov 9, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have not much knowledge. So please if you need any information write down the process to get the infromation you want.

    I purchased a dedicated server - HP ProLiant DL120 G7/Intel Quad-Core Xeon E3-1230, and divided into two VPS. 1st VPS configuration is 8GB RAM, 500GB HDD and hosting a wordpress blog. 2nd VPS is 24GB RAM and 2.5TB HDD and hosting an image hosting site.

    Users are downloading image files via some software and opening image files through browser. Users are posting image sets on forum.

    This is my my.cnf file
    Code:
    [mysqld]
    skip-name-resolve
    sql-mode                        = NO_ENGINE_SUBSTITUTION
    #event-scheduler                = 1
    
    ## Cache
    thread-cache-size               = 16
    table-open-cache                = 4096
    table-definition-cache          = 2048
    query-cache-size                = 256M 
    query-cache-limit               = 512M
    
    ## Per-thread Buffers
    sort-buffer-size                = 8M
    read-buffer-size                = 1M
    read-rnd-buffer-size            = 8M
    join-buffer-size                = 1M
    
    ## Temp Tables
    tmp-table-size                  = 32M 
    max-heap-table-size             = 64M 
    
    ## Networking
    back-log                        = 100
    max-connections                 = 400
    max_user_connections            = 1000
    max-connect-errors              = 10000
    max-allowed-packet              = 32M
    interactive-timeout             = 5
    wait-timeout                    = 5
    
    ### Storage Engines
    #default-storage-engine         = InnoDB
    innodb                          = FORCE
    
    ## MyISAM
    key-buffer-size                 = 64M 
    myisam-sort-buffer-size         = 128M
    
    ## InnoDB
    #innodb-buffer-pool-size        = 128M
    #innodb-log-file-size           = 100M
    #innodb-log-buffer-size         = 8M
    #innodb-file-per-table          = 1
    #innodb-open-files              = 300
    innodb_buffer_pool_size         = 512M
    innodb_additional_mem_pool_size = 64M
    innodb_thread_concurrency       = 8
    innodb_flush_log_at_trx_commit  = 0
    innodb_log_buffer_size          = 8M
    
    [mysql]
    no-auto-rehash
    
    Problem part.
    It works fine 2-4 days. In working days there are 280 Task maximum when I use top command and memory usage approx 1.5-1.6 GB. But when problem arise tasks goes above to 350-430 and at that time memory usage 3.2-4.5 GB and at everything collapse. Visitors can't able to open the site and see the image.

    Maximum user online on the site approx 150 maximum as per GA (Google Analytic)

    I think there is different kind of configuration for image hosting site and file hosting site.

    So please need help to optimize.


    NOTE : If you need any other info please let me know so that I can provide.
     
  2. zakrami

    zakrami Member

    Joined:
    Nov 9, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Top command
    Code:
    top - 04:45:28 up 19:04,  1 user,  load average: 0.90, 1.01, 1.21
    Tasks: 176 total,   2 running, 173 sleeping,   0 stopped,   1 zombie
    Cpu(s):  2.4%us,  1.7%sy,  0.0%ni, 95.7%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
    Mem:  25165824k total,  1646632k used, 23519192k free,        0k buffers
    Swap:   524288k total,        4k used,   524284k free,        0k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     9385 nobody    18   0 66976 4176 1996 S  0.7  0.0   0:00.44 httpd
    11723 nobody    15   0 67152 4380 2008 S  0.7  0.0   0:00.29 httpd
    11968 nobody    15   0 67016 4188 1984 S  0.7  0.0   0:00.02 httpd
    32252 nobody    15   0 66984 4280 2008 S  0.7  0.0   0:00.73 httpd
     1984 mysql     16   0 3253m 373m 6636 S  0.3  1.5  24:24.32 mysqld
     3311 nobody    15   0 66976 4184 1996 S  0.3  0.0   0:00.30 httpd
     5151 nobody    15   0 67288 4560 2008 S  0.3  0.0   0:00.40 httpd
     6093 nobody    15   0 66976 4236 2008 S  0.3  0.0   0:00.39 httpd
     8004 nobody    15   0 66972 4212 2008 S  0.3  0.0   0:00.39 httpd
     9336 nobody    15   0 66980 4264 1996 S  0.3  0.0   0:00.24 httpd
     9337 nobody    15   0 67024 4228 1996 S  0.3  0.0   0:00.35 httpd
     9371 nobody    15   0 66976 4196 1996 S  0.3  0.0   0:00.30 httpd
     9653 nobody    15   0 66976 4216 2008 S  0.3  0.0   0:00.30 httpd
    11453 nobody    15   0 67152 4380 2008 S  0.3  0.0   0:00.14 httpd
    11831 nobody    15   0 66968 4164 1976 S  0.3  0.0   0:00.17 httpd
    11834 nobody    15   0 66968 4136 1964 S  0.3  0.0   0:00.09 httpd
    11836 nobody    15   0 67012 4164 1996 S  0.3  0.0   0:00.22 httpd
    11850 nobody    15   0 67296 4464 2008 S  0.3  0.0   0:00.06 httpd
    11949 nobody    15   0 66896 4084 2008 S  0.3  0.0   0:00.16 httpd
    11971 nobody    15   0 67012 4128 1964 S  0.3  0.0   0:00.11 httpd
    12042 nobody    15   0 67032 4228 2008 S  0.3  0.0   0:00.14 httpd
    13376 nobody    15   0 67016 4212 1984 S  0.3  0.0   0:00.05 httpd
    13381 nobody    15   0 67028 4228 2012 S  0.3  0.0   0:00.17 httpd
    14006 nobody    15   0 66968 4196 2008 S  0.3  0.0   0:00.04 httpd
    14056 nobody    15   0 67152 4156 1940 S  0.3  0.0   0:00.04 httpd
    14074 nobody    15   0 67016 4172 1968 S  0.3  0.0   0:00.02 httpd
    15809 nobody    15   0 67152 4180 1964 S  0.3  0.0   0:00.03 httpd
    15871 hosturim  16   0  199m  12m 7168 R  0.3  0.1   0:00.01 php
        1 root      15   0 19240 1440 1208 S  0.0  0.0   0:00.08 init
     1164 root      14  -4 10652  608  352 S  0.0  0.0   0:00.00 udevd
     1436 nobody    15   0 66980 4284 2012 S  0.0  0.0   0:00.66 httpd
     1491 root      18   0  179m 1628 1064 S  0.0  0.0   0:00.16 rsyslogd
     1510 named     18   0  679m  27m 2620 S  0.0  0.1   0:07.65 named
    
     
  3. zakrami

    zakrami Member

    Joined:
    Nov 9, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Code:
     >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.40-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 894K (Tables: 25)
    [--] Data in InnoDB tables: 117M (Tables: 134)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 23
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 21h 22m 41s (9M q [59.648 qps], 1M conn, TX: 5B, RX: 791M)
    [--] Reads / Writes: 56% / 44%
    [--] Total buffers: 936.0M global + 18.2M per thread (400 max threads)
    [OK] Maximum possible memory usage: 8.0G (33% of installed RAM)
    [OK] Slow queries: 0% (0/9M)
    [OK] Highest usage of available connections: 7% (28/400)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/416.0K
    [OK] Key buffer hit rate: 99.6% (38K cached / 142 reads)
    [OK] Query cache efficiency: 77.0% (4M cached / 5M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 53 sorts)
    [OK] Temporary tables created on disk: 13% (115 on disk / 825 total)
    [OK] Thread cache hit rate: 99% (56 created / 1M connections)
    [OK] Table cache hit rate: 94% (235 open / 250 opened)
    [OK] Open file limit used: 1% (105/8K)
    [OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
    [OK] InnoDB buffer pool / data size: 512.0M/117.8M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
     
  4. whmpress

    whmpress Registered

    Joined:
    Feb 2, 2015
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    It might not be mysql at all causing this. Have you considered apache optimizaiton so far?
     
  5. zakrami

    zakrami Member

    Joined:
    Nov 9, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    No. I didn't considered apache optimization so far.
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  7. zakrami

    zakrami Member

    Joined:
    Nov 9, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I will post the info when this happens again.

    Thanks for reply.
     
  8. zakrami

    zakrami Member

    Joined:
    Nov 9, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi error file is so huge. how can I see the content
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You will typically want to search the log for the time at which your website is inaccessible. EX:

    Code:
    grep "Fri Feb 06 08:58:07" /usr/local/apache/logs/error_log
    Thank you.
     
Loading...

Share This Page