|
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?]
|