Load product data from external database?

[bibob]

Hi

 

Currently i have a large shop with about 100.000 products, and arround 5mil images.

i have optimized the theme to load faster, using sprites and other optimizations.

 

the problem is the load time of the webshop is slow becouse of the large database. here is output of a test attached to this post.  as you see the "Time to First Byte" taking loong time, probally becouse of the large database.

 

Is there a way to optimize that by loading data from external place? such images and product data, is there some way someone could reccomend?

 

Look forward to hear from you

 

I'm using PS 1.5.6.1 

Edited January 6, 2014 by bibob (see edit history)

[bibob]

i just found some article about images http://blog.dh42.com/prestashop-and-media-servers/

maybe i can do these steps for loading images?   should it be on a static ip address? so it will have no effect to use same server ip address?

 

still waiting for another solution to optimize this large database for product descriptions and data

[bellini13]

is it just your home page that you speed tested?  You have a lot of products, but you do not mention how many categories you have.  I have seen a number of posts lately of people complaining about speed, and the common factor between them all are the number of products and categories.

 

Issues range from poorly configured mysql databases (not PS related), poorly written modules provided with a theme, and missing indexes on various tables.

 

I would first suggest you enable the mysql slow query log (assuming you have a vps/dedicated server with privileges) and see if there are any queries executed that exceed 1 second. 

 

I would also download the Tuning Primer Script located at the following URL and execute it on the server command line.  You would execute this after running your mysql server for more than 48 hours with normal site usage.  The script will look at several runtime parameters and make suggestions on how to better tune your mysql database

http://www.day32.com/MySQL/

 

After that, I would then start reading up on how to debug Prestashop to determine where all the time is being spent, and see if those components either have bugs that need to be fixed, if they can be disabled completely, or rewritten to handle such a large volume of data.

[bibob]

Hi

 

thaaanks alot, after installing Tuning Primer Script on the server now it takes

 First View

(3.544s)

https://dl.dropboxusercontent.com/u/17796755/fastest.png

 

Before First View

(7.511s)

 

 

 

i got folowing output when running  ./tuning-primer.sh

https://dl.dropboxusercontent.com/u/17796755/tuning-primer.png

[krisives]

If you have a tool like PHPMyAdmin installed or use the "mysql" command on the command line and run the "SHOW PROCESSLIST;" SQL you can see which query is taking up the time. If you enable the MySQL slow query log in "my.cnf" you can find out which query is taking so long. Maybe it's missing an index too.

[bellini13]

have you taken the advice from the tuner and altered any of your mysql configuration?

 

You have 3000+ queries taking longer then 10 seconds, that's way too high.  You need to enable slow query log and determine what those queries are

 

You have 500 connections configured, but only used 50 of them used in 3 days.  You can likely reduce that to 100, to save memory and put those resources elsewhere.

 

You should look to increase your worker thread cache, even if its just 4 to begin with.

 

Also, you need to run the primer script only after the database has been running for over 48 hours with normal usage.  Do not take recommendations if the server was running less than that.

 

The key with tuning mysql is small changes.  Make a single change, wait 3-4 days and run the tuner again and see what else could be changed.

[bibob]

have you taken the advice from the tuner and altered any of your mysql configuration?

 

You have 3000+ queries taking longer then 10 seconds, that's way too high.  You need to enable slow query log and determine what those queries are

 

You have 500 connections configured, but only used 50 of them used in 3 days.  You can likely reduce that to 100, to save memory and put those resources elsewhere.

 

You should look to increase your worker thread cache, even if its just 4 to begin with.

 

Also, you need to run the primer script only after the database has been running for over 48 hours with normal usage.  Do not take recommendations if the server was running less than that.

 

The key with tuning mysql is small changes.  Make a single change, wait 3-4 days and run the tuner again and see what else could be changed.

Thanks alot i will make some test and get back with results, as iam importing products and it is not finished yet, therefore i cant use the results before it is completed