HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


Huge OCP4_stats table...is this a problem?

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#56302 (In Topic #12240)
Avatar

Fan in action

Trying to troubleshoot a slow site in a shared hosting environment

Hi everyone,

My site has slowed to a crawl and I'm trying to work my way through possible causes. Strongly suspect it will come down to some combination of three things:

a) I'm on a shared hosting environment
b) I've put in a bunch of custom stuff that could be making it hang somewhere
c) I'm still running a slightly older version

But…in the course of troubleshooting I've come across a rather huge database table on my system and was wondering if anybody can throw some light on it.

ocp4_stats > currently weighing in around 118 megs…is this something I should be worried about?

he asks nervously…

any insight appreciated

cheerio,

andrew

Back to the top
 
Posted
Rating:
#56303
Avatar

Hi,

That table is only an issue from a storage space perspective (and there's a config option to say how many days of stats are kept).

Sorry your site is running slow. Is it every page, or just certain pages?

If it's a shared server and has started suddenly likely causes would be a big increase in your hits, or a big surge in someone else's usage of the server.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#56307
Avatar

Fan in action

Hi Chris,

Thanks for clarification on that table, good to know. The other huge one I can see in there is the translations one but I'm pretty sure that's downright essential.

It's tricky enough troubleshooting this stuff I have to admit. Everything is going slow but certain pages are spectacularly slow, pretty much anything to do with catalogues. It's also only recently really slowed down so I'm fairly sure there are a number of factors at play here.

There hasn't been a recent massive spike in traffic but the first thing I'm doing, as suggested by hosting company, is moving database to different server which might stop the second potential problem you mentioned.

From there I think it's a question of gradually turning off various custom bits I had going and trying to drill down to something specific. In a way it's a good thing as it'll force me to sort out a proper local development environment and upgrade and finally fix a whole bunch of stuff I've been avoiding.

I've used the query tool in the admin to have a quick look at how many queries are being generated on certain pages and saw a figure of around 120-130 on several pages so I'm fairly sure there's room for improvement there.

Anyway, a lot of this sort of troubleshooting is new to me so this post is really just to organise it in my own head and also maybe the description might help somebody. My sequence of things to test at the moment is something like the following after I've made sure I've got adequate backups in place:

i) Test moving database server
ii) Run cleanup tools as described here - ocPortal.com - ocPortal Tutorial: Cleanup tools
iii) Turn on gzip option in admin
iv) Turn off as many custom blocks I might have made as possible.
v) Turn off 3rd party items (e.g. twitter and facebook widgets)
vi) Start moving through things like custom sprites, minimising/optimising CSS etc. etc.

Our site has around 1300 members and is getting around 300 visits a day so I'm assuming that decent shared hosting should be able to handle that but I might be wrong in that assumption, be interested to hear anybody else's feedback on similar numbers.

Either way, I'm seriously thinking of switching over to ocportal's recommended hosting partners in the next month regardless of what ends up being the exact snag with my current setup so that and upgrading to new version are sort of items vii and viii on the list above.

The sort of things I'm using to try and pinpoint where things are going wrong are:

a) Internal ocportal tools such as the query tool
b) Firebug using Net panel and a few others
c) Installed a bunch of Firebug addons from Yahoo and Google mentioned in this performance testing overview  
d) Looking at benchmarking things like Pingdom Tools
e) Reading long technical articles that are miles over my head like - Optimizing Page Load Time

And that's about it…as I said, I'm not an expert on this sort of troubleshooting and I have a sneaking suspicion that the root cause is something fairly dumb I've done myself.

Alrighty…enough rambling. Thanks for getting back to me on the database table thing Chris. If I work out where the bottleneck is, I'll post an update here in case it helps anyone else.

Cheerio,

Andrew

Back to the top
 
Posted
Rating:
#56314
Avatar

Fan in action

And as I look through the big list of queries on my testing page, I see one laggardly fellow in there that appears to be taking 28 seconds. Looking further, I see that this is almost certainly something to do with a custom call to the database I put in myself.

Motto of the story: Always assume you screwed it up yourself first  :$
Back to the top
 
Posted
Rating:
#56315
Avatar

Maybe you need an index on whatever that query is WHERE'ing.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#56336
Avatar

Fan in action

So, further investigation reveals that is was simply a piece of badly organised SQl on my part…didn#t even need an index but thanks for the suggestion. Will bear it in mind for future things.

That query tool really is a very handy piece of kit…

a
Back to the top
 
1 guests and 0 members have just viewed this: None
Control functions:

Quick reply   Contract

Your name:
Your message: