503 Errors and Search. *Updated and HELP Needed* - Page 3

davedenmark · #21 (**permalink**) 07-30-2007, 09:08 PM

I believe I can help. I'm a DBA/Developer/Network Admin that works with large database "synchronization" projects that index large amounts of data. Your problem is similar to the ones I have dealt with in the past.

The database problem has to do with indexing. Your database has grown to a very large size and in sort, has gotten fragmented. Database optimization can greatly increase the speed of the database by organizing & compacting (similar to the way ZIP files compress many smaller disorganized files into once nice container) the records (specifically large column datatypes (posts for instance?) and binary data (attachments?).

Let me break it down for you. The query in your error message shows a couple basic things:

Query_time: 93
This is in milliseconds...which is a very long time for computers. Normally queries should be <1s (almost all of my database queries run in <.2s; the data transfer takes longer than the database to return the records). Think of ping times for packet travel trhough the intertubes (anybody still play quake II on 56k? haha).

Lock_time: 0
This number indicates that you were not writing data, and reading only; aka SELECT queries.

Rows_sent: 15
Fifteen rows was all you asked for, via the query. Rather small number.

Rows_examined: 267055
WOW! That's quite a few "posts" the database had to go through to locate just 15 records. No wonder it took 93 milliseconds for it to run through all that data.

Looking deeper into the query, I see that the table structure they are using is quite normalized and logical, but the developers for this version of phpBB chose not to use INNER JOINS, instead they chose WHERE clauses. Using a JOIN statement over a conventional series of Where A1.ID = B1.ID statements. Switching over to a join would place less of a tax on the individual tables and moreso towards their indexes. Google for more info on this if you'd like.

Since your forum is running phpBB I am guessing that MySQL is running this backend. Upgrading to the latest version of MySQL can also affect performance of the database's own ability to maintain itself. While the site is pulling up this uncached query for each user that clicks on "forum list" takes time away from other web sites (or even this site) to display other database-related data for users just browsing along. Depending on how the server admin has the site running, they can allocate more CPU cycles (threads or even basic clock time in milliseconds) to each site to allow them to perform more complex tasks at the expense of general responsiveness, or cut things back to be nice and quick for all sites on this server. In a nutshell, this site is starting to grow some roots and needs a bigger yard.

You can also have your server admin schedule cron jobs to compact & reorganize the databases (via some sort of script or stored procedure).

[Edit: Just looked at the link posted earlier back to phpBB forums and this guy knows how to use PHP & SQL together pretty well. However, he's not scaling the site up to a very large amount but would be sufficient for this site. How far have you guys gotten on the upgrades?]

**Wideawake** · #22 (**permalink**) 07-30-2007, 09:26 PM

All of this is over my head

I havent started on any of the tweaks yet, would they be safe to preform do u think? Yea it uses MySQL and if Im not mistaken the newest version. The DB size is only about 125MBs. I know dedicated hosting would probably solve any issues but I figure it shouldnt be that bad. I think a conversion to vB or phpBB3 would also fix this, but I havent found proper SEO mods, and WAP themes. And you think that a cron job could help then I will email my host and see if they would do that for me.
Thanks for the suggestions!
Mike

**Wideawake** · #23 (**permalink**) 07-30-2007, 09:33 PM

I also mad a few adjustments by deleting unactivated users, taken down the blogs, stopping guests search, and implemented a new CAPTCHA system.

davedenmark · #24 (**permalink**) 07-30-2007, 09:53 PM

From the Admin CP, have you tried reindexing the search indexes?

What about reindexing the database entirely? There should be a script for it in the ACP.

There are a few links, but unfortunately I cannot paste them here, I have PM'd them to you instead.

boyo · #25 (**permalink**) 07-30-2007, 11:04 PM

Ok, as for the mods, i'm nto sure what SEO is, or what your refering to, but I use SMF on a couple of the sites I administer, and it has a built in WAP, which works pretty good, it's just bare bones, only text (In WAP mode) Check it out, http://www.simplemachines.org Here is what the WAP looks like.

**Trident** · #26 (**permalink**) 07-30-2007, 11:19 PM

Whatever you guys have done has made all the difference in the world.
This forum has gone from unusable (this weekend) to faster than I've ever seen it. No errors today.
And the issue that I posted about a few weeks ago seems to have cleared itself up. [This was the only site that I couldn't log into automatically using FireFox. I could log in automatically on other forums and I could log in automatically here using IE7.] I'm having no trouble logging in automatically now.

**Wideawake** · #27 (**permalink**) 07-30-2007, 11:27 PM

Quote:

Originally Posted by davedenmark

From the Admin CP, have you tried reindexing the search indexes?

What about reindexing the database entirely? There should be a script for it in the ACP.

There are a few links, but unfortunately I cannot paste them here, I have PM'd them to you instead.

I have recently reindexed the search indexes. But theres nothing in the ACP to do the whole database. Im looking into the what you pm'd me.
~mike

**Wideawake** · #28 (**permalink**) 07-30-2007, 11:28 PM

Quote:

Originally Posted by boyo

Ok, as for the mods, i'm nto sure what SEO is, or what your refering to, but I use SMF on a couple of the sites I administer, and it has a built in WAP, which works pretty good, it's just bare bones, only text (In WAP mode) Check it out, http://www.simplemachines.org Here is what the WAP looks like.

Im not much of a fan of SMF. And the WAP version I like to have fully functional. Also SEO is search engine optimization.....notice the .php files look like .html

Thanks for the suggestion tho!
~mike

**Wideawake** · #29 (**permalink**) 07-30-2007, 11:56 PM

In phpmyadmin I went through and optimized all the tables in the database. Hopefully this will further help. Thanks again to all the ppl who left suggestions.
~Mike

**jamesus** · #30 (**permalink**) 07-31-2007, 12:20 PM

Quote:

Originally Posted by Trident

Whatever you guys have done has made all the difference in the world.
This forum has gone from unusable (this weekend) to faster than I've ever seen it. No errors today.
And the issue that I posted about a few weeks ago seems to have cleared itself up. [This was the only site that I couldn't log into automatically using FireFox. I could log in automatically on other forums and I could log in automatically here using IE7.] I'm having no trouble logging in automatically now.

I agree. This is the snappiest I have ever seen the site!