VPS crashes sometimes due to many queries

[deonderd]

Hi Guys

 

I have some peformance issues that concerns me.

Sometimes my VPS crashes.. because the site is making to many queries

any suggestions why this is the case? and what i need to do to be sure it not happens again?

Do i need more resources on the vps? or are my settings wrong?

And also how can i speed up the sites for visiting customers?

 

 

my situation is as follows

 

1 prestashop multistore installation with 15 domains..using the same catalog but in different languages and countries

 

example site http://www.deonderdelenshop.com

the shop contains +/- 8000 products

 

 

VPS

4 CPUS 

mem 4Gb

HarDisk SSD -> 60 GB

VMWARE

CentOS 4/5/6 (64 bit)

Directadmin

 

in attachement U can find printscreens of VPS  and debugging on site

 

 

 

 

 

 

[tuk66]

Why do you think that your problem is caused by "too many queries"? In general, you can run some tool, Monit for example, to monitor all VPS services and restart them after crash.

[deonderd]

when it happens.. i need to restore the vps with a backup.. because i cannot login any more

the guy who does the maintenance on my vps told me the following

 

at the moment of  the crash there were to many access to MySQL database.. the logs only work to that moment

Problabby the vps cannot handle the piek moments.. but to be sure the vps need to be monitored

SQL is  4 GB big its possible that on piek moments the vps has a hard time..

 

 

 

the stats on the prestashop tells me that i have on all my sites together 47101 visitors yesterday.. i dont know if the stats in prestashop are correct.. i use google analytics to for every domain seperate.. but i read on seo forums that google analytics.;also not shows u everything

 

grtz

[tuk66]

Have you used MySQL slow query log?

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

 

Also look at http://dba.stackexchange.com/questions/43324/how-to-debug-a-db-memory-leak-causing-mysql-to-go-before-its-own-limits

[El Patron]

remember, vps is like shared...if someone takes over in partition, then all other's in same partition is also affected.

 

make sure to follow up on tuk66 recommendations.

 

also using phpmyadmin, you can select 'status' to view various mysql run time settings, will show red for out of balance values.

 

Question: can you not simply boot the vps to recover?   having to restore is as you know very scarey stuff, loss of data etc.

 

