Joins performed without indexes

My dedicated server is being slow, and we are investigating the root cause of the issue.

The largest problem I am seeing is there are hundreds of thousand of sql joins without indexes. This is the largest consumer of CPU cycles that I can see on your SQL server. In the past 24 hours your MySQL server has nearly 100K of them.

Joins performed without indexes: 93396

This is the first and for most issue that you you need to solve, as these are killing the performance of your server.

What tables don't use indexes?

DigitalShadow:

What tables don't use indexes?
Very few. If your site's database hasn't been altered from original ZC implementation, then most tables will have well-performing indexes.

But, without a list of the specific queries they say ought to have joins, it's nearly impossible to do anything meaningful with their feedback.
And, they're probably only looking at some analysis panel and not at any actual queries ... so those numbers might mean nothing at all.

Ask them to quote the top 10 unique queries demonstrating the problem so we can investigate.

DrByte:

Ask them to quote the top 10 unique queries demonstrating the problem so we can investigate.

Can I find this information, is there a logging script that could be used?

DigitalShadow:

Can I find this information, is there a logging script that could be used?
Chances are they've configured MySQL to "log any slow queries" and also to "log any queries which don't use indexes". Those are settings the server administrator can set server-wide, and the output gets dumped into text files on the server, which usually only the server administrator has access to. But they could share that data with you if they're willing. (Sometimes they won't because the logs might contain data from other sites on the server too, and they don't want to go to the trouble of stripping out only your data for you because that takes time on their part.)

And they might not want to have to package up a huge file storing 93396 queries for you to have to download ... besides, there's probably a lot of duplicates in it and that's too much data for you to go through anyway. That's why I asked for the top 10 unique ones.

But you can ask.

They might also be using some advanced tools to perform additional analysis on the logged data. Whether they have any extra information to share with you is unknown to you or me.

Your server might also be slow because of PHP errors ... which Zen Cart stores in the /logs/ folder: http://www.zen-cart.com/content.php?124-blank-page
If your server is generating logs on each page-click, then you really need to fix those first, as every error slows the site down.

The server is mine, so nothing to worry about in regards to other data.

I keep an eye on the log folder, nothing in there to report.

The hosting company are amazing, we have been working on this issue for a while. We are looking to exhaust all options to get the site running as well as it can, I am upgrading the hardware, but if we can improve the code as well, then a benefit can be had there too.

I will get back to you with more information.

It could be that you've got plugins installed that have added tables which aren't properly indexed.

Also, as a diagnostic step to compare your site against original ZC db schema, you could export your table structures using phpMyAdmin and compare those against the original ZC install sql.

Did you see the PM I sent you?

There was one error in there that might be related to that issue I mentioned in the paypal error thread, I have posted in that thread.

The other errors I sent you, did anything look odd?

I've replied to the PayPal thread. Your database content is still latin1 format, and needs upgrading to utf8. Follow the steps here: http://www.zen-cart.com/content.php?313-should-i-convert-my-store-from-iso-8859-1-to-utf8

That is done, I presume this wouldn't have caused the joins without indexes problem.

DigitalShadow:

My dedicated server is being slow, and we are investigating the root cause of the issue.

What tables don't use indexes?

I'm also seeing this on our server and have requested the top 10 unique queries as suggested by DrByte.

100,000 per day average: You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
15M per day average: your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

OK, so now I have a 4 hour (ish) log of queries that do not use indexes.

7000 + lines long and it means nothing to me.

I cannot attach it because I have exceeded my allotment (1.5MB) and can find no way to delete previous attachments. The zipped file is only 46KB. When I add the attachments that I can see, the total is less than 800kb.

So what assistance are you looking for? There is a sort of download/attachment manager, though I think I have attached other files to posts and they not count against what I personally am able to "maintain". Perhaps if the data was put into a spreadsheet it could be sorted in some fashion to make a unique list of these non-indexed queries instead of seeing the same thing repetatively. It may be that only a few queries are wrong and are used extensively.

mc12345678:

So what assistance are you looking for?

Since 2 people seem to be reporting similar database issues, maybe there is a problem that needs to be addressed. No one can fix a problem if they dont know about it.

I see any red mySQL stat as something that needs attention & millions in 4 hours seems to be quite excessive. The stats, BTW, are only visible in my dedicated server. None of my shared servers have the data available.