Bug Database load increase

[janslu]

I've tried to transfer my forum from vbseo to dbseo tonight, but there was a significant increase in database load and had to revert the procedure. There was a huge spike in query numbers with dbseo and althought the queries themselves didn't seem to be very intensive - the sheer number of them caused problems. When I was checking the mytop console - I've seen multiple queries of pagination checks at the same time. I'm sorry I didn't copy the exact query, but it was something like:

count(ceiling(*/10) from vb_ where...

Judging by the munin graphs the increase in queries/sec was 1.5 - 3 times the vbseo load. At the moment there's approx 1100 users online and I have a lot of super long threads (spanning hundreds of pages).
Any way to optimize the paging lookup process?

 

[Fillip H.]

Please provide the exact query, otherwise I cannot provide any assistance

 

[janslu]

Here you go. mytop shows multiple queries being executed at the same time. None of them takes longer than a second, it's just that it's so many of them:

SELECT CEIL(COUNT(*) / 10) AS page FROM post WHERE postid <= XXX AND threadid = YYY

mysql> EXPLAIN SELECT CEIL(COUNT(*) / 10) AS page FROM vb_post WHERE postid <= 8105467 AND threadid = 26936;
+----+-------------+---------+------+------------------------------------------------------+----------+---------+-------+-------+-------------+
| id | select_type | table   | type | possible_keys                                        | key      | key_len | ref   | rows  | Extra       |
+----+-------------+---------+------+------------------------------------------------------+----------+---------+-------+-------+-------------+
|  1 | SIMPLE      | vb_post | ref  | PRIMARY,threadid,th_search,threadid_visible_dateline | threadid | 4       | const | 34115 | Using where |
+----+-------------+---------+------+------------------------------------------------------+----------+---------+-------+-------+-------------+

 

[Fillip H.]

I'll apply some caching to this in v1.0.2

 

[janslu]

Sorry for bothering, but there's no mention of caching changes in today's release notes. Did you implement it in 1.0.2?

 

[Fillip H.]

I did, yes

 

[janslu]

I have installed 1.0.2 on production server this night, and so far I'm still seeing significantly higher loads on database than with vbseo. Number of Select queries went up by the factor of 3 initially, then dropped down to lower levels (1,5x higher than avarage before), but the database server load is still 2,5x higher then before.
I'm also still seeing mostly the same query:

SELECT CEIL(COUNT(*) / 10) AS page FROM post WHERE postid <= XXX AND threadid = YYY

when viewing mytop. Do you think you can squezee some more performance from paging functions?

Edit: I've had to revert to vbseo. Number of queries wasn't that bad, but the load was rising very fast with the number of users online. I have almost 10 million posts in the database and it seems that these queries cause a significant load on the database. In addition to load - there was a huge increase on data being sent between db and web server.

 

[Fillip H.]

Do you have a caching method (except for Filecache) enabled in config.php? It's the line that reads for example

$config['Datastore']['class'] = 'vB_Datastore_APC';

If you don't, or it's commented out, then you will gain no benefit from caching and this would explain the high load.

If you had a caching method enabled in vBSEO, but not in the config.php file, this would explain the difference. There is nothing else different, and in fact DBSEO has fewer queries than vBSEO, so caching is the only thing that makes sense.

 

[janslu]

I'm using memcache datastore (two web servers) and have vbOptimize installed (no problems reported in vbOptimize).

I'm pretty sure the problem may be caused by my site's specifics. I have many VERY long threads (hundreds of pages each). The specific query

mysql> EXPLAIN EXTENDED SELECT CEIL(COUNT(*) / 10) AS page FROM vb_post WHERE postid <= 8105467 AND threadid = 26936 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vb_post
         type: ref
possible_keys: PRIMARY,threadid,th_search,threadid_visible_dateline
          key: th_search
      key_len: 4
          ref: const
         rows: 51060
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

seems to suggest that there's 51000+ posts in this specific threads for example (if I understand Explain correctly).

 

[Fillip H.]

So memcache is enabled via the line mentioned in the post above?

 

[janslu]

Yes!

$config['Datastore']['class'] = 'vB_Datastore_Memcached';
$i = 0;
// First Server
$i++;
$config['Misc']['memcacheserver'][$i]		= 'x.x.x.x';
$config['Misc']['memcacheport'][$i]			= 11211;
$config['Misc']['memcachepersistent'][$i]	= true;
$config['Misc']['memcacheweight'][$i]		= 1;
$config['Misc']['memcachetimeout'][$i]		= 1;
$config['Misc']['memcacheretry_interval'][$i] = 15;

vbOptimize

vB Optimise: Test Setup
Running Test: Connection to memcache
 Test completed successfully
Running Test: Store Cache
 Test completed successfully
Running Test: Fetch Cache
 Test completed successfully
Running Test: Flush Cache
 Test completed successfully

 

[Fillip H.]

In that case there's precious little more I can do, unfortunately. That query is required in order to get the "go to new post" to redirect to the correct page.

 

[janslu]

In that case there's precious little more I can do, unfortunately. That query is required in order to get the "go to new post" to redirect to the correct page.

Hi Fillip H.,
sorry for bumping an old topic. I just need to reevaluate my options. Are you certain you cannot resolve this issue? As I said these are rather powerful servers and vbseo is working fine on the same setup. If there's really nothing you can do to get dbseo working with 10 million posts, can you think of a 301 redirect from vbseo urls to the built in vbulletin ones? I really needed dbseo as a supported way to get out of vbseo death, and since it's not working on my site, I'm a litte lost.

 

[Fillip H.]

I have attempted to make further changes to eliminate the problematic query. Can you please re-download v1.0.5 and re-upload all files, and let me know if this fares better for you?

 

[janslu]

I will make a test once the traffic on my site eases down a little (in 2 hours).

 

[janslu]

I have attempted to make further changes to eliminate the problematic query. Can you please re-download v1.0.5 and re-upload all files, and let me know if this fares better for you?

I've installed freshly downloaded 1.0.5 while most of my users went to bed. I'm happy to announce the preliminary observations:

1. no significant impact on a database server! Number of queries stays approx the same as with vbseo. Only change is a spike in data transfer by mysql, but it's still relatively low amounts, so nothing to worry about.

2. There's a very high increase in memcache requests however - number of requests went up by 5x, network traffic by 2x. It didn't cause a significant load, but looking at the current numbers I'm a little afraid about the network interface interrupts caused by the memcache queries - it's close to 4500 requests/second in the lazy night time (up from below 1000). We'll see tomorrow.

Thank you for your work.

 

[Fillip H.]

Having lots of cache requests is a good thing

DBSEO employs caching way more liberally than vBSEO, because RAM cache lookups (for single-server setups, using APC or XCache) or Memcache server cache lookups (for multi-server setups with a dedicated, RAM-heavy server for MySQL / Memcache) is much faster than MySQL lookups.