By austintnacious on

Hi!

My Drupal 6.4 installation has slowed to a crawl.

I don't know what to do about it!

Non-Drupal pages coming from the web host are super fast but anything output via Drupal is so slow it's impossible to get any work done!

I've turned off all unnecessary modules, I don't have any traffic or users yet and I'm using a bare bones theme, so there really should be no stress on the database server.

What I've noticed and what worries me is that when I back up my database now the back-up file is over 550 MBytes.

Why is the database so huge?

Is this what's causing thee poor performance?

HELP!

⋅ Categories: Drupal 6.x

Comments

aitala’s picture

aitala commented

I think folks will need more information than you provide... but 550 MB sounds waaay too huge. How many users / nodes do you have? What does the site do? Etc...

Maybe even a link to your site? Assuming its still running?

Eric

__________

Eric Aitala - f1m@f1m.com
The Formula 1 Modeling Website
www.f1m.com

__________
Eric Aitala - ema13@psu.edu
Penn State

224b8605113373e086cb27708ff301ba18ce394db1996e7e22928e4555e0d20b1b6cecc7f67c9bd9e536cb915779c485

austintnacious’s picture

austintnacious commented

Right now the site does nothing.

I'm trying to learn Drupal and develop a site at the same time.

I've basically given up because the performance is so bad. . .

Is there no way to optimize a Drupal site?
Any tools for checking on Drupal response times etc.?

Thanks!

aitala’s picture

aitala commented

Um, there is nooooo way that the database should be that big. I have 2000 users, 1200 nodes, plus Gallery and phpBB in the same DB and its only 60 Mbytes.

Can you check the DB with something like phpMyAdmin to see what's piling up the space? It can also optimize tables.

Also look for the Drupal devel module... it might help.

Eric

__________

Eric Aitala - f1m@f1m.com
The Formula 1 Modeling Website
www.f1m.com

__________
Eric Aitala - ema13@psu.edu
Penn State

224b8605113373e086cb27708ff301ba18ce394db1996e7e22928e4555e0d20b1b6cecc7f67c9bd9e536cb915779c485

austintnacious’s picture

austintnacious commented

I had another look at the Db with phpMyAdmin and the cache_devel_themer table is at 512 MiB (MBytes?) with over 300 hundred records.

Do you know if it's safe for me to empty this table without causing problems elsewhere?

Thanks!

Also, how do I use phpMyAdmin to optimize the Db?
I can't find and "optimize" button anywhere ;-P

aitala’s picture

aitala commented

'Optimize' in this case means fixing any overhead in the tables... if there is none, you can't optimize.

I think you can truncate the cache_devel_themer table... but make sure you are running a default Drupal theme like Garland first

E

__________

Eric Aitala - f1m@f1m.com
The Formula 1 Modeling Website
www.f1m.com

__________
Eric Aitala - ema13@psu.edu
Penn State

224b8605113373e086cb27708ff301ba18ce394db1996e7e22928e4555e0d20b1b6cecc7f67c9bd9e536cb915779c485

avskip’s picture

avskip commented

On my site (3 years old and active) the DB is only about 30mb.

Something has either gone wrong or you are uploading a lot of files that are being held in the database.

Maybe it's time to start over??

dman’s picture

dman commented

Two things, possibly related.
Huge database is usually just thousands of page versions in the cache. You can empty any table in the database starting with cache_ without damaging anything, and your backups will be tiny again.

This is supposed to happen automatically somewhat, as is a lot of other housekeeping, when cron is run. Could it be you've never run cron? Temp files cleanups and cache clearing all happens through that. Including watchdog and access log rotation.

It's possible that having thousands of unused entries would slow the site down a bit. However a common slow-down problem is from 404 pages. If you mention a couple of wrong images in your page or theme, Drupal CMS tries to serve them ... each time ... which takes a lot more work than is appropriate. Check your logs in the admin area and see if there is anything you can fix up.

.dan.
if you are asking a question you think should be documented, please provide a link to the handbook where you think the answer should be found.
| http://www.coders.co.nz/ |

.dan. is the New Zealand Drupal Developer working on Government Web Standards

austintnacious’s picture

austintnacious commented

I'll go try a manual run of Cron now. actually I can't find the link to run cron manually.
I'm sure I saw that in there before.
Maybe it was provided by one of the modules I de-activated to try to troubleshoot this, devel or admin menu or something like that

I've emptied the cache tables in phpMyAdmin and that has speeded things up considerably for unauthenticated users but it still seems slow when logged in as admin!

I wonder why they're not clearing themselves out.
I know I've manually cleared the cache a few times and I didn't know that the Themer part of Devel had a cache let alone was storing over 500MBytes of stuff in there. . .

Can the watchdog table also be emptied without causing probs?

Thanks for the help!

dman’s picture

dman commented

It's totally safe to truncate the watchdog any time. In fact it helps when getting a clean slate to debug.

.dan.
if you are asking a question you think should be documented, please provide a link to the handbook where you think the answer should be found.
| http://www.coders.co.nz/ |

.dan. is the New Zealand Drupal Developer working on Government Web Standards

MacRonin’s picture

MacRonin commented

Nothing specific point to this, but if you have a good amount of activity (valid or bots) you could end up with large Logs files i you keep the data to long. Are those setting reasonable?

You could also use a tool like phpMyAdmin to see what the records counts are for the diff tables. This might at least give you a direction to check. That 550meg has to be taken up by something.

-------------------
http://www.PrivacyDigest.com/ News from the Privacy Front (Drupal)
http://www.SunflowerChildren.org/ Helping children around the world ( Drupal)

austintnacious’s picture

austintnacious commented

Actually, it a little worrying that the logs are absolutely empty.

I must have something configured wrong there.

Again thanks for the help!

MacRonin’s picture

MacRonin commented

It's possible that you didn't turn logging on. Remember this is Drupal logging, Apache still can do its own.

But from your post above it sounds like you might have some DEVEL query logging turned on. I think that the DEVEL config page has an option to clear the log entries. But before you clean it out, make sure that you update your options to keep it from logging any more info or it might fill right back up again.

-------------------
http://www.PrivacyDigest.com/ News from the Privacy Front (Drupal)
http://www.SunflowerChildren.org/ Helping children around the world ( Drupal)

beautifulmind’s picture

beautifulmind
he/him
Primary language Gujarati
Location Dubai
commented

Few thing are apparent in Drupal.
If your data base is too large, just truncate the watchdog table, db will shrink to some kbs.
Another reason for this can be that may be have been allocated less php memory. While running a Drupal site other than forums you must have at least 64M of php memory.

:)
Beautifulmind

Regards.
🪷 Beautifulmind

austintnacious’s picture

austintnacious commented

Well, I think I've got back to perfoe=rming as well as it's going to until I find out more about tuning the system.

People watch out for the "cache_devel_themer" table that thing fills up super fast!

I cleared it out and within a few mins and a few page loads from Drupal it was already at 85MBytes.

I've turned Devel and Admin Menus off for the moment.

So one more question.

When a table shows it has overhead and I use phpMyAdmin to "optimize" the table, clearing the over head.
What exactly is happening?

It doesn't seem to stop the table from going into an "overhead" condition again.

Does this have to be done manually?

Or can I set up a cron job or a module to optimize and clear overheads regularly.

Thanks

aitala’s picture

aitala commented

To be honest, I am not entirely sure other than 'defragmenting' the table and 'reclaiming' unused space... basically if the table has had a number of changes, it can get messy. Optimizing it cleans it up and returns the unused space.

A table will always get some overhead if its updated frequently or had frequent deletes. There's not much you can do other than optimizing it once in a while. You can run a cron job if your server allows you to run mysqlcheck or myisamchk from the command line.

Not sure if Drupal's cron (or poormanscron) does an optimize on the DB.

Eric

__________

Eric Aitala - f1m@f1m.com
The Formula 1 Modeling Website
www.f1m.com

__________
Eric Aitala - ema13@psu.edu
Penn State

224b8605113373e086cb27708ff301ba18ce394db1996e7e22928e4555e0d20b1b6cecc7f67c9bd9e536cb915779c485