Admin Causes 100% CPU use - with Reward Points modules installed

[asphole]

Hi,

I'm currently running zen version 1.3.8A / DB Patch Level 1.3.8 and have suddenly started getting a very slow admin area. (2-3 weeks ago now) I've checked the settings mentioned in previous threads about category counts etc and switched off everything not needed (including version checking) but nothing has helped speed up the admin area. After spending hours discussing this with my hosts they have now advised me that the server CPU is spiking when they access my admin area. I've pasted their reply:

'It seems that there is something wrong with the database as when I try browse the admin page, the CPU usage goes up to 133% then it comes down again after loading the page. the mysql process is using more then 100% CPU while browsing admin page. Could you please check with your developer and optimize the database properly. '

I certainly havent made any changes prior to this error occurring and the site was lightning quick (The actual store loads very quickly) Parse times for the store were aprox 0.1 seconds even in the admin area but almost overnight the admin area is now 9 seconds minimum (store still 0.1 seconds).

Unfortunately with so many orders to process it is taking forever when each page load takes so long. Does anybody have any idea why this could be happening or even how I could pinpoint the exact reason for the CPU spike?

Thanks Guys.

[Ajeh]

Do you only use the Admin for working with your data or do you import?

Have you checked your database to see if you are missing indexes?

Are there any particular pages in the Admin that are specifically bad?

How many Categories?

How many Products?

How many Attributes?

[asphole]

Hi Ajeh,

The only thing i really need to do consistently is update the status to dispatched. I have approximately 20 categories and 800 products.

I wouldn't know what indexes should be there to be honest but it was working well until recently and nobody has changed any DB settings.

Only 1 product has attributes and thats been out of stock for several months so I dont think thats the cause.

Basically, any page at all inside the admin area can take 10-15 seconds to load.

My hosts keep telling me to contact the cart developers so they can optiimise the sql queries but again i dont really beleive this is a zen issue as no changes have been made in between it loading it quickly and then slowly. (However my hosts are continually tweaking server settings)

Any ideas?

[Ajeh]

If you like, you may PM me about this issue and I can look into it further to see if I can spot what is causing this problem, when I get some time ...

From what it sounds like, you don't have enough of anything to run slow ...

[asphole]

From what it sounds like, you don't have enough of anything to run slow ...

My thoughts exactly! However, I have just figured out the issue and hopefully it may help others in the future. I installed a mod called 'Reward Points' a couple of months ago and obviously tested it before implementing on my live site, then tested it again once it was live and it all worked as it should (Which is why I didnt think of this when the site slowed down 2 months later)

I checked the original install files and there was no index created on any of the mysql tables it created. I viewed the tables via phpmyadmin and there were about 400 pages of records in the `zen_reward_customer_points` table so I created an index on 'customers_id' in that table and bam! Instant page loads again!!

It seems like this was causing the delay all along. (Although i'm still not sure why loading 'Configuration>My Store' for example would be affected by this table)

So, if anyone else has suddenly seen a performance hit in their admin area and they have the reward points mod installed it's probably worth creating an index on the above table.

Thanks for taking the time to get involved ajeh and for the very generous offer of taking a look personally.

[Ajeh]

You are most welcome ... glad that you discovered the cause was missing indexes and thanks for posting the solution that resolved your problems ...