PPCGeeks Forums HTC Arrive HTC HD2 HTC Thunderbolt HTC Touch Pro 2 HTC Evo 4G HTC Evo 3D Samsung Galaxy S II Motorola Droid X Apple iPhone Blackberry
Go Back   PPCGeeks > Site Information > Site News & Announcements

Notices


Reply
 
LinkBack Thread Tools Display Modes
  #21 (permalink)  
Old 07-30-2007, 09:08 PM
davedenmark's Avatar
Lurker
Offline
 
Join Date: Jun 2007
Posts: 9
Reputation: 0
davedenmark is a n00b
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
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?]
Reply With Quote
  #22 (permalink)  
Old 07-30-2007, 09:26 PM
Wideawake's Avatar
Founder & Owner
Offline
Pocket PC: iPhone XS Max
Carrier: Sprint
Threadstarter
Location: Clermont, Florida
 
Join Date: May 2006
Posts: 5,577
Reputation: 4644
Wideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributions
Mentioned: 8 Post(s)
Tagged: 0 Thread(s)
Send a message via Skype™ to Wideawake
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
__________________
Please read this before posting.



Reply With Quote
  #23 (permalink)  
Old 07-30-2007, 09:33 PM
Wideawake's Avatar
Founder & Owner
Offline
Pocket PC: iPhone XS Max
Carrier: Sprint
Threadstarter
Location: Clermont, Florida
 
Join Date: May 2006
Posts: 5,577
Reputation: 4644
Wideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributions
Mentioned: 8 Post(s)
Tagged: 0 Thread(s)
Send a message via Skype™ to Wideawake
I also mad a few adjustments by deleting unactivated users, taken down the blogs, stopping guests search, and implemented a new CAPTCHA system.
Reply With Quote
  #24 (permalink)  
Old 07-30-2007, 09:53 PM
davedenmark's Avatar
Lurker
Offline
 
Join Date: Jun 2007
Posts: 9
Reputation: 0
davedenmark is a n00b
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
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.
Reply With Quote
  #25 (permalink)  
Old 07-30-2007, 11:04 PM
boyo's Avatar
PPCGeeks Regular
Offline
Pocket PC: PPC-6700 / Mogul
Carrier: Sprint
 
Join Date: Feb 2007
Posts: 53
Reputation: 10
boyo is a n00b
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
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.
Reply With Quote
  #26 (permalink)  
Old 07-30-2007, 11:19 PM
Trident's Avatar
The Source of Power
Offline
Pocket PC: EVO4G & EPIC4g
Carrier: Sprint
Location: Disney World
 
Join Date: Aug 2006
Posts: 2,337
Reputation: 1485
Trident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on repTrident is halfway to VIP status based on rep
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Send a message via Skype™ to 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.
__________________
The only way of discovering the limits of the possible is to venture a little way past them into the impossible.
Any sufficiently advanced technology is indistinguishable from magic.


Reply With Quote
  #27 (permalink)  
Old 07-30-2007, 11:27 PM
Wideawake's Avatar
Founder & Owner
Offline
Pocket PC: iPhone XS Max
Carrier: Sprint
Threadstarter
Location: Clermont, Florida
 
Join Date: May 2006
Posts: 5,577
Reputation: 4644
Wideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributions
Mentioned: 8 Post(s)
Tagged: 0 Thread(s)
Send a message via Skype™ to Wideawake
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
Reply With Quote
  #28 (permalink)  
Old 07-30-2007, 11:28 PM
Wideawake's Avatar
Founder & Owner
Offline
Pocket PC: iPhone XS Max
Carrier: Sprint
Threadstarter
Location: Clermont, Florida
 
Join Date: May 2006
Posts: 5,577
Reputation: 4644
Wideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributions
Mentioned: 8 Post(s)
Tagged: 0 Thread(s)
Send a message via Skype™ to Wideawake
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
Reply With Quote
  #29 (permalink)  
Old 07-30-2007, 11:56 PM
Wideawake's Avatar
Founder & Owner
Offline
Pocket PC: iPhone XS Max
Carrier: Sprint
Threadstarter
Location: Clermont, Florida
 
Join Date: May 2006
Posts: 5,577
Reputation: 4644
Wideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributionsWideawake should be added to the payroll for their contributions
Mentioned: 8 Post(s)
Tagged: 0 Thread(s)
Send a message via Skype™ to Wideawake
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
Reply With Quote
  #30 (permalink)  
Old 07-31-2007, 12:20 PM
jamesus's Avatar
VIP Member
Offline
Pocket PC: HTC Mogul, HTC Apache
Carrier: Sprint
Location: Chicago, IL
 
Join Date: Sep 2006
Posts: 3,345
Reputation: 556
jamesus knows their stuffjamesus knows their stuffjamesus knows their stuffjamesus knows their stuffjamesus knows their stuffjamesus knows their stuff
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Send a message via AIM to jamesus Send a message via Yahoo to jamesus
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!
__________________
Device History: PPC 6800 (Mogul) < PPC 6700 < PPC 6600 < iPAQ 2200 Series < Palm 505 < Pen & Paper





A mass of tears have transformed to stones now sharpened on suffering and woven into slings...
Reply With Quote
Reply

  PPCGeeks > Site Information > Site News & Announcements

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT -4. The time now is 04:36 AM.


Powered by vBulletin® ©2000 - 2024, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.6.0
©2012 - PPCGeeks.com