Large Slow site on VPS

[monkeytown]

We're on 1.3.8a. Haven't had time to upgrade yet.

[CJPinder]

Hi Linda,

You are missing numerous indexes ...

What version of Zen Cart is this?

For a ZC 1.3.8a site, to me, the indexes look exactly as they should be. Which ones do you think are missing?

Regards,
Christian.

[CJPinder]

Hi,

I think the problem is a resource issue because I can be refreshing on the product details page and getting parse times of 1 second and then suddenly the same page takes 50 seconds to load. I hate to throw money at the host if that isn't the problem but to me the site seems to be fine until what I guess is a bottle neck in the mysql server. Does that sound reasonable?

Thanks for answering all my questions. There is quite a lot that can be done to optimise a ZC site, especially on a VPS or dedicated server. Much more than I could possibly go into in a forum post. However, you immediate issue seems to be caused by one or more SQL queries hogging the MySQL server.

In the Admin, if you go to Configuration -> Product Info, what is the 'Also Purchased Products Columns per Row' setting set to? If it is not 0 then trying setting it to 0 and see if that helps. If it does and you want that functionality then it can be fixed.

In phpMyAdmin do you have a Status tab? If so could you go to it and then tell me which values are marked in red?

Regards,
Christian.

[Ajeh]

Sorry I miss counted ...

Now, knowing this is v1.3.8, the concerns are the Known Bug Fixes and several additional fixes in v1.3.9 that improve speed ...

There are other configuration settings that should be reviewed to ensure things are turned off that are not used ...

Example: even if not displaying the Category Counts, make sure that the setting is turned off ...

Even if not displaying New, Featured, Specials and Upcoming, make sure that the settings are turned off ...

Check the Optimization of the database ...

I would try just for fun a test database with a copy of your live site put in it and a test directory with a copy of Zen Cart v1.3.9 and run the upgrade on the database and just see how the speed compares ...

[monkeytown]

I set the also bought to 0. I don't think we need this functionality although if we can include it with little to no performance hit we will. When turned off the site seem to perform fine (product info page parses in around .1 seconds). The problem though is that the site does run fine at times and then gets bogged down so it is hard to tell if that made a difference. Toggling it off and on doesn't seem to have an immediate effect on the site.

As for the red values on the status tab, there are several.

This MySQL server has been running for 0 days, 0 hours, 43 minutes and 41 seconds
Received 22 MiB 30 MiB

Slow_queries 24
Handler_read_rnd 13 k
Handler_read_rnd_next 258 M
Qcache_lowmem_prunes 98 k
Created_tmp_disk_tables 192
Select_full_join 220
Opened_tables 244
Table_locks_waited 385




@Ajeh - I need to upgrade but with the extensive changes to the site including to some core files I haven't had the time recently to tackle it. I don't think that is the primary problem but it still needs to happen one of these days and will hopefully provide a slight (if not more) performance boost.

[Ajeh]

The idea was to test a minimized concept of an upgrade in a test database with the v1.3.9d code just to see the affect to the speed and performance as there may be clues to quick fixes you could apply to your v1.3.8 site ...

This would just be:

1 make a test directory

2 FTP v1.3.9d code to test directory

3 make a test database

4 copy live database to test database

5 copy confgure.php files from live to test configure.php with changes to paths, database and username

6 run the /zc_install and upgrade the test database

[CJPinder]

I set the also bought to 0. I don't think we need this functionality although if we can include it with little to no performance hit we will. When turned off the site seem to perform fine (product info page parses in around .1 seconds). The problem though is that the site does run fine at times and then gets bogged down so it is hard to tell if that made a difference.

Best leave it set to 0 for the moment.

You said that the problem started after hosting a second site on your VPS. Can you give us some information about this 2nd site? Version, add-ons, URL, settings etc?

This MySQL server has been running for 0 days, 0 hours, 43 minutes and 41 seconds

43 minutes isn't very long. Was the MySQL server manually restarted or did it crash?

Slow_queries 24

Do you have the slow query log enabled? If so could you post what the slow queries are.

Handler_read_rnd_next 258 M
Table_locks_waited 385

For 43 minutes they both seem very high.

Approximately how many visitors a day does each site get?

Regards,
Christian.

[monkeytown]

The second site is a news website that we moved to our server. It was previously on shared hosting so I figured it wouldn't provide much of a resource hog although they do get a few thousand hits a day. I do have the slow query log enabled. I'll take a look at it when I get a chance (possibly in the morning).

I restarted the mysql server because it was preventing our pages from loading (pages taking several minutes to load). I included it to show that the numbers below were attained in relatively short time. Most of the number don't mean anything to me however.

@Ajeh - I'll try to duplicate this when I can work out some time to do so. Thanks for the tips :)

[CJPinder]

The second site is a news website that we moved to our server. It was previously on shared hosting so I figured it wouldn't provide much of a resource hog although they do get a few thousand hits a day.

Ah, so not a ZC site then. That makes it a harder to pin point the problem. The issue may well lie with the news site rather than your ZC site.

The news site may well have worked well on shared hosting as good shared hosting providers have very powerful MySQL servers. On a VPS it may well cause problems.

I do have the slow query log enabled. I'll take a look at it when I get a chance (possibly in the morning).

Seeing the slow queries would help to determine which site is causing the problem.

I restarted the mysql server because it was preventing our pages from loading (pages taking several minutes to load). I included it to show that the numbers below were attained in relatively short time. Most of the number don't mean anything to me however.

A couple of the numbers are very high, much higher than I would expect from a ZC site. I would suspect that it is the news site that is causing excessive load on the MySQL server.

Regards,
Christian.

[Vger]

Servint may also have the Max Number of Concurrent MySQL Queries set too low. The VPS spec is "almost" what you'd get with full dedicated.

Ask Servint what they've got that set to, and if it's in the low hundreds ask them to kick it up to 1,000 and see if the VPS can handle it. They can always put them down again.

Also make use that "use_pconnect" is set to false in Zen Cart configure.php files. It is by default set to that but maybe you changed it?

If you are logging page parse times, turn that off as well.

Vger