UBB.threads PHP Forum Software Community Forums UBB.threads Version 7/8 Support Slow Queries

My forums are still running very slowly so I'm trying a variety of things, including logging slow queries. I occasionally get one like this -

# Time: 121205 3:39:37
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 11.296875 Lock_time: 0.031250 Rows_sent: 200 Rows_examined: 2526
use wine;
SET timestamp=1354696777;
SELECT p.POST_ID
FROM ubbt_POSTS p LEFT JOIN ubbt_POSTS pp on p.POST_ID=pp.POST_ID,
ubbt_TOPICS t
WHERE p.POST_IS_APPROVED = '1'
AND t.TOPIC_STATUS <> 'M'
AND t.FORUM_ID IN ('1','2','3','4','35','36','9','34','37','39','38','40')
AND p.TOPIC_ID = t.TOPIC_ID
AND MATCH (p.POST_SUBJECT,p.POST_DEFAULT_BODY) AGAINST ('eifrxxjofjousp, <a href=\"http://johannagonzalez.com/\">Online Casino</a>, AdhYOwh, Online casino cribbage, fiFOEJN, http://johannagonzalez.com/ Online casino us slot invaders moolah, DwGkfCT, <a href=\"http://vigrx2k.com/\">Does vigrx really work</a>, eTRCYvn, Vigrx suit, lVAymLd, http://vigrx2k.com/ Vigrx vs extagen, EzoPDHu, <a href=\"http://cnbook.org/\">Buy cheap viagra online uk</a>, oHZxUMG, Viagra alternative, KeiQGND, http://cnbook.org/ Viagra suppliers in the uk, pjUqTMs, <a href=\"http://searchuktravel.com/\">HCG</a>, GavChGj, Mushrooms on hcg diet, LEYaNFp, http://searchuktravel.com/ Hcg diet recipes, LxnKRRW, <a href=\"http://johnniesbaseball.com/\">Free online casino slot games</a>, dPpSxTV, Up bonus online casino, KdCltEk, http://johnniesbaseball.com/ Virtual online casino, YMnYNxO, <a href=\"http://capsiplextreatment.com/\">Capsiplex</a>, KPUsBOs, Trackback lascia un commento capsiplex in usa, fLyKZyB, http://capsiplextreatment.com/ Capsiplex, HGTWReR.' IN BOOLEAN MODE)

ORDER BY p.POST_POSTED_TIME DESC
LIMIT 200;

I imagine this is spam I don't want to deal with. I thought I remembered a way to have only logged in users able to do searches. Was that my imagination or is that a real feature? I run a spammer-blocker add-on so I imagine that would help a fair amount.

OK I found it, just took a while. So I will turn that off in my forums, that might help a bit.

