vb.org Archive - View Single Post - How did you solve searching in large forums?

99Percent · #15 06-24-2006, 03:26 PM

Quote:

Originally Posted by Paul M

mySQL locks the table(s) with a read lock, obviously because it's reading them. That will prevent any write locks until the query has finished, and as I recall - depending on your settings - those queued write locks will then cause further read lock requests to queue, thus a query queue will build up.

But if a read lock is issued to a table, that doesn't prevent other reads from happening, only writes, no? So I don't understand why other people cannot still browse the forum while a user is doing a search for example. Only people who are making a post would have to wait until the read lock is cleared.

What we notice happens sporadically in our forum is that a surge of new Apache connections occurs. Here are several Apache Server Status just seconds apart which I managed to capture in real time just before the server crashed:

Code:

Current Time: Wednesday, 21-Jun-2006 16:49:28 CDT
Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT
Parent Server Generation: 0
Server uptime: 4 hours 51 minutes 34 seconds
Total accesses: 197069 - Total Traffic: 911.2 MB
CPU Usage: u4947.26 s378.21 cu.8 cs0 - 30.4% CPU load
11.3 requests/sec - 53.3 kB/second - 4848 B/request
115 requests currently being processed, 14 idle workers

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWW_WWWWWWWWWWWWWWW_WWWWWWRWWWW__WWWWW___W_______W
.................W..............................................
................................................................
................................................................
..............................

Code:

Current Time: Wednesday, 21-Jun-2006 16:49:54 CDT
Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT
Parent Server Generation: 0
Server uptime: 4 hours 52 minutes 1 second
Total accesses: 197189 - Total Traffic: 911.5 MB
CPU Usage: u4790.07 s365.54 cu.78 cs0 - 29.4% CPU load
11.3 requests/sec - 53.3 kB/second - 4847 B/request
139 requests currently being processed, 16 idle workers

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWW_WWWW
__WWW__W_WWWWWW_WW______W__.....................................
................................................................
................................................................
..............................

Code:

Current Time: Wednesday, 21-Jun-2006 16:50:20 CDT
Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT
Parent Server Generation: 0
Server uptime: 4 hours 52 minutes 26 seconds
Total accesses: 197264 - Total Traffic: 911.8 MB
CPU Usage: u4748.97 s361.83 cu.77 cs0 - 29.1% CPU load
11.2 requests/sec - 53.2 kB/second - 4846 B/request
170 requests currently being processed, 15 idle workers

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
_WWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWWW_WW___W__W_WW______.......
................................................................
................................................................
..............................

Code:

Current Time: Wednesday, 21-Jun-2006 16:50:47 CDT
Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT
Parent Server Generation: 0
Server uptime: 4 hours 52 minutes 54 seconds
Total accesses: 197299 - Total Traffic: 912.2 MB
CPU Usage: u4749.94 s361.89 cu.77 cs0 - 29.1% CPU load
11.2 requests/sec - 53.2 kB/second - 4848 B/request
199 requests currently being processed, 11 idle workers

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWWWWWWWWWWWWWWWWWWW
WWWWWW_W_W________..............................................
................................................................
..............................

Code:

Current Time: Wednesday, 21-Jun-2006 16:51:39 CDT
Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT
Parent Server Generation: 0
Server uptime: 4 hours 53 minutes 45 seconds
Total accesses: 197505 - Total Traffic: 913.5 MB
CPU Usage: u4754.06 s362.19 cu.77 cs0 - 29% CPU load
11.2 requests/sec - 53.1 kB/second - 4850 B/request
262 requests currently being processed, 14 idle workers

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWWWWWWWWWWWWCWWWWWWWWWWWWWWWW
WW____WW_W__WW______............................................
..............................

Code:

Current Time: Wednesday, 21-Jun-2006 16:52:10 CDT
Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT
Parent Server Generation: 0
Server uptime: 4 hours 54 minutes 17 seconds
Total accesses: 197650 - Total Traffic: 914.0 MB
CPU Usage: u4756.07 s362.31 cu.77 cs0 - 29% CPU load
11.2 requests/sec - 53.0 kB/second - 4849 B/request
327 requests currently being processed, 10 idle workers

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWCWWW__________.............

Code:

Current Time: Wednesday, 21-Jun-2006 16:56:06 CDT
Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT
Parent Server Generation: 0
Server uptime: 4 hours 58 minutes 12 seconds
Total accesses: 197721 - Total Traffic: 915.4 MB
CPU Usage: u4689.97 s356.97 cu.77 cs0 - 28.2% CPU load
11.1 requests/sec - 52.4 kB/second - 4854 B/request
350 requests currently being processed, 0 idle workers

WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
WWWWWWWWWWWWWWWWWWCWWWWWWWWWWW

And then the server status request through http also fails because all the Apache slots have been filled and are busy. At this point, only a restart of Apache service restores the server to normal.

During this time, I can access other non vBulletin pages of the server, while actual vBulletin pages remain hanging, so this indicates to me that its either a PHP or a mySQL problem which is causing the Apache connections to wait and surge. We can make this happening to a degree with a search but sometimes the connections surge to some and then it falls back to normal after a couple of minutes. So we suspect that with a specific type of search is how the server ultimately crashes.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01152 seconds Memory Usage 1,805KB Queries Executed 11 (?)
More Information
Template Usage: (1)SHOWTHREAD_SHOWPOST (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (7)bbcode_code (1)bbcode_quote (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_box (1)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (1)post_thanks_postbit_info (1)postbit (1)postbit_onlinestatus (1)postbit_wrapper (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit reputationlevel showthread	Included Files: ./showpost.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_postinfo_query fetch_postinfo fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showpost_start bbcode_fetch_tags bbcode_create postbit_factory showpost_post postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start showpost_complete
Messages: