Need help mysql optimization (image hosting site)

[zakrami]

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

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

 

[zakrami]

Top command

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

 

[zakrami]

 >>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
 >>  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

 

[whmpress]

It might not be mysql at all causing this. Have you considered apache optimizaiton so far?

 

[zakrami]

No. I didn't considered apache optimization so far.

 

[cPanelMichael]

Hello

Do you notice any error messages in /usr/local/apache/logs/error_log when this happens?

Thank you.

 

[zakrami]

I will post the info when this happens again.

Thanks for reply.

 

[zakrami]

Hi error file is so huge. how can I see the content

 

[cPanelMichael]

You will typically want to search the log for the time at which your website is inaccessible. EX:

grep "Fri Feb 06 08:58:07" /usr/local/apache/logs/error_log

Thank you.