slow site: db problems

Hello,

We're running Drupal 4.7.6 with the following contrib
modules:

captcha, contemplate, event, forward, inline, links,
pathauto,
tagadelic, taxonomy_theme, urlfilter, cck, flexinode, i18n,
panels,
print, taxonomy_access, textimage, views.

We're having a serious database performance issue. Page
loads sometimes
take several minutes, so does adding or editing content. The
top and
iostat commands show a very high iowait. iostat -p also
tells us that
the load is on mysql.

So, a couple of questions:

- Could there be an obvious problem with our combination of
contrib
modules? I'll try disabling them to see what each of them
might or might
not do, but there may be obvious problems ...

- In the node table, the vid row is at the bottom. On a
fresh Drupal
install, I noticed that the vid row is located second (below
nid). How
bad is this? What's the best way to put the vid row (and its
content)
back in place again?

- I'm thinking this might also relate to another problem
we're having: a
few weeks ago, our search indexing stopped working. This
happened after
upgrading from 4.7.5 to 4.7.6. I tried rebuilding the index,
but the
problem stays. Is it possible that both issues are
connected?

If more specific info is needed to give advice or help,
please ask.

Thanks,
-- bruno

-- 
[ Drupal support list | http://lists.drupal.org/
]

Quoting Bruno De Bondt <brunoindymedia.be>:

> Hello,
>
> We're running Drupal 4.7.6 with the following contrib
modules:
>
> captcha, contemplate, event, forward, inline, links,
pathauto,
> tagadelic, taxonomy_theme, urlfilter, cck, flexinode,
i18n, panels,
> print, taxonomy_access, textimage, views.
>
>

What does your php log contain?  I'm thinking a memory
overflow issue.

Earnie -- http://for-my-kids.com
-- 
[ Drupal support list | http://lists.drupal.org/
]

Hello,

> What does your php log contain?  I'm thinking a memory
overflow issue.
>
> Earnie -- http://for-my-kids.com
>   

The php log doesn't contain anything about memory overflow.
In case it
did, what would this mean? That php isn't allowed to use the
memory it
needs?

I'm getting closer to a solution though.

Yesterday evening I 'rebuilt' our database on a test
machine: installed
a fresh Drupal db, added the tables from the contrib modules
we're
using, and imported our site's data. All seemed fine, i/o
seemed ok.

This morning however, iowait was showing numbers of 70%, 80%
again (*).
When I restarted mysql, it would drop to 0,0%. Wondering
what might be
causing this, I disabled the modules to see the possible
effect, but got
no result.

Then I thought of the other problem we're having: our search
indexing is
broken. The search's settings page says it indexed 100% of
the site,
while in fact it always stops somewhere for no apparent
reason. When we
rebuild the index, search builds the index up to the point
when it was
asked to re-index, and then it stops again. So we have to
re-index
regularly to keep our index up to date.

Anyway, thinking furher about the indexing problem, I
manually launched
cron.php when iowait was 0,...%. When I did this, iowait
went up to 70%,
80% and stayed very high (+50%). Restarting mysql brought it
down to
0,...% again.

So I'm thinking both problems may be connected: the broken
search
indexing and cron.php that causes iowait to go way up - and
causing it
to stay very high. We also see that there is a high load on
mysql.

Could you think of a reason why this is happening? Or how I
could look
for reasons? I disabled cron for now.

thanks,
-- bruno


(*) We recently moved our test site to a seperate machine,
but there was
still a crontab on the live server pointing to the test
domain, which is
why cron.php was called on the test site.

-- 
[ Drupal support list | http://lists.drupal.org/
]

Quoting Bruno De Bondt <brunoindymedia.be>:

>
> Could you think of a reason why this is happening? Or
how I could look
> for reasons? I disabled cron for now.
>

There could be a few reasons; 
http://www.google
.com/search?num=100&hl=en&q=cron.php+site%3Adrupal.o
rg&btnG=Search brings several pages for 
research.

Earnie
-- 
[ Drupal support list | http://lists.drupal.org/
]

> Anyway, thinking furher about the indexing problem, I
manually launched
> cron.php when iowait was 0,...%. When I did this,
iowait went up to 70%,
> 80% and stayed very high (+50%). Restarting mysql
brought it down to
> 0,...% again.
>
> So I'm thinking both problems may be connected: the
broken search
> indexing and cron.php that causes iowait to go way up -
and causing it
> to stay very high. We also see that there is a high
load on mysql.

- Set up a MySQL slow query log on your test machine.

- OPTIMIZE all your tables.

- There is a setting on the search config page that allows
you to
control the number of nodes indexed per cron run. Try
reducing it.

- Try disabling the search module and run cron. If it isn't
the search
module causing an issue, try disabling other modules .. ad
nauseum.

etc. etc.
-- 
[ Drupal support list | http://lists.drupal.org/
]