Hi There,

One of our sites (knsiradio.com) is running VERY slow. We're experiencing 40+ second page loads if the site loads at all. This site has 15,000+ news stories on it.

One of the techs at our hosting company took a look at the MySQL logs and said this:

"Your MySQL server is currently hitting as high as 4,942 queries per second (yes, per second), which is easily enough to push it into overload status. I checked the processlist, and the MySQL queries in question are all queries on the knsiradio database, and 99% of them look to be exactly the same query just being run over and over and over."

The AutoNav is set to show the first two levels, and all the news stories are on the 3rd level. So its not an autonav issue.

They can't help much because it's an application issue, not a server issue. We can upgrade the server all we want, and though it may help a little bit, it won't solve the root cause: slow queries and multiple queries.

We're running concrete 5.6.1.2

We tried updating to 5.6.3.3 in a testing environment, but that didn't help.

There isn't enough memory on the server to even perform a database backup from the dashboard.

For those who like MySQL Language...here is something else the tech at our hosting company sent us:

----------
I took a packet capture, and stripped out all the queries, sorted them, cut them short so I wouldn't see the "where blah = blah blah" bit, and then counted them, and here are your top 10 queries (or the first bit of them) from a page-load that is fast (in the format of "count query"). In total, there was over 1,000 queries on the page-load, which is about 4 to 6 times more than a typical app, but still got us a page render in only 2 seconds or so, so not totally intolerable:

367 select akID, akHandle, akName, AttributeKeys.a
267 select avID from CollectionAttributeValues whe
173 select Pages.cID, Pages.pkgID, Pages.cPointerI
173 select cvID, cvIsApproved, cvIsNew, cvHandle,
65 select arID, arOverrideCollectionPermissions,
54 select avID, akID, uID, avDateAdded, atID from
54 select atID, pkgID, atHandle, atName from Attr
47 select bID, bIsActive, BlockTypes.btID, Blocks
46 select CollectionVersionBlocks.isOriginal, Blo
42 select petID, pkgID, petHandle, petName from P

Now, here's from a page load that is slow, whole different story. I just about fell out of my chair and :

3266 select avID from CollectionAttributeValues whe
3207 select cvID, cvIsApproved, cvIsNew, cvHandle,
3202 select Pages.cID, Pages.pkgID, Pages.cPointerI
2955 select avID, akID, uID, avDateAdded, atID from
2947 select atID, pkgID, atHandle, atName from Attr
2770 select value from atBoolean where avID = '285'
385 select akID, akHandle, akName, AttributeKeys.a
80 select arID, arOverrideCollectionPermissions,
53 select CollectionVersionBlocks.isOriginal, Blo
53 select bID, bIsActive, BlockTypes.btID, Blocks

Those queries broken out into their full length (this time extracted from the stack trace):

3266 times - select avID from CollectionAttributeValues where cID = '193' and cvID = '5' and akID = '17' LIMIT 1

3207 times - select cvID, cvIsApproved, cvIsNew, cvHandle, cvName, cvDescription, cvDateCreated, cvDatePublic, cvAuthorUID, cvApproverUID, cvComments, ptID, CollectionVersions.ctID, ctHandle, ctName from CollectionVersions left join PageTypes on CollectionVersions.ctID = PageTypes.ctID where cID = '112' order by cvID desc

3202 times - select Pages.cID, Pages.pkgID, Pages.cPointerID, Pages.cPointerExternalLink, Pages.cIsActive, Pages.cIsSystemPage, Pages.cPointerExternalLinkNewWindow, Pages.cFilename, Collections.cDateAdded, Pages.cDisplayOrder, Collections.cDateModified, cInheritPermissionsFromCID, cInheritPermissionsFrom, cOverrideTemplatePermissions, cCheckedOutUID, cIsTemplate, uID, cPath, cParentID, cChildren, cCacheFullPageContent, cCacheFullPageContentOverrideLifetime, cCacheFullPageContentLifetimeCustom from Pag
es inner join Collections on Pages.cID = Collections.cID left join PagePaths on (Pages.cID = PagePaths.cID and PagePaths.ppIsCanonical = 1) where Pages.cID = '304'

2955 times (and note: the identical query to the following where avID -= 285 hit over 2000 times):
select avID, akID, uID, avDateAdded, atID from AttributeValues where avID = '285'

2947 times (and again, this one over 2000 times was the identical query, not even a variation of the where blah = blah values):
select atID, pkgID, atHandle, atName from AttributeTypes where atID = '3'

And last but not least, from our heavy hitters category, this identical query (ie: no change to the 'where') run 2,770 times:
select value from atBoolean where avID = '285' LIMIT 1

So, long story short, there is some really broken something's going on under the hood here intermittently, and I expect that the app has some internal cache that it stores things in, and when that cache expires, is when we get the bad behavior as it re-loads it's cache (only explanation I can come up with for it being both intermittent, and the apparent cached data surviving a mysql/apache restart).
----------
Any thoughts?