Site Is Really Slow.

[PranK]

Bummer. I thought I'd sorted them... Hmmm.

[PranK]

OK, Just saw the IPS Driver Errors just now.

It looks like the service which keeps the search index up to date is causing table locking on the posts table. I have been meaning to convert that table to Innodb (no table-level locking) but haven't gotten around to it. I'll try to do it over the weekend.

[funkymonkey]

hmm just got the IPS error at 12...

jumped on the server and it was some indexer service for sphinx_ipb? does that make sense Prankeh?

[funkymonkey]

gah... looks like you already figured it out

[PranK]

gah... looks like you already figured it out

Yeppers.

[a20089]

While the site isn't a speed demon, it seems to be running better these days.

[PranK]

I made some changes yesterday to Apache, PHP & MySQL which should make a difference to both speed and getting rid of those pesky IPB Driver Errors.

[adznight]

Howdy Christian, I'm sure you're aware but I'm still seeing IPS Driver and speed errors. Just though I'd let you know. Very intermittent though, it's not my end, I'm on a 2Mb/2Mb connection here

[PranK]

Hey Adz, check the IPS Driver Error issue in this forum (maybe 1 or 2 down?) I have been working on it recently.

[paulr33]

from what ive seen so far and learnt it seems the actual database is probably the bottleneck

300-400ms isnt exactly crash hot but that certainly wont make the page load in 20 seconds plus

typical loading times i am seeing from a few sources is well into the 10 seconds for a page load

it could be a case some users have some stupid indexes or some whacko foreign keys etc on them

my access is pathetic, it takes 10 seconds + for any thread to load

if i fork more connections its not different they all just take as long

its probably a case we are maxing out mysql and its not perfomring as well as it could be

a load average of the box is exactly that, the average load in the running queue

it does not give an accurate representation to how mysql is performing if it has 3 childs that are running as the load will just show around 1,2,3 etc

but if each of those pids is fightint for locking resouces (which mysql is HOPELESS at) and other resources then yeah its going to suck

how is the database setup, is it on localhost

has anyone optimized the schemea?

has anyone looked at the slow queruies (theres a command arg to log them)

how are the indexes setup for the big tables?

if you do show process do you see much locking processes or waiting?

wht about copy to tmp_table do you see much of this?

the actual tcp response from the box is a bit slow which is probably due to system load and number of apache childs

they could be fighting for resources, competing with other junk or it could just be the box is busy

a good test is;

open a dos prompt

telnet www.skylinesaustralia.com 80

then note how long the socket takes to open

this is a good sign that it could be either network or server/httpd process contention

then once its open paste in;

GET /forums/style_images/1/bc_new.gif HTTP/1.0

HOST: www.skylinesaustralia.com

and hit enter twice

it should come out pretty quickly and then end

so if it does then it shows the server and your connectivity is pretty fast

i think its only when you load the main forum pages that it takes ages

which is probanly all the sql seleciting and other junk involved

can we move to another datbase other than mysql?

postgres would be so much better

not tryring to be a know it all and pointing blame etc

more interested in getting it sorted

there are lots of tools to help debug slow pages / database etc

apache_top is a good one

the mysql slow_process command line is a good one

without the proper access i can only speculate and guess

but i know its driving me nuts and probably others

do we need to donate some more $$$?

[PranK]

Wow Paul, great post! Its good to see people taking an interest in fixing it rather than just getting cranky!

I agree wholeheartedly with you that it is MySQL. I have been playing with the conf for a few weeks now, I just cant seem to get it to a happy medium between load and speed.

Re. your questions;

how is the database setup, is it on localhost

- Yes

has anyone optimized the schemea?

- Its the default IPB Schema with additional tables for mods / hacks and other things

has anyone looked at the slow queruies (theres a command arg to log them)

- Yep, I log all slow (>5 sec) queries to a slow query log. I just havent check it in a while).

how are the indexes setup for the big tables?

- Not sure, but I have checked the indexes through the admin cp of IPB and it reports no problems.

if you do show process do you see much locking processes or waiting?

- Only occasionally. I have been considering changing the posts table to Innodb as we dont use full text on it anymore anyway as search is taken care of by Sphinx

wht about copy to tmp_table do you see much of this?

- Hmmm, havent noticed.

Also, I havent analyzed the database for a while, thats definately worthwhile doing.

I have a few ideas for speeding the site up. Postgres wasn't one of them but I am more than happy to go down that path. I was also going to setup lighttpd to serve static content (images mainly) and have apache looking after the php side of things.

You sound like you know your stuff, I'll be more than happy to give you access to the box to have a poke around if you'd like?

Thanks Paul,

Christian

[paulr33]

hey no problems

happy to help out

the big ones that are likely to yield help will be chcking out apache_top

and looking at the mysql process listing it

it could either be httpd contention, if it is then its just apache conf tweaking

and if its sql related then we should find it in show processlist and other mysql checks etc

it cant hurt to manually check the indexes and the database shouldnt need to be 'checked' as in check table

as that will look for corrupted blocks etc

postgres will only help (or another database) in the event that mysql is bottlenecking

that is, its bottlenecking due to the way it locks the tables

mysql cant do row locking as its too simplistic so when someone makes a changes to their permissions

or adds a new thread etc it must lock the whole table and commit that change

so for the time being while its locked nothing else can read from that table

so you have all the other processes waiting for 'waiting - locked table'

ive seen it all too often with mysql and high performance based websites

postgres, sql server, oracle etc all do row locking

in the sense when a row is being updated or inserted they lock that specific row only

and allow access to all the other rows so your application isn't any wiser

im happy for some access to have a look around and see what i can find

maybe its something easy or maybe its something complicated

cant hurt to loook

[paulr33]

just letting you know its much better tonight

obviously you are working on it - good work

hope it helps

[R31Nismoid]

Read the IPS driver thread Paul, list of what was done today is there

[The Mafia]

hi guys

sorry about the late reply.

well done. The site is definatly faster from home and work.

Its still a little slow, but a hell of a lot better than what it was.

A little bit more tweaking and it will be perfect!

[mesmerized]

well done admins, pages load within 3 seconds using broadband, has never been so quick

[The Mafia]

lol and about bloody time!

Make it faster though