 |
 |
 |
 |
Registered: 06/05/06
Posts: 709
|
|
|
 |
 |
 |
 |
|
|
|
|
|
|
Express Hosting
"We are the official hosting company of UBB.threads. Ask us about our free migration services to migrate your UBB.threads installation."
|
|
|
|
|
|
|
|
|
|
|
#227435 - 07/01/09 01:22 PM
Re: Generation times and Ubbthreads logo appear in header, not footer - screenshot
[Re: Rick]
|
journeyman
|
Registered: 11/02/06
Posts: 91
|
|
Guys: I'm still having a lot of trouble with 7.53 since I upgraded last week. Normally, server load is between 0.10 and 0.50. Then, all of a sudden, load spikes to 3, 4, and this morning at 7:25-7:30 (far from peak time), to 6.20, and generation times to 300-400 seconds. Here's what the slow query log says:
ORDER BY p.POST_POSTED_TIME DESC
LIMIT 200;
# Time: 090701 7:27:20
# User@Host: XXXXXXXXXXXXXXXXXX @ XXXXXXXXXXXXXXXX []
# Query_time: 337 Lock_time: 322 Rows_sent: 1 Rows_examined: 272558
SELECT count(p.POST_ID)
FROM ubbt_POSTS p,
ubbt_TOPICS t
WHERE p.USER_ID IN ( 2, 247, 260, 289, 694, 1087, 1245, 2437, 2891, 3411, 6493, 6930, 7152, 8172, 8207, 8711, 11852, 12183, 12873, 14652, 14703, 22110, 22362 )
AND p.TOPIC_ID = t.TOPIC_ID
and t.FORUM_ID not in ('42','66','69');
Here's another at the same time:
# Time: 090701 7:27:05
# User@Host:XXXXXXXXXXXXXXXXXXXX @ XXXXXXXXXXXXXXXXXX []
# Query_time: 322 Lock_time: 0 Rows_sent: 200 Rows_examined: 3315585
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','5','6','7','8','9','10','11','12','13','14','15','16','17','18','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','43','44','45','46','47','48','49','50','51','52','53','55','56','57','58','59','61','62','63','64','65','67','68','70')
AND p.TOPIC_ID = t.TOPIC_ID
AND MATCH p.POST_SUBJECT AGAINST ('Columbia' IN BOOLEAN MODE)
AND p.POST_POSTED_TIME > '1183386103'
I have the mysql search enabled, not internal, I have results limited to 200, and the weird thing is, a week ago when I was still on 7.1, I did not have these spikes. Something happened when I went from 7.1 to 7.53, and now, intermittently, but regularly throughout the day, the board will go from humming along with a very low load to basically being locked down for minutes at a time. It's feast or famine! Server is a dual Opteron, 4 GB of RAM, 4 15K RPM SCSI with RAID 10. I'll take any help I can get. I'm willing to upgrade the server if necessary, but I can't believe that everything was humming along just fine with 7.1 and that 7.53 can't do the same with this server. Something, somewhere is not set right, I suspect, and I appreciate any help you fine folks can provide in helping me ID the problem.
|
|
Top
|
|
|
|
|
|
|
|
|
|
|
|
|
|
#227437 - 07/01/09 01:26 PM
Re: Generation times and Ubbthreads logo appear in header, not footer - screenshot
[Re: 24hourcampfire]
|
journeyman
|
Registered: 11/02/06
Posts: 91
|
|
Here's the current mytop status :
MySQL on localhost (4.1.22-standard-log) up 0+00:53:46 [10:06:38]
Queries: 385.4k qps: 122 Slow: 27.0 Se/In/Up/De(%): 45/02/08/00
qps now: 1 Slow qps: 0.0 Threads: 392 ( 391/ 0) 33/00/00/00
Cache Hits: 132.4k Hits/s: 42.0 Hits now: 0.0 Ratio: 76.1% Ratio now: 0.0%
Key Efficiency: 99.5% Bps in/out: 14.1k/257.3k Now in/out: 321.9/44.8k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
57689 root localhost mysql 0 Query show full processlist
57715 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 2 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USE
57714 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 5 Query SELECT USER_TEXT_EDITOR,USER_TOPICS_PER_PAGE,USER_POSTS_PER_TOP
57713 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 9 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USE
57711 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 11 Query select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CRE
57712 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 11 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57710 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 14 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57708 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 15 Query SELECT t.TOPIC_REPLIES, t.TOPIC_VIEWS, t.TOPIC_LAST_REPLY_TIME,
57709 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 15 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57698 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 18 Query select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CRE
57688 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 20 Query SELECT t2.USER_SHOW_AVATARS, t2.USER_TIME_FORMAT, t2.USER_IGNOR
57661 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 22 Query update ubbt_USERS set USER_SESSION_ID = '98dce83da57b0395e16346
57687 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 22 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USE
57662 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 27 Sleep
57669 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 27 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57658 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 29 Query SELECT t.TOPIC_REPLIES, t.TOPIC_VIEWS, t.TOPIC_LAST_REPLY_TIME,
57659 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 29 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57660 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 29 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57656 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 30 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USE
57657 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 30 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USE
57646 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 32 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57636 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 33 Query SELECT t2.USER_SHOW_AVATARS, t2.USER_TIME_FORMAT, t2.USER_IGNOR
57629 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 35 Query select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CRE
57618 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 37 Query select t1.USER_DISPLAY_NAME, t3.USER_LAST_VISIT_TIME, t1.USER_P
57613 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 39 Query SELECT t.TOPIC_REPLIES, t.TOPIC_VIEWS, t.TOPIC_LAST_REPLY_TIME,
57614 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 39 Query SELECT t.TOPIC_REPLIES, t.TOPIC_VIEWS, t.TOPIC_LAST_REPLY_TIME,
57611 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 41 Query SELECT t2.USER_SHOW_AVATARS, t2.USER_TIME_FORMAT, t2.USER_IGNOR
57612 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 41 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57609 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 42 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
57610 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 42 Query SELECT t.TOPIC_REPLIES, t.TOPIC_VIEWS, t.TOPIC_LAST_REPLY_TIME,
57566 XXXXXXXXXXXXXXXXXX localhost XXXXXXXXXXXXXXXXXX 48 Query SELECT t2.USER_TOPIC_VIEW_TYPE, t1.USER_ID, t1.USER_DISPLAY_NAM
|
|
Top
|
|
|
|
|
|
|
|
|
|
|
|
|
|
#227447 - 07/01/09 03:26 PM
Re: Generation times and Ubbthreads logo appear in header, not footer - screenshot
[Re: 24hourcampfire]
|
journeyman
|
Registered: 11/02/06
Posts: 91
|
|
Mysql tuner: [--] Up for: 2m 0s (17K q [144.108 qps], 2K conn, TX: 37M, RX: 2M) [--] Reads / Writes: 53% / 47% [--] Total buffers: 1.1G global + 5.4M per thread (600 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 4.3G (107% of installed RAM) [OK] Slow queries: 0% (0/17K) [OK] Highest usage of available connections: 1% (7/600) [OK] Key buffer size / total MyISAM indexes: 900.0M/880.9M [OK] Key buffer hit rate: 96.2% (212K cached / 8K reads) [OK] Query cache efficiency: 71.9% (5K cached / 7K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 464 sorts) [OK] Temporary tables created on disk: 1% (6 on disk / 367 total) [OK] Thread cache hit rate: 99% (7 created / 2K connections) [OK] Table cache hit rate: 91% (61 open / 67 opened) [OK] Open file limit used: 3% (113/3K) [OK] Table locks acquired immediately: 99% (5K immediate / 5K locks)
|
|
Top
|
|
|
|
|
|
|
|
|
|
|
Previous Topic
Index
