| View previous topic :: View next topic |
| Author |
Message |
MikeEby
Joined: 24 Jun 2007
Posts: 5142
Location: Osceola, Indiana
|
 Link Posted: Sat Oct 24, 2009 1:20 am Post subject: |
 |
|
WOW...AR is only showing 8455 post in the top 10...Weird thing is he still shows 13411 in this thread.
_________________
Doing HD since the last century!
|
|
| Back to top |
|
 |
Nashou66
Joined: 12 Jan 2007
Posts: 12837
Location: West Seneca NY
TV/Projector: Marquee 8000, 8500 And a 9500LC RetroIV , 2 Longbow 8500 Ultras(2004!!)Hd145's , Ampro 3600, a G90!!
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Sat Oct 24, 2009 1:34 am Post subject: |
|
|
| MikeEby wrote: | | WOW...AR is only showing 8455 post in the top 10...Weird thing is he still shows 13411 in this thread. |
Good catch. That's a bug. I should be displaying the total posts in the top 10 not the number of posts he has active in the database. A post is a post, even if it was nuked 3 years later.
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
km987654
Joined: 25 Jul 2007
Posts: 1549
Location: Australia
TV/Projector: Barco BG808s 9
|
| Link Posted: Sat Oct 24, 2009 8:13 am Post subject: |
|
|
|
Here is a different problem. Last week my messages inbox said something like 50% full. That has jumped to 216%. I mean how do you get to 216% anyway??
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Sat Oct 24, 2009 2:07 pm Post subject: |
|
|
| km987654 wrote: | | Here is a different problem. Last week my messages inbox said something like 50% full. That has jumped to 216%. I mean how do you get to 216% anyway?? |
Sorry - I should have mentioned something about this.
I've reduced the Inbox and Sent box from 200 to 50. The Savebox was reduced from 400 to 200.
This is why you all of a sudden have more than 100% in your Inbox.
We have an enormous amount of PMs: Almost 100mb worth or 1/5 of our database is PMs. Most of which are old messages that people don't care about. This is normal of course (I'm not pointing fingers  ) - People don't clean up until they're forced to clean up or the system does it automatically.
Note that messages have not been deleted. If you had more than 50 the messages are all still there. The only difference is that if you have more than 50 you can't go bigger, and new users will never be able go higher than 50.
Just like before, when you receive a new PM and have more than the max already, the oldest drops off the list. If you're above the max your inbox isn't truncated to 50 (I tested this). If you don't want the oldest message to drop off,move important messages to your Savebox.
We don't want to use the forum as an emailing service - if someone needs to talk to someone every day and go back and forth multiple times, use email.
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Sun Oct 25, 2009 7:38 pm Post subject: |
|
|
Update: I'm extremely interested in knowing know if anyone gets the 'phpBB error: Could not correct to the database' error now that I've done some cleanup.
I have some SQL optimizations to still go through so I'm *hoping* we won't see similar errors for many years without having to upgrade to $300-400/month servers.
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Mon Oct 26, 2009 8:01 pm Post subject: |
|
|
Ok, some more tests have shown that it's primarily the main forum page (http://www.curtpalme.com/forum) causing the issues. Whenever it's accessed both CPUs peg at 100% usage by the database application.
There's likely too much "stuff" on it due to all the enhancements I've done. Over time the amount of data has grown such that it's now an issue,
I'm going through the page with a fine toothed comb and running each query manually to see where things can be optimized. I've already fixed the "top 10 posters" listing and improved the response time for that query from 0.3s average down to 0.02s (15x speed improvement).
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
garyfritz
Joined: 08 Apr 2006
Posts: 7097
Location: Fort Collins, CO
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Tue Oct 27, 2009 4:50 pm Post subject: |
|
|
Ok, another optimization done that I think was *really* slowing down the main page which in turn causes a snowball effect for the rest of the forum.
This time is the add-on mod that is used on in the main page to show the thread title in the "last post" section of each forum.
Old code:
| Code: | // RESPONSE TIME SLOW: Showing rows 0 - 29 (31 total, Query took 0.63 sec)
SELECT f.*, p.post_time, p.post_username, u.username, u.user_id, t.topic_title, t.topic_last_post_id, t.topic_id
FROM ((( phpbb_forums f
LEFT JOIN phpbb_posts p ON p.post_id = f.forum_last_post_id )
LEFT JOIN phpbb_users u ON u.user_id = p.poster_id )
LEFT JOIN phpbb_topics t ON t.topic_last_post_id = p.post_id )
ORDER BY f.cat_id, f.forum_order |
New code:
| Code: | // RESPONSE TIME FAST: Showing rows 0 - 29 (31 total, Query took 0.0181 sec)
SELECT f.*, p.post_time, p.post_username, u.username, u.user_id, t.topic_title, t.topic_last_post_id, t.topic_id
FROM ((( phpbb_forums f
INNER JOIN phpbb_posts p ON p.post_id = f.forum_last_post_id )
INNER JOIN phpbb_users u ON u.user_id = p.poster_id )
INNER JOIN phpbb_topics t ON t.topic_last_post_id = p.post_id )
ORDER BY f.cat_id, f.forum_order |
Basically just changing the LEFT JOINS to INNER JOINS since the data will all be found within the next set (no need to join all values). Time required to process to transaction dropped from 0.63 seconds (average) to 0.0181 seconds (average). A x34 speed increase.
So as I'm suspecting, these changes are all in "unofficial" mods/adds that I added over the years coded by people who basically don't know what they're doing. Sheesh. The "core" phpbb code is sleek, quick and properly optimized.
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
Nashou66
Joined: 12 Jan 2007
Posts: 12837
Location: West Seneca NY
TV/Projector: Marquee 8000, 8500 And a 9500LC RetroIV , 2 Longbow 8500 Ultras(2004!!)Hd145's , Ampro 3600, a G90!!
|
| Link Posted: Tue Oct 27, 2009 7:38 pm Post subject: |
|
|
Not sure if this is related, but I have just started to get Junk Mail sent to my two e-mails simultaneously , i assume its from me being a vendor on the main page.
I sent you an e-mail also Kal.
It coincides with the move to the new server. any other vendors getting more junk mail?
Athanasios
_________________
Don't blame your underwear for your crooked ass~ unknown Greek philosopher
"The government can't control the economy without controlling the people" RR
One Smart Dog!!!
Tom Bauerle to Andre Cuomo" Elliot Spitzer Screwed some Prostitutes, you screwed the Constitution" On his call to Cuomo to Resign!!!
Marquee High Performance Bellows now shipping!!
Marquee Modifications and Performance Enhancement
Marquee C-element and Bellow removal
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Tue Oct 27, 2009 7:57 pm Post subject: |
|
|
| Nashou66 wrote: | Not sure if this is related, but I have just started to get Junk Mail sent to my two e-mails simultaneously , i assume its from me being a vendor on the main page.
I sent you an e-mail also Kal.
It coincides with the move to the new server. any other vendors getting more junk mail?
Athanasios |
No relation.
Anyone with an email posted publicly gets junk mail since 'bots harvest any emails posted publicly. If you want to sell things you have to post your email publicly. It's a catch-22. If you notice all the mail goes to your @curtpalme.com address which is then forwarded to your regular addresses.
I get about 300+ junk mails/day because of this.
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
Nashou66
Joined: 12 Jan 2007
Posts: 12837
Location: West Seneca NY
TV/Projector: Marquee 8000, 8500 And a 9500LC RetroIV , 2 Longbow 8500 Ultras(2004!!)Hd145's , Ampro 3600, a G90!!
|
|
| Back to top |
|
|
dturco
Joined: 06 Feb 2009
Posts: 3746
Location: Eastern Shore Maryland
TV/Projector: Runco DLP VX-3000i Marquee 9500 parts doner
|
| Link Posted: Tue Oct 27, 2009 9:56 pm Post subject: |
|
|
I don't know if this is a bug but at 5:45 I was editing a post and when I pushed submit "No post mode specified" came up and the screen froze for nearly a minute.
Dave
_________________
Firefly rules. Can't stop the signal.
http://www.hulu.com/firefly
|
|
| Back to top |
|
|
AnalogRocks
Moderator
Joined: 08 Mar 2006
Posts: 20964
Location: Toronto, Ontario, Canada
TV/Projector: Sony 1252Q, AMPRO 4000G
|
| Link Posted: Tue Oct 27, 2009 10:14 pm Post subject: |
|
|
| dturco wrote: | I don't know if this is a bug but at 5:45 I was editing a post and when I pushed submit "No post mode specified" came up and the screen froze for nearly a minute.
Dave |
No it's just you 
_________________
Tech support for nothing
CRT.
HD done right!
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Wed Oct 28, 2009 2:52 pm Post subject: |
|
|
Okidoki, so I've spent about 20 hours now doing some more optimization on the main page and looking at all of the other pages... The main page is definitely the culprit that brings everyone down. Every other page is fine. Queries run crazy fast on all the other page. The main page had a bunch of problems and since it's the main page, well, it gets loaded tons!
Originally everyone saw this:
| Quote: | | Loading the main page 10 times: CPU load: 7.21 MySQL CPU usage: 199%. Load on the server stayed high for a few minutes. |
This is not good. Every time the main page was being loaded, mySQL was hogging both CPUs at nearly capacity (200% would be full). If you get a bunch of loads almost at the same time mySQL was grinding to a halt as subsequent users had to wait... and if the queue was full, you'd get that "could not connect to database error".
So after a bunch of work:
Result now for Club Members that have "Recent Posts" listed on the main page:
| Quote: | Loading the main page 10 times: CPU load: 0.64 MySQL CPU usage: 74%. Load on the server went down very fast.
Loaded the main page 20 times: CPU load: 1.74 MySQL CPU usage: 135%. Load on the server went down very fast. |
Result now for non-Club Members/anonymous users that do not have "Recent Posts" listed on the main page:
| Quote: | Loading the main page 10 times: CPU load: 0.12 MySQL CPU usage: 64%. Load on the server went down very fast.
Loading the main page 20 times: CPU load: 0.32 MySQL CPU usage: 72%. Load on the server went down very fast. |
These are much better results. One of the problems was that I was calculating the "Recent Posts" list for *all* users every time regardless of if they were a Club Members or not. Not smart, considering that at any given time only about 1/4 of the online users are Club Members.
I still have some optimization to do with the "Recent Posts" list shown on the main page as it's about 20 times slower than the page that shows *all* recent posts. This doesn't make sense. I'll have to compare the two and see what kind of bonehead SQL the coder used on the main page.
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Wed Oct 28, 2009 8:20 pm Post subject: |
|
|
Shaved off another 0.3 seconds average of mySQL processing on the "Recent Topics" list on the main page. It's now 10 times faster:
| Quote: |
OLD QUERY - RESPONSE IS SLOW: 25 rows in 0.36 sec
SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username, f.forum_name, f.forum_id, t.topic_replies
FROM (phpbb_topics AS t, phpbb_posts AS p, phpbb_users AS u)
LEFT OUTER JOIN phpbb_forums f ON p.forum_id = f.forum_id
WHERE t.forum_id NOT IN (8)
AND t.topic_status <> 2
AND p.post_id = t.topic_last_post_id
AND p.poster_id = u.user_id
ORDER BY p.post_id DESC
LIMIT 25
NEW QUERY - RESPONSE IS x10 FASTER: 25 rows in 0.035 sec
SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username, f.forum_name, f.forum_id, t.topic_replies
FROM (phpbb_topics t, phpbb_posts p)
LEFT OUTER JOIN phpbb_posts p2 ON p2.post_id = t.topic_first_post_id
LEFT OUTER JOIN phpbb_forums f ON p.forum_id = f.forum_id
LEFT OUTER JOIN phpbb_users u ON p.poster_id = u.user_id
LEFT OUTER JOIN phpbb_users u2 ON u2.user_id = t.topic_poster
WHERE t.forum_id NOT IN (8)
AND p.post_id = t.topic_last_post_id
ORDER BY t.topic_last_post_id DESC
LIMIT 25 |
Maybe I should become a DBA!
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
MikeEby
Joined: 24 Jun 2007
Posts: 5142
Location: Osceola, Indiana
|
| Link Posted: Wed Oct 28, 2009 8:34 pm Post subject: |
|
|
Joins ROCK!
Mike
_________________
Doing HD since the last century!
|
|
| Back to top |
|
|
Nashou66
Joined: 12 Jan 2007
Posts: 12837
Location: West Seneca NY
TV/Projector: Marquee 8000, 8500 And a 9500LC RetroIV , 2 Longbow 8500 Ultras(2004!!)Hd145's , Ampro 3600, a G90!!
|
| Link Posted: Fri Oct 30, 2009 1:43 am Post subject: |
|
|
Curt i am not seeing the number of users in the chat room, i was in there and so was another person and when i refreshed the main page it showed no numbers? and at the bottom of the forum page it says chat room empty even though i am in there, any ideas?
Nashou
_________________
Don't blame your underwear for your crooked ass~ unknown Greek philosopher
"The government can't control the economy without controlling the people" RR
One Smart Dog!!!
Tom Bauerle to Andre Cuomo" Elliot Spitzer Screwed some Prostitutes, you screwed the Constitution" On his call to Cuomo to Resign!!!
Marquee High Performance Bellows now shipping!!
Marquee Modifications and Performance Enhancement
Marquee C-element and Bellow removal
|
|
| Back to top |
|
|
kal
Forum Administrator
Joined: 06 Mar 2006
Posts: 12332
Location: Ottawa, Canada
TV/Projector: JVC DLA-RS56
|
| Link Posted: Fri Oct 30, 2009 1:55 pm Post subject: |
|
|
| Nashou66 wrote: | Curt i am not seeing the number of users in the chat room, i was in there and so was another person and when i refreshed the main page it showed no numbers? and at the bottom of the forum page it says chat room empty even though i am in there, any ideas?
Nashou |
I fixed it.
Really strange. Some lines of code were commented out and I (for the life of me) don't ever remember doing this.
Kal
_________________
My basement/HT/bar/brewery build 2.0
|
|
| Back to top |
|
|
|
|
Forum FAQ
Search
Memberlist
Photo Albums
Register
Profile
Private Messages
Log in