(I pm'ed another community member dh42 to take a look at your post)

[deonderd]

i tried to restart the vps.. but it had no effect at all 

so we did not see any other option then put a backup of the whole vps back in place.. i lost one day of data..

Edited July 7, 2014 by deonderd (see edit history)

[Dh42]

What version of Prestashop are you using and is caching turned on? The main query that is killing your shop is because of your block categories module, it seems to be throwing lots of queries out.  At the same time it looks like your carrier language has too many rows as well, how many carriers are you running in your shop?

[El Patron]

(note I do what dh42 say do)

[deonderd]

Beneed config info provided by prestashop What do u mean with carrier language?

There are 11 languages in the script..

Every domain is set to use 2 or three languages

for example  

belgian site (french, dutch, english)

Spanish site  (spanish, english)

Italian site (Italian, english)

 

 

Server information

Server information: Linux #1 SMP Tue Apr 23 19:29:00 UTC 2013 x86_64

Server software version: Apache/2

PHP version: 5.3.24

Memory limit: -1

Max execution time: 3000

Database information

MySQL version: 5.5.31

MySQL engine: InnoDB

Tables prefix: prstshp_

Store information

PrestaShop version: 1.5.6.2

Shop URL: http://deonderdelenshop.nl/

Current theme in use: default

Mail configuration

Mail method: You are using the PHP mail function.

Your information

Your web browser: Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36

 

Ps i will El Patron..

Listen to you ..  the pro's... to learn more myself

Edited July 7, 2014 by deonderd (see edit history)

[Dh42]

Taken from your screen shot, http://screencast.com/t/VT9iTq1bp  You can see there are 14025 rows in that table, something is wrong there should really not be that many rows there, but at the same time I am seeing something similar in other tables as well. 

[El Patron]

how often is this happening?  I can not tell is this happened once or is re-occurring.

[deonderd]

i'm not sure.. but when i check in phpadmin.. i noticed before.. that every shop in the multistore setting  .. has all the languages in the database.. 

 

the logical would be that u have all the languages.. and that every shop uses from those the once they need.. 

but instead if that .. every shop in multistore has all the languages

 

in the settings not every store can access all the languages in the shop itself.. 

i asked myself before .. what the use of it is.. tohave all languages for every shop

 

i exported the tabel prstshp_carrier_lang

u can find it here

https://www.dropbox.com/s/nbel3bhob8s2h0c/deonderd_belgium.xml

 

 

[deonderd]

how often is this happening?  I can not tell is this happened once or is re-occurring.

 

this is second time it happens.

 

At the moment i'm not advertising or taking any effort of getting more customers.

Also every day. some products get updated with pics.. lots of them now has no pics..

 

I need to be sure.. my shop and vps is running the way it should be.. before i take effort in increasing sales

 

[El Patron]

when this happens again, you need to turn on ps error report, and then try to access, because if you are having to restore (assuming blank page), knowing that underlying error message is worth it's weight in gold.

 

having to restore, makes me think there is a 'real' error that has not yet been revealed.

[deonderd]

if this happens i cannot login any more also not on DA or ftp..

I needed to ask my hosting company .. to place back the backup from the whole vps including centos and DA etc

[El Patron]

if this happens i cannot login any more also not on DA or ftp..sounds to me like someone needs a new hosting environment...and you say that you or your hosting company can not even boot the vps to recover...your probably have loads of backed up querys but something in your server tipped over.  debugging ps may be like shaving a pig, lot squealing for very little wool.

 

no matter what ps 'might' have done, you should be have ftp (not sure what DA is), and if not then a simple server boot should suffice.

 

I'd look for alternative to this server...because they must be restoring entire vps space not just ps

 

servers are a commodity, vps is as said, shared.....ave maria pues

 

personally I am on my last 'anything' not cloud.  Being able to have performance increase as load increases, is the cat's meow.

[deonderd]

DA = Direct admin

and yes we can reboot... hostingcompany can reboot to..but it did not solve the problem after crash..

Edited July 7, 2014 by deonderd (see edit history)

[El Patron]

DA = Direct admin

and yes we can reboot... hostingcompany can reboot to..but it did not solve the problem after crash..

 

hum, that is not how I understood early post, what do you mean did not solve the problem after you booted?

 

blank page?  or just ran out of gas again?

[deonderd]

just no access to the site.. ok ..lets call it a blank page

[deonderd]

again de websiteis down

Link to database cannot be established: SQLSTATE[08004] [1040] Too many connections

 

Now.. i cannot login anymore on the site

I cannot login in my direct admin panel..

 

so whole vps is infected with this issue 

Edited July 8, 2014 by deonderd (see edit history)

[El Patron]

again de websiteis down

Link to database cannot be established: SQLSTATE[08004] [1040] Too many connections

 

Now.. i cannot login anymore on the site

I cannot login in my direct admin panel..

 

so whole vps is infected with this issue 

 

ave maria pues (omg in ColOmbia)

 

I would look at my access log and see if getting a dos attack.  If so then you could block them.

 

But to me it smells of stalled vps, that is not servicing your requests so that it finally tips over.  Also look in the apache error log.

 

Sorry you having this issue, fingers crossed.

[Dh42]

The site(s) need to have either Prestashop optimized or it needs to be moved to a bigger hosting account. The issue will keep happening because traffic will increase and it will happen to a point that google will notice and reduce your rank in the search engine because your site will not be reliable.

[deonderd]

last general system messages in Direct admin

000000260 Warning: The system load average is 35.51 07/08/2014 000000259 Warning: The system load average is 22.02 07/08/2014 000000258 Warning: The system load average is 11.43 07/07/2014 000000256 The service 'mysqld' on server totalsupport.sohosted-vps.nl is currently down 07/05/2014 000000255 The service 'httpd' on server totalsupport.sohosted-vps.nl is currently down 07/05/2014

 

 

Var/log/httpd/error_log

 

[Wed Jul 09 00:12:00 2014] [warn] child process 26373 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:00 2014] [warn] child process 26420 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:00 2014] [warn] child process 26421 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:00 2014] [warn] child process 26450 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:00 2014] [warn] child process 26452 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:00 2014] [warn] child process 26466 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 28896 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25323 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25325 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25406 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25427 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25446 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 29084 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25507 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25606 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25790 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25906 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25942 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 25989 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 26013 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 27542 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 26373 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 26420 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 26452 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:01 2014] [warn] child process 26466 still did not exit, sending a SIGTERM

