Need help mysql optimization (image hosting site)

zakrami

February 01, 2015 04:19

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.

Comments

8 comments

zakrami

February 01, 2015 09:47
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
0
zakrami

February 02, 2015 12:05
>> MySQLTuner 1.4.0 - Major Hayden >> 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
0
whmpress

February 02, 2015 13:06
It might not be mysql at all causing this. Have you considered apache optimizaiton so far?
0
zakrami

February 02, 2015 14:26
No. I didn't considered apache optimization so far.
0
cPanelMichael

February 02, 2015 15:16
Hello :) Do you notice any error messages in /usr/local/apache/logs/error_log when this happens? Thank you.
0
zakrami

February 02, 2015 16:22
I will post the info when this happens again. Thanks for reply.
0
zakrami

February 05, 2015 03:36
Hi error file is so huge. how can I see the content
0
cPanelMichael

February 06, 2015 16:35
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.
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?