At this point I have upgraded MySQL to the latest, upgraded PHP to the latest in its line, added FastCGI, and converted one of my forums to InnoDB (all except the posts table which wouldn't convert). It seems to be running without any errors.

I'll test for a day and then tomorrow night I'll convert the others to InnoDB. Somehow I need to get these sped up a bit.

the search has two permissions under:
Control Panel » Site Permissions
Oh, Nevermind you found it I guess

My changes do seem to be helping. The server used to be flat-line pegged at 100% and now it at least has breaks in the pegging. So progress is being made. I have a dedicated server so it's all mine. On the downside it only has 2g of memory and that's its max. I would have to pay more to go to a new server and I can't afford that right now. So I am trying to squeeze the most I can out of this one.

I'm going to upgrade to the latest version of UBB. Then I'm going to upgrade from the PHP 5.3 line to the PHP 5.4 line and see if that helps.

InnoDB won't help, unless the software takes advantage of it. We aren't doing 'transactions', which InnoDB does MUCH better at. Also record level locking versus table level.

UBB and most forum software is heavily read intensive and MyISAM does that well..

Where it falls down is in the queries you mentioned above... the new 7.5.7 limits the query length to 50 characters ( reasonable value ) and discards crap like that one query was doing..

That alone will help huge...

Also, there are really two terribly inefficient queries that kill mysql.. They happen when there are very long topics ( lots of pages, so ubb uses an offset, which sucks ) and very large forum ( also lots of pagination )...

So just cleaning up showflat and postlist would take a HUGE load off any loaded server.

All in all, however.. RAM IS KING!! there is now way around that... to really run properly and with speed.. you should have enough ram to entirely cache your databases AND INDEXES into memory... whenever you have to hit the hard drive for any reason, you take two orders of magnitude hit in speed, if not more...

I never run with less than 8Gigs of ram ( a linux dedi with 8gb ram is $149/month )....

So favor RAM over CPU, if you are server shopping in the future and get as much RAM as possible... RAM RAM RAM

SD, how do you load MyISAM tables into memory? I thought you can only do that with innoDB?

it's not that you need to declare a whole bunch of 'HEAP' type tables, but as the MYD files are accessed, they are actually ( where available ) sucked into RAM and cached automagically...

linux does this for you...

now, what i do is at startup, do a command like "cp /path/to/all/my/*.MYD /dev/null"

that gets the ball rolling immediately...

with 32gigs of ram, i'm off and running then with the OS handling all the writebacks on change

if you every look at your server ram usage, after it has been running for a long time, you will notice that regardless of how much RAM you have, there seems to be a very high percentage 'in use'... i reality, the OS is just hedging it's bets and using RAM wherever possible...

right now, for example my 32 GIG ram server is 'using' 29Gigs, but not really, if you get my drift...

normally i would NEVER get a 32 gig ram server, but i got 'lucky' in that there was a bad MOBO that caused a lot of pain and i was repaid with 4 sticks of 8Gigs to 'make me go away and stop complaining'

bottom line, regardless of myisam or innodb.... RAM RAM RAM!!

get all you can and more.. it's cheap.... and it beats banging the HDD

Link to some MySQL geekiness that i mentioned in the above post

they talk about what i described and even have some stats and 'wow, that really works!!' kinda comments too

i've been doing this on all my servers to quickly 'warm' up the Mysql tables on startup... makes life easier with a lotta RAM RAM RAM tho!

if you are low on ram, then disregard what i said in this and the last post

you'll have to better use indexes and really have to TUNE my.cnf more

I'm paying $99/month and with finances right now I can't afford to upgrade. So I need to tune this to run as cleanly as possible with what I have, which is 2g RAM.

ubbt_POSTS table... keep it as lean ( prune old content if possible )...

or implement a custom solution, like creating a ubbt_POSTS_ARCHIVE table, where you send older posts to...

then a small modification to ubbt_TOPICS to add 1 field 'IS_ARCHIVE'....

that way the ubbt_POSTS table only has current posts and runs lickity split...

i've done this for a couple large forums and it works quite well...

all in all.... it can be done.... but if you can double 2GB to 4GB of RAM, you buy yourself a quick solution

I'd love to see archiving as an option of the stock software; I've seen people leave with huge forums just because of this :/.

I'll consider the archiving option.

I'm happy to report that I just upgraded tonight to PHP 5.4.9. So I'm now on the latest version of MySQL, the latest version of PHP, and am running FastCGI. I've upgraded to the latest version of UBB. I converted the tables to INNODB. With all those changes, my processor, which used to be pegged solidly at 100%, is now floating at around 50%. Yes it still pegs occasionally but that's fine. The improvement is substantial.

So everything is running fine on the latest php

Yes. I am now on PHP 5.4.9 on both of my servers and the forums haven't had any issues with it.

On the down side, while normal forum operations are now much faster, and the server isn't pegged constantly, making posts is still excruciatingly slow. It can literally take 90 seconds for a forum post to go in, and often the operation times out. Meaning I'd have to extend the wait time past 90 seconds - and how many people are going to wait 2+ minutes for a forum post to go in?

There's got to be some way to speed up the forum posts.

The BellaOnline forum has 54,000 members, 717,000 posts, and the database is 1.12 gig. When I upgraded to the latest version of MySQL I imported the database fresh, so it's nice and clean in terms of being laid onto the hard drive. It did speed up a bit when I went to InnoDB but clearly not enough.

Other operations are fast. It's just that post insert that drags on completely.

Thoughts? Is it all about that text index?

I think you're at the point of needing more ram to speed things up, perhaps dropping the indexes as sd said will help as well...

OK I looked in my slow query log and the three slow queries around the times of my timing-out-posts seem to have nothing to do with what I'm doing. They are all looking at the "General Parakeet Chat" area (forum 5) - which I'm not in. And two of them are ordering by username, which is confusing. If someone was looking at the forum area in general it would list items by date. What query lists by user name, for a given forum area?

So I'm not getting any sense, from the slow forum query log, what is slowing down my posts. I'll note the post *does* get in even though it times out. So it's something hanging it after the post goes in. Any ideas how to troubleshoot? Maybe something is simply locking up after the post is done?

Here are the three queries going on during my testing time.

# Time: 121217 1:28:32
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 15.328125 Lock_time: 0.000000 Rows_sent: 11 Rows_examined: 93778
SET timestamp=1355725712;
select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CREATED_TIME,t1.TOPIC_LAST_REPLY_TIME,t1.TOPIC_SUBJECT,
t1.TOPIC_STATUS,t1.TOPIC_IS_APPROVED,t1.TOPIC_ICON,t1.TOPIC_VIEWS,t1.TOPIC_REPLIES,t1.TOPIC_TOTAL_RATES,
t1.TOPIC_RATING,t3.USER_NAME_COLOR,t2.USER_MEMBERSHIP_LEVEL,t1.USER_ID,t1.TOPIC_IS_STICKY,t1.TOPIC_LAST_POSTER_ID,
t1.TOPIC_LAST_POSTER_NAME,t1.TOPIC_LAST_POST_ID,t1.TOPIC_IS_EVENT,t1.TOPIC_HAS_FILE,t1.TOPIC_HAS_POLL,t1.TOPIC_POSTER_NAME,t1.TOPIC_THUMBNAIL,t4.POST_BODY,t3.USER_GROUP_IMAGES
from ubbt_TOPICS as t1
left join ubbt_USERS as t2 on t1.USER_ID = t2.USER_ID
left join ubbt_USER_PROFILE as t3 on t1.USER_ID = t3.USER_ID
left join ubbt_POSTS as t4 on t1.POST_ID = t4.POST_ID
where t1.FORUM_ID = 5
and t1.TOPIC_IS_STICKY = '0'

AND t1.TOPIC_IS_APPROVED = '1'
ORDER BY t2.USER_DISPLAY_NAME asc
LIMIT 9860, 11;
# Time: 121217 1:40:56
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 10.859375 Lock_time: 0.000000 Rows_sent: 11 Rows_examined: 53307
SET timestamp=1355726456;
select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CREATED_TIME,t1.TOPIC_LAST_REPLY_TIME,t1.TOPIC_SUBJECT,
t1.TOPIC_STATUS,t1.TOPIC_IS_APPROVED,t1.TOPIC_ICON,t1.TOPIC_VIEWS,t1.TOPIC_REPLIES,t1.TOPIC_TOTAL_RATES,
t1.TOPIC_RATING,t3.USER_NAME_COLOR,t2.USER_MEMBERSHIP_LEVEL,t1.USER_ID,t1.TOPIC_IS_STICKY,t1.TOPIC_LAST_POSTER_ID,
t1.TOPIC_LAST_POSTER_NAME,t1.TOPIC_LAST_POST_ID,t1.TOPIC_IS_EVENT,t1.TOPIC_HAS_FILE,t1.TOPIC_HAS_POLL,t1.TOPIC_POSTER_NAME,t1.TOPIC_THUMBNAIL,t4.POST_BODY,t3.USER_GROUP_IMAGES
from ubbt_TOPICS as t1
left join ubbt_USERS as t2 on t1.USER_ID = t2.USER_ID
left join ubbt_USER_PROFILE as t3 on t1.USER_ID = t3.USER_ID
left join ubbt_POSTS as t4 on t1.POST_ID = t4.POST_ID
where t1.FORUM_ID = 5
and t1.TOPIC_IS_STICKY = '0'

AND t1.TOPIC_IS_APPROVED = '1'
ORDER BY t1.TOPIC_SUBJECT asc
LIMIT 9120, 11;
# Time: 121217 1:41:06
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 14.562500 Lock_time: 0.000000 Rows_sent: 11 Rows_examined: 93058
SET timestamp=1355726466;
select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CREATED_TIME,t1.TOPIC_LAST_REPLY_TIME,t1.TOPIC_SUBJECT,
t1.TOPIC_STATUS,t1.TOPIC_IS_APPROVED,t1.TOPIC_ICON,t1.TOPIC_VIEWS,t1.TOPIC_REPLIES,t1.TOPIC_TOTAL_RATES,
t1.TOPIC_RATING,t3.USER_NAME_COLOR,t2.USER_MEMBERSHIP_LEVEL,t1.USER_ID,t1.TOPIC_IS_STICKY,t1.TOPIC_LAST_POSTER_ID,
t1.TOPIC_LAST_POSTER_NAME,t1.TOPIC_LAST_POST_ID,t1.TOPIC_IS_EVENT,t1.TOPIC_HAS_FILE,t1.TOPIC_HAS_POLL,t1.TOPIC_POSTER_NAME,t1.TOPIC_THUMBNAIL,t4.POST_BODY,t3.USER_GROUP_IMAGES
from ubbt_TOPICS as t1
left join ubbt_USERS as t2 on t1.USER_ID = t2.USER_ID
left join ubbt_USER_PROFILE as t3 on t1.USER_ID = t3.USER_ID
left join ubbt_POSTS as t4 on t1.POST_ID = t4.POST_ID
where t1.FORUM_ID = 5
and t1.TOPIC_IS_STICKY = '0'

AND t1.TOPIC_IS_APPROVED = '1'
ORDER BY t2.USER_DISPLAY_NAME desc
LIMIT 9140, 11;

Here's a sample progression.

I visit a forum area on my LisaShea forums. With the debug info turned on, I see it draws in under a second. So super quick.

I then make a new post there. Looking at the server itself, it is only 33% in use. So it's not pegged at all.

90 seconds later, the post times out.

I then went to the forum main page. I get:

Generated in 93.087 seconds in which 92.896 seconds were spent on a total of 26 queries. Zlib compression disabled.

So it's as if whatever the post was doing then affected the whole forum.

I now look at the main forum page fresh. Now I get:

Generated in 0.127 seconds in which 0.013 seconds were spent on a total of 26 queries. Zlib compression disabled.

So I'm back to a quick load.

So something about the post operation is causing a serious hit to the forum.

OK this time I ran it while watching in MySQL Workbench.

When the post starts up, I get an entry for "update ubbt_TOPICS" - and it goes to a status of query end.

And it sits there.

other processes around it come and go, and it just sits and sits. It doesn't have a time. Just a zero.

Then I get a select post ID beneath it, and that one does increment a time. It got up to 110 in this screenshot.

And eventually my actual submission page times out, and then they all go away.



So what is going on with the "query end" status?

OK in researching Query End hang status, I saw several reports saying it had to do with multiple queries trying to simultaneously hit the same table. Maybe something in that script is not quite right, so that it's doing two things at once? Can someone look into that?

To solve it, I edited my my.ini in my MySQL directory and set the query_cache_size to 0. Once I did that, POOF, my posts go through instantly.

So my problem is solved in that sense, and it means I don't have the advantages of a query cache. So a small degradation to solve a huge hurdle, until we can figure out what is jamming up in that post script.

you are delaying the inevitable...

RAM RAM RAM

If I could upgrade the ram in these systems, I would. They are maxed out.

I haven't heard back from the hosting company about how much money it would cost to upgrade to completely new servers.

I'll note that if anything all the advice I read on the web about sql cache was to *drop* its value - not raise it - to help with this problem. The problem apparently isn't RAM here. It's that two SQL threads are in contention and are crashing into each other. If anything the advice said a larger cache created a larger opportunity for that crashing to occur and that reducing the RAM dedicated to the sql cache minimized the chances of this happening (because the cache would clear before the collision occurred). But they said in general this event was indicative of something wrong with the code, that there was that potential for two threads to hit the same area at the same time. That the code should ensure one process finished before the next began.

So in this specific case I don't think more RAM will help, since everyone is saying to reduce the RAM used, not increase it.

See here and then follow the link to the bug report -

http://serverfault.com/questions/176465/mysql-thread-get-stuck-in-end-state

Again removing the cache made the problem go away. So putting all load on active memory fixed the problem. So to me that doesn't sound like RAM was the bottleneck.

SD.. can you tell me where to that command string? I'd like to implement that on my server (Apache).

Thanks
Paul

You'd do it via the shell (command line) on your server.

Thanks Gizmo.. I should have been more clear. Where would I place that command string to have it executed every time the server is rebooted? I know I can do that from the shell command line... but the benefit of that operation would be undone by a reboot. I'm not super savy on Apache / Unix startup scripts. Looking for the magical spot to place that 'cp ... > dev/null' code string.

Thanks
Paul

In case anybody finds this thread and has similar problems, I ended up resolving my issues and now have a quick, happy forum with the 2 gig RAM. I list the steps I took to get it here -

Tuning Ideas for 2G RAM