[Wed Jul 09 00:12:02 2014] [error] child process 28896 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 25325 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 25427 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 25446 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 29084 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 25790 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 26013 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 27542 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 26373 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:02 2014] [error] child process 26452 still did not exit, sending a SIGKILL

[Wed Jul 09 00:12:03 2014] [notice] SIGHUP received.  Attempting to restart

[Wed Jul 09 00:12:07 2014] [notice] SSL FIPS mode disabled

[Wed Jul 09 00:12:07 2014] [warn] RSA server certificate CommonName (CN) `localhost' does NOT match server name!?

[Wed Jul 09 00:12:07 2014] [notice] Apache/2.2.24 (Unix) mod_ssl/2.2.24 OpenSSL/1.0.0-fips DAV/2 configured -- resuming normal operations

[Wed Jul 09 00:12:16 2014] [notice] caught SIGTERM, shutting down

[Wed Jul 09 00:12:31 2014] [notice] SSL FIPS mode disabled

[Wed Jul 09 00:12:31 2014] [warn] RSA server certificate CommonName (CN) `localhost' does NOT match server name!?

[Wed Jul 09 00:12:31 2014] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)

[Wed Jul 09 00:12:32 2014] [notice] SSL FIPS mode disabled

[Wed Jul 09 00:12:32 2014] [warn] RSA server certificate CommonName (CN) `localhost' does NOT match server name!?

[Wed Jul 09 00:12:32 2014] [notice] Apache/2.2.24 (Unix) mod_ssl/2.2.24 OpenSSL/1.0.0-fips DAV/2 configured -- resuming normal operations

[Wed Jul 09 00:24:16 2014] [error] [client 66.249.64.154] File does not exist: /var/www/html/robots.txt

[Wed Jul 09 00:24:16 2014] [error] [client 66.249.64.154] File does not exist: /var/www/html/404.shtml

[Wed Jul 09 00:24:16 2014] [error] [client 66.249.64.149] File does not exist: /var/www/html/published

[Wed Jul 09 00:24:16 2014] [error] [client 66.249.64.149] File does not exist: /var/www/html/404.shtml

 

Cron log  /var/log/cron

 

Jul  9 00:10:08 totalsupport CROND[30262]: (root) CMD (/usr/local/maldetect/maldet --mkpubpaths >> /dev/null 2>&1)

Jul  9 00:11:15 totalsupport CROND[30482]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:12:31 totalsupport crond[1875]: (CRON) STARTUP (1.4.4)

Jul  9 00:12:31 totalsupport crond[1875]: (CRON) INFO (running with inotify support)

Jul  9 00:13:01 totalsupport CROND[2042]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:14:01 totalsupport CROND[2254]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:15:01 totalsupport CROND[2441]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:16:01 totalsupport CROND[2686]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:17:01 totalsupport CROND[2839]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:18:01 totalsupport CROND[2930]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:19:01 totalsupport CROND[3055]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:20:01 totalsupport CROND[3135]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:20:01 totalsupport CROND[3136]: (root) CMD (/usr/local/maldetect/maldet --mkpubpaths >> /dev/null 2>&1)

Jul  9 00:21:01 totalsupport CROND[3253]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:22:01 totalsupport CROND[3297]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:23:01 totalsupport CROND[3366]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:24:01 totalsupport CROND[3418]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:25:01 totalsupport CROND[3508]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:26:01 totalsupport CROND[3558]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:27:01 totalsupport CROND[3619]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:28:01 totalsupport CROND[3730]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:29:02 totalsupport CROND[3795]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:30:01 totalsupport CROND[3902]: (root) CMD (/usr/local/maldetect/maldet --mkpubpaths >> /dev/null 2>&1)

Jul  9 00:30:01 totalsupport CROND[3903]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:31:01 totalsupport CROND[4028]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:32:01 totalsupport CROND[4124]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:33:01 totalsupport CROND[4200]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:34:01 totalsupport CROND[5012]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:35:01 totalsupport CROND[5070]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:36:01 totalsupport CROND[5111]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:37:01 totalsupport CROND[5172]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:38:01 totalsupport CROND[5243]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:39:01 totalsupport CROND[5296]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:40:01 totalsupport CROND[5371]: (root) CMD (/usr/local/maldetect/maldet --mkpubpaths >> /dev/null 2>&1)

Jul  9 00:40:01 totalsupport CROND[5375]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:41:01 totalsupport CROND[5461]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:42:01 totalsupport CROND[5899]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:43:01 totalsupport CROND[5982]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:44:01 totalsupport CROND[6019]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:45:01 totalsupport CROND[6084]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:45:01 totalsupport CROND[6085]: (root) CMD (/usr/local/sbin/spri -q >> /dev/null 2>&1)

Jul  9 00:46:01 totalsupport CROND[6429]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:47:01 totalsupport CROND[6502]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:48:01 totalsupport CROND[6566]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:49:01 totalsupport CROND[6628]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:50:01 totalsupport CROND[6700]: (root) CMD (/usr/local/maldetect/maldet --mkpubpaths >> /dev/null 2>&1)

Jul  9 00:50:01 totalsupport CROND[6705]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:51:01 totalsupport CROND[6783]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:52:01 totalsupport CROND[6844]: (root) CMD (/usr/local/directadmin/dataskq)

Jul  9 00:53:01 totalsupport CROND[6914]: (root) CMD (/usr/local/directadmin/dataskq)

[deonderd]

The site(s) need to have either Prestashop optimized or it needs to be moved to a bigger hosting account. The issue will keep happening because traffic will increase and it will happen to a point that google will notice and reduce your rank in the search engine because your site will not be reliable.

 

 

Hi

 

i dont mind taking bigger hosting if this is the reason of the crashes

But thats what i 'm not sure about.. i'm using around 30 % of resources.. in vsphere the harddisk is in red;.but in reality i'm using around 8 gb (seen in directadmin)

 

Prestashop optimized? do U mean the sql? 

 

grtz

[deonderd]

update

 

 

we increased the setting max_connections on the server

 

some more info

 

http://deonderdelenshop.nl/info.php

[Dh42]

Increasing the connections will help, but not really. The database was refusing connections because they were over the connection limit, but at the same time it was being knocked offline as well. 

 

As for the optimizing, I do a lot of optimizing Prestashop so that it loads faster and it takes less server resources. That way people have a faster load time on the site while at the same time it reduces the stress on the server.

[El Patron]

from above log

 

The service 'mysqld' on server totalsupport.sohosted-vps.nl is currently down

 

I would look at my mysql error log, if not enabled enable it for future info.

 

look around here: /var/lib/mysql

[deonderd]

FYI

 

i upgraded my hosting package to 8gb ram and 100 gb HD

site seems to run good now

 

during the crash there were some peek moments on disk and ram.. 

but i dont know where they are comming from..

 

 

Do U know anyone with multistore setting and more then 4 languages.. 

 

i need to know if the lang query that Dh42 is mentioning is normal or not in my kind of setup

 

 

thnx mark