Pressed0024 Posted December 20, 2013 Share Posted December 20, 2013 Everyone knows Prestashop speeds a lot of time doing mysql queries. There are way too many SELECT done by prestashop. Assuming we have done CDN, full page caching, and CCC compile. What else can we do to speed up the site during mysql database queries? How can we reduce the queries? How to better cache them? Link to comment Share on other sites More sharing options...
bellini13 Posted December 20, 2013 Share Posted December 20, 2013 you need to determine which queries are slowest by trapping them use the slow query log in mysql. Once you have identified them, you would then need to determine how best to alter them. This is not a trivial task.. Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 Just like Bellini said, it is not trivial, it is going to be hard as hell actually. There is already so much time put into making the queries efficient. Have you ran a sql tuning script on your server? Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 Just like Bellini said, it is not trivial, it is going to be hard as hell actually. There is already so much time put into making the queries efficient. Have you ran a sql tuning script on your server? No, how should sql tuning script work? Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 Check this out, https://github.com/major/MySQLTuner-perl you are going to need root access to your server environment. Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 (edited) Hmm, looks like i dont have root access Edited December 20, 2013 by Pressed0024 (see edit history) Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 Then you pretty much either have to change the queries by hand or be happy with what you are currently getting. Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 Apologize if my previous statement was kinda rude. Was typing in a hurry. I was actually hoping that someone have identified some codes changes to default prestashop which is universal to all stores that makes a huge impact on the query time and further optimized the default presta. Also, I'm allocated 2 cores for my hosting plan. Would getting more cores vs high frequency cpu help in the mysql queries? I have a lot of SELECT queries for some reasons. Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 Who is your host and what kind of plan do you have, are you on a VPS? If there was a more efficient query, it would more than likely be included in Prestashop.. Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 Stablehost enterprise hosting. Its considered Shared but offers 100% of 2 cores shared. In other words, its not VPS and I dont have root. Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 Let's say I already have full page cache caching the html page, APC doing the 2 segment 64mb opcode caching and mysql query caching 512mb of queries. What is there actually left for the server to do that is taking that 1-2 sec for html to load on my browser? Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 You need to move to a vps, which enterprise plan do you have? They are pretty much lying to you, or selling you a low quality machine. Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 Link me to your site, you can pm me with it if you want. I can tell you what this issue is by first look. Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 EN-BASIC Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 They are pretty much lying to you or selling you a machine that has a slow processor in it for that price. Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 That's why i asked if more cores or high frequency is better. Their CPU speed: https://forums.stablehost.com/index.php?threads/what-cpu-resources-are-we-actually-getting-in-enterprise-plan.184/ Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 Ok, maybe they can with that server since they only cost around $50 a month for the whole server. A newer processor would be better, like an e-1230 or something like that. Same number of cores but they run faster and are on a newer die. Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 I think I might as well give up on squeezing that extra bit of juice from the html first page load and focus my efforts on the js part which takes another 1 sec to render page. If right parts of the js is delayed, we can make the page feel faster: http://www.prestashop.com/forums/topic/292821-how-to-forcing-js-to-load-the-last/ At least this is more feasible. Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 What is your total page load time and what tool are you using to test the site? Link to comment Share on other sites More sharing options...
Pressed0024 Posted December 20, 2013 Author Share Posted December 20, 2013 Total page load time isn't my concern here. It can take up to 30 sec to fully load all DOMs including external ones, but this is meaningless since user wont really get to see this. I'm averaging 2-3 sec (using physical stop watch) page above the fold page render time. ~700ms spent on the initial HTML and another 800-900ms for the theme's js (including download time from maxcdn using testing tools). The rest probably makes up for my reaction delays pressing the stop watch. My target here is to bring initial HTML down to 200ms and theme's js to 200ms. I use Chrome dev tool, pingdom, webpagetest, gtmetrix. Whats more important for me is webpagetest's page render video which gives a better feel of what visitor experience. Seeing how difficult it is to squeeze that extra juice for initial HTML, i might as well look at delaying some js to footer to reduce download time (~100+kb). Link to comment Share on other sites More sharing options...
PrestaHeroes USA Posted December 20, 2013 Share Posted December 20, 2013 many do not look at their mysql runtime stats, for modern phpmyadmin it is easy to view including 'red' highlights for mysql configurations that can be tuned. http://screencast.com/t/wrr9ri8zTO 1 Link to comment Share on other sites More sharing options...
Dh42 Posted December 20, 2013 Share Posted December 20, 2013 Your site would benefit from a server with better processing power with timings like that. Link to comment Share on other sites More sharing options...
Recommended Posts