deonderd Posted July 7, 2014 Share Posted July 7, 2014 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 Link to comment Share on other sites More sharing options...
tuk66 Posted July 7, 2014 Share Posted July 7, 2014 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. Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 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 Link to comment Share on other sites More sharing options...
tuk66 Posted July 7, 2014 Share Posted July 7, 2014 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 Link to comment Share on other sites More sharing options...
El Patron Posted July 7, 2014 Share Posted July 7, 2014 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) Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 (edited) 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) Link to comment Share on other sites More sharing options...
Dh42 Posted July 7, 2014 Share Posted July 7, 2014 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? Link to comment Share on other sites More sharing options...
El Patron Posted July 7, 2014 Share Posted July 7, 2014 (note I do what dh42 say do) Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 (edited) 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) Link to comment Share on other sites More sharing options...
Dh42 Posted July 7, 2014 Share Posted July 7, 2014 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. Link to comment Share on other sites More sharing options...
El Patron Posted July 7, 2014 Share Posted July 7, 2014 how often is this happening? I can not tell is this happened once or is re-occurring. Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 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 Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 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 Link to comment Share on other sites More sharing options...
El Patron Posted July 7, 2014 Share Posted July 7, 2014 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. Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 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 Link to comment Share on other sites More sharing options...
El Patron Posted July 7, 2014 Share Posted July 7, 2014 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. Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 (edited) 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) Link to comment Share on other sites More sharing options...
El Patron Posted July 7, 2014 Share Posted July 7, 2014 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? Link to comment Share on other sites More sharing options...
deonderd Posted July 7, 2014 Author Share Posted July 7, 2014 just no access to the site.. ok ..lets call it a blank page Link to comment Share on other sites More sharing options...
deonderd Posted July 8, 2014 Author Share Posted July 8, 2014 (edited) 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) Link to comment Share on other sites More sharing options...
El Patron Posted July 8, 2014 Share Posted July 8, 2014 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. Link to comment Share on other sites More sharing options...
Dh42 Posted July 8, 2014 Share Posted July 8, 2014 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. 1 Link to comment Share on other sites More sharing options...
deonderd Posted July 8, 2014 Author Share Posted July 8, 2014 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) Link to comment Share on other sites More sharing options...
deonderd Posted July 8, 2014 Author Share Posted July 8, 2014 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 Link to comment Share on other sites More sharing options...
deonderd Posted July 8, 2014 Author Share Posted July 8, 2014 update we increased the setting max_connections on the server some more info http://deonderdelenshop.nl/info.php Link to comment Share on other sites More sharing options...
Dh42 Posted July 8, 2014 Share Posted July 8, 2014 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. Link to comment Share on other sites More sharing options...
El Patron Posted July 8, 2014 Share Posted July 8, 2014 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 Link to comment Share on other sites More sharing options...
deonderd Posted July 9, 2014 Author Share Posted July 9, 2014 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now