Thread

I have 2 problems on one heavy visited site:
1. system reports that problem is with indexes in query like this

SELECT DISTINCT(t.entry_id) FROM exp_channel_titles AS t
    LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id
       INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id WHERE t.entry_id !='' AND t.site_id IN ('1')  AND t.entry_date < 1346501576  AND (t.expiration_date = 0 OR t.expiration_date > 1346501576) AND (t.channel_id = '1' OR t.channel_id = '5' OR t.channel_id = '4' OR t.channel_id = '6' OR t.channel_id = '7' OR t.channel_id = '8' OR t.channel_id = '12' OR t.channel_id = '13' OR t.channel_id = '14' ) AND exp_categories.cat_id = '129' AND t.status = 'open';

system gave me message after analysis

You have had 13452 queries where a join could not use an index
properly
You should enable “log-queries-not-using-indexes”
Then look for non indexed joins in the slow query log

and in log file I got these queries as rpoblematic

and second problem is that in 17 hours of logging events I have found out that EE created 856358 tmp tables. Obviously there is a problem with database and my hosting company suggested to switch to innodb but latest informations on this forum I found are really out of date. For example this http://ellislab.com/forums/viewthread/110394/
Are there any new informations regarding that? I would like to point out to this link when talking about this problem.

Please help, because I use CE cache and although it helps, still there is a big strain on MySQL and I have to solve it somehow because in administration people need around 10 minutes to save one entry.

Could you provide more data: 
1) What versions of ee, php, DB are you using? 
2) Have you tried addons like solspace static page caching? 
3) Have you used partials [http://eeinsider.com/articles/template-partials-using-stash/] and related advice in the ee-insider article?
4. Can you back-up to a time before these problems?
Good luck!

1. EE v2.5.2, PHP 5.3.10. mysql 5.1.63
2. as I mentioned above, I use CE Cache and I use it as static cacheing, and that means it saves everyting in files
3. I do not see how template partials can help in this situation because I do not use embeds and I reduced number of queries. Bigger problem is that of 800 000 and someting tmp tables created by the system, 13 000 queries in 17 hours that are not using indexes right and that innodb would be solution but I cannot find something new about EE and innodb on forums
4. only if I remove 200 000 users

Bigger problem is that of 800 000 and someting tmp tables created by the system, 13 000 queries in 17 hours that are not using indexes right and that innodb would be solution but I cannot find something new about EE and innodb on forums.

MyISAM is the preferred engine when using ExpressionEngine. MyISAM is read-optimized, and ExpressionEngine is primarily read-driven.

What has me concerned is the number of temp tables you are seeing. ExpressionEngine doesn’t create temp tables as part of its operation.

So, let’s figure out what’s going on. Have you done any custom work on your ExpressionEngine db? What other add-ons are you using? Do you know what tables are being created?

Can you open your Control Panel and visit Tools > Data > SQL Manager and use the Query Form to run these 2 queries:

DESCRIBE exp_channel_titles;

DESCRIBE exp_channels;

Run them separately and get me a screenshot of each output so I can have a look.

Cheers,

Here are the links on the pictures

http://i843.photobucket.com/albums/zz355/crnaovca/ScreenShot2012-09-05at62505PM.png

and

http://i843.photobucket.com/albums/zz355/crnaovca/ScreenShot2012-09-05at62407PM.png

I did not do anything custom on EE db and I am using these addons

http://i843.photobucket.com/albums/zz355/crnaovca/ScreenShot2012-09-05at62407PM.png

If EE is read driven, why then I am having so much table locks? Because if I have 200 000 users per month and only 20 people entering in the CP around 100 entries per day then there should not be more than 200-300 insert/update/delete queries per day general. As I have seen it this query i mentioned is not indexed properly.

Hi Crnaovca,

The 3rd screenshot is a duplicate, can you check that? I’d really like to see the add-ons list.

If EE is read driven, why then I am having so much table locks?

That’s just it, ExpressionEngine shouldn’t be doing that, so we need to find out what’s going on. Can you get information on the tables that are bing created?

Thanks!

I have a similar problem at the place where I work .. we host some 20 different EE sites, in a miscellaneous number of versions (ranging from 1.6.1 for some really old not-important sites, through 2.5.3 for the most recent and most active sites).

actually, the 2 sites that pull the most traffic are at version 2.5.2, and we notice a strange lag spike at 8pm our time, where the mysql cluster sags under the stress from the server running these EE instances.

The slow query log returned the same query that the OP posted. Is there an answer from the OP as to how he solved his problem? We would very much appreciate it if it were posted here on the forum 😊

Hi ExpressJoa,

We never did hear back from Crnaovca on this issue specifically.

I’m going to ping the Engineers on this for a better understanding of that query and why it might be impacting performance.

Cheers,

The above query creates a temp table when it needs to specify ‘Distinct’, which happens when a category is specified in a channel tag (either dynamically via the url or through the use of a parameter).

It shouldn’t be causing slow post times, however.  That would typically be an issue with clearing caches or perhaps ‘pinging’ or something along those lines.

The heavy load at a particular time is also interesting.  Are you seeing a surge in traffic then?  No chron jobs running or anything along those lines?

It’s also likely a good idea to give the site a good optimization check.  Turn the output profiler on in ‘Admin- System Admin- Output and Debug’.  Go through your template pages (logged in as a superadmin)- do any pages seem to be using an inordinate number of queries?  Check the templates on those queries- any chance you’re using conditionals and THINK some tags aren’t being run, but they are?  Any way you can reduce the number of tags on a page?  If you know channel tags that limit by category are a problem- can you identify those tags and cache them?  I’d start just setting the tag cache.  Don’t go overboard with caching- but if you see something that’s a likely issue, adding a tag cache may help.

There isn’t a quick alternative to that ‘distinct’ in that query, so changing it right now isn’t an option.  But we should be able to get things running smoothly for you.  And I’ve noted that one down as something that would be well served by optimization.

That help?