MySQL Performance Problems

Neo · November 12, 2019, 9:19am

Just restarted MySQL a few times.

There seems to be a problem with MySQL performance because one table (our man page table) is too large and I need to move that table to a new database and out of the main forums DB.

That table is over 7 GB, bigger than the rest of the DB combined:

	neo_man_page_entry	347,938 rows 	MyISAM utf8_bin	size 7.1 GiB

This weekend I will move this table outside of the main forum DB and into it's only separate DB.

Hopefully, this will solve the performance issues; or at least lead me to a solution. I apologize for this MySQL performance problem; it's my fault for moving the man pages to the DB and preformatting the pages and keeping those preformatted pages in the DB as well; and hopefully moving this table outside of the main forum DB will help.

Thanks for your patience.

Neo · November 12, 2019, 9:48am

The other possibility is that the search bots from China which are attacking the site are the problem:

Currently Active Users: 4410

This is the highest number we have seen in a few years, and is out of the ordinary; so I will block those bots with some quick PHP blocking code (below) and see if that makes a difference.

Neo · November 12, 2019, 9:51am

Like so:


<?php
if (stripos($_SERVER['HTTP_USER_AGENT'], 'google') !== false) { // don't block googlebot
    define('NEO_SEND_SOME_BOTS_TO_403', false);
} else {
    define('NEO_SEND_SOME_BOTS_TO_403', true);
}

if (strlen($_SERVER['REMOTE_ADDR']) < 8) {
    $ip = '0.0.0.0';
} else {
    $ip = filter_var($_SERVER['REMOTE_ADDR'], FILTER_SANITIZE_STRING,FILTER_FLAG_STRIP_HIGH);
}

$checkbot = "SELECT bot_flag, country_iso2 FROM session WHERE country_iso2 IN ('RU','CN','KR','KP','UA','TW') AND userid = 0 AND host ='" . $ip . "' LIMIT 1";
$session_info = $vbulletin->db->query_first($checkbot);
if ($session_info['bot_flag'] == '1' && $session_info && NEO_SEND_SOME_BOTS_TO_403) {
    $file = '/var/log/apache2/debug/neo_global_country_bot_block_403.log';
    if ($neo_global['debug_block_with_403']) {
        error_log(date(DATE_RFC822) . ' SEND 403 Page: ' . $_SERVER['REMOTE_ADDR'] . ' Country: ' . $session_info['country_iso2'] . "\n", 3, $file);
    }
    header($_SERVER["SERVER_PROTOCOL"] . " 403 Forbidden", true, 403);
    include '/var/www/status/403.html';
    die;
}

Neo · November 12, 2019, 10:01am

Which leads to:

linux:# tail -50 /var/log/apache2/debug/neo_global_country_bot_block_403.log
Tue, 12 Nov 19 08:58:32 -0600 SEND 403 Page: 182.111.240.115 Country: CN
Tue, 12 Nov 19 08:58:32 -0600 SEND 403 Page: 117.70.39.98 Country: CN
Tue, 12 Nov 19 08:58:35 -0600 SEND 403 Page: 117.60.39.21 Country: CN
Tue, 12 Nov 19 08:58:37 -0600 SEND 403 Page: 116.16.180.11 Country: CN
Tue, 12 Nov 19 08:58:39 -0600 SEND 403 Page: 91.122.30.68 Country: RU
Tue, 12 Nov 19 08:58:41 -0600 SEND 403 Page: 91.122.30.68 Country: RU
Tue, 12 Nov 19 08:58:41 -0600 SEND 403 Page: 223.244.152.107 Country: CN
Tue, 12 Nov 19 08:58:45 -0600 SEND 403 Page: 49.79.192.171 Country: CN
Tue, 12 Nov 19 08:58:46 -0600 SEND 403 Page: 114.104.184.190 Country: CN
Tue, 12 Nov 19 08:58:46 -0600 SEND 403 Page: 114.104.184.190 Country: CN
Tue, 12 Nov 19 08:58:48 -0600 SEND 403 Page: 117.69.144.83 Country: CN
Tue, 12 Nov 19 08:58:51 -0600 SEND 403 Page: 183.166.135.238 Country: CN
Tue, 12 Nov 19 08:58:52 -0600 SEND 403 Page: 123.179.131.100 Country: CN
Tue, 12 Nov 19 08:58:53 -0600 SEND 403 Page: 117.67.130.87 Country: CN
Tue, 12 Nov 19 08:58:56 -0600 SEND 403 Page: 114.237.28.37 Country: CN
Tue, 12 Nov 19 08:58:57 -0600 SEND 403 Page: 113.78.65.242 Country: CN
Tue, 12 Nov 19 08:58:58 -0600 SEND 403 Page: 119.7.152.128 Country: CN
Tue, 12 Nov 19 08:59:03 -0600 SEND 403 Page: 112.194.204.125 Country: CN
Tue, 12 Nov 19 08:59:07 -0600 SEND 403 Page: 49.83.242.142 Country: CN
Tue, 12 Nov 19 08:59:09 -0600 SEND 403 Page: 113.103.120.149 Country: CN
Tue, 12 Nov 19 08:59:10 -0600 SEND 403 Page: 114.223.161.134 Country: CN
Tue, 12 Nov 19 08:59:13 -0600 SEND 403 Page: 182.111.241.132 Country: CN
Tue, 12 Nov 19 08:59:16 -0600 SEND 403 Page: 101.75.156.131 Country: CN
Tue, 12 Nov 19 08:59:20 -0600 SEND 403 Page: 163.179.62.115 Country: CN
Tue, 12 Nov 19 08:59:21 -0600 SEND 403 Page: 117.69.241.84 Country: CN
Tue, 12 Nov 19 08:59:25 -0600 SEND 403 Page: 121.56.213.32 Country: CN
Tue, 12 Nov 19 08:59:25 -0600 SEND 403 Page: 49.89.84.54 Country: CN
Tue, 12 Nov 19 08:59:25 -0600 SEND 403 Page: 182.101.203.34 Country: CN
Tue, 12 Nov 19 08:59:26 -0600 SEND 403 Page: 91.122.30.68 Country: RU
Tue, 12 Nov 19 08:59:31 -0600 SEND 403 Page: 223.215.56.73 Country: CN
Tue, 12 Nov 19 08:59:32 -0600 SEND 403 Page: 36.23.217.84 Country: CN
Tue, 12 Nov 19 08:59:32 -0600 SEND 403 Page: 223.215.175.173 Country: CN
Tue, 12 Nov 19 08:59:35 -0600 SEND 403 Page: 180.125.17.185 Country: CN
Tue, 12 Nov 19 08:59:36 -0600 SEND 403 Page: 36.6.149.145 Country: CN
Tue, 12 Nov 19 08:59:45 -0600 SEND 403 Page: 37.9.113.198 Country: RU
Tue, 12 Nov 19 08:59:52 -0600 SEND 403 Page: 115.237.149.248 Country: CN
Tue, 12 Nov 19 08:59:52 -0600 SEND 403 Page: 117.43.174.94 Country: CN
Tue, 12 Nov 19 08:59:53 -0600 SEND 403 Page: 114.239.150.74 Country: CN
Tue, 12 Nov 19 08:59:54 -0600 SEND 403 Page: 36.4.85.49 Country: CN
Tue, 12 Nov 19 09:00:10 -0600 SEND 403 Page: 141.8.188.14 Country: RU
Tue, 12 Nov 19 09:00:10 -0600 SEND 403 Page: 59.33.107.241 Country: CN
Tue, 12 Nov 19 09:00:12 -0600 SEND 403 Page: 119.5.152.119 Country: CN
Tue, 12 Nov 19 09:00:13 -0600 SEND 403 Page: 141.8.142.50 Country: RU
Tue, 12 Nov 19 09:00:15 -0600 SEND 403 Page: 223.215.56.88 Country: CN
Tue, 12 Nov 19 09:00:20 -0600 SEND 403 Page: 114.98.173.136 Country: CN
Tue, 12 Nov 19 09:00:27 -0600 SEND 403 Page: 180.95.169.52 Country: CN
Tue, 12 Nov 19 09:00:31 -0600 SEND 403 Page: 117.57.62.8 Country: CN
Tue, 12 Nov 19 09:00:41 -0600 SEND 403 Page: 49.87.135.33 Country: CN
Tue, 12 Nov 19 09:00:50 -0600 SEND 403 Page: 123.130.129.40 Country: CN
Tue, 12 Nov 19 09:00:50 -0600 SEND 403 Page: 60.168.86.163 Country: CN

Neo · November 12, 2019, 11:10pm

Added some quick code to turn on and off various "bot blocking code" based on the load average of the site.

Very simple example:

<?php
$quickload = getLoad();
if ($quickload > 6) {
    $checkbot = "SELECT bot_flag, country_iso2 FROM session WHERE country_iso2 IN ('RU','CN','KR','KP','UA','TW') AND userid = 0 AND host ='" . $ipFilter . "' LIMIT 1";

    $session_info = $vbulletin->db->query_first($checkbot);
    if ($session_info['bot_flag'] == '1' && $session_info && NEO_SEND_SOME_BOTS_TO_403) {
        $file = '/var/log/apache2/debug/neo_global_country_bot_block_403.log';
        if ($neo_global['debug_block_with_403']) {
            error_log(date(DATE_RFC822) . ' SEND 403 Page: ' . $_SERVER['REMOTE_ADDR'] . ' Country: ' . $session_info['country_iso2'] ." Load: ".$quickload. "\n", 3, $file);
        }
        header($_SERVER["SERVER_PROTOCOL"] . " 403 Forbidden", true, 403);
        include '/var/www/status/403.html';
        die;
    }
}

function getLoad()
{
  $string=file_get_contents('/proc/loadavg');
  $loadavg=explode(' ', $string);
  if($loadavg[0]){
        return $loadavg[0];
  }else{
        return 0;
  }       
}

Neo · November 13, 2019, 4:31am

Update:

Have made a few changes to help with the DB load during peak times.

Coped the huge 7 GB man page table over to another DB.
Moved three busy man page DB queries over to the new database and off the main forum DB.
On the last man page table still in use in the main DB, added some code to stop man pages from being served from the DB when the load is very high (serve from a unix command line function versus the DB).

Let's see today if things improve or not, today.

TODO:

Test current code and config.
Move the main man page DB query off the main forum DB and to the new DB.
Delete the man page table from the main DB (when all is tested and done).

Neo · November 14, 2019, 12:22am

Update:

Have completely moved all DB queries regarding man pages to a new database (outside of the main site DB).

After testing, will delete the table of man pages from the main DB.

Deleting this table from the main DB with reduce the size of the main DB by 75%. This, in turn, reduces the sizes of the daily backups and data dumps by 75%, etc. etc.

Note: Restarted MySQL a few times today.....

Neo · November 14, 2019, 4:08am

Update:

To be clear, the main reasons I put the man page table in a different database was to make the main database smaller (75%) smaller; and so the daily backups are faster and use less resources. These backups take up less disk space on both the main server and the backup server.

From a DB CPU perspective, there is little to no performance difference having one huge database or two smaller ones.

Neo · November 14, 2019, 12:21pm

Seems like the bulk of this MySQL performance issue is solved (fingers crossed).

Neo · November 15, 2019, 12:05am

Daily backups:

Before moving man pages to new database:

-rw-r--r-- 1 root root 1292500820 Nov 13 22:31 main_masterdump_2019-11-13-22.sql.gz

After moving man pages to new database:

-rw-r--r-- 1 root root  449734289 Nov 14 22:30 main_masterdump_2019-11-14-22.sql.gz

Roughly a 65% reduction in the size for full daily backups.

Neo · November 22, 2019, 6:14am

I just completed (for the most part) a database optimization which was slowing the site down.

In a nutshell, when I created some new code to mitigate against Google soft 404 "errors" (slim content) by adding matching man pages to posts with very little content, I used a "real time" FULLTEXT search of over 350K man pages, and this was causing some performance issues.

Plus, my new code worked, and Google search referrals have just about doubled since I created the solution, but the upside/downside was that the site traffic also doubled (which is a good thing) and the new queries were killing performance (a bad thing).

In the final optimization, I preprocessed all these matches and stored the results in the DB (the ids of all the man pages which match the discussion contents) so, the long MySQL queries doing these FULLTEXT NATURAL LANGUAGE searches on the fly were eliminated (right now about 99.5+ are gone, I have about a half a day of processing to get to 99.999 percent.

The performance improvement is remarkable and the site is much faster, and I have earned my black belt (or at least a brown belt) in MySQL performance tuning, after all these years.

Neo · December 8, 2019, 10:08pm

Following up on this after a few weeks of testing....

Looks like we have solved any mysql performance issues we had and all is running smooth and fast, as it should be.

There is a single fall back query related to the "similar man pages" and tag searches which has an under 0.4 second "slow query"; and I plan redesign that "fall back query" to get rid of that occasional delay which effects only non registered users and tag searches.

Neo · December 10, 2019, 4:13am

Mitigated this slow query for "tag search" and the associated "similar man page".

Still need fine tuning for bad spelling of tags, etc; but over all the performance is much better (and the quality / spelling of the tags issue it a different issue than DB query performance, generally).

Neo · December 11, 2019, 12:03am

Just shaved about 200ms from the mysql query used on the user badge page for each user by adding a field in the user database to store the total tag count for each user and moving that query to a daily cron file instead of updating this in real time.

<?php
$sql = "select userid, count(1)  as tagcount  FROM tagthread group by userid order by tagcount desc";
$taginfo = $vbulletin->db->query_read($sql);
while ($tag = $vbulletin->db->fetch_array($taginfo)) {
    $sql2 = "update user set tagcount = " . $tag['tagcount'] . " where userid = " . $tag['userid'];
    $vbulletin->db->query_write($sql2);
}

Neo · December 18, 2019, 11:23pm

FYI:

mysql> show variables like '%long_quer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.400000 |
+-----------------+----------+
1 row in set (0.01 sec)

ubuntu# mytop

MySQL on localhost (5.7.23)                                                                                                    load 0.97 1.18 1.38 2/1855 6627 up 0+21:55:36 [22:20:02]
 Queries: 29.3M    qps:  389 Slow:     0.0         Se/In/Up/De(%):    71/01/06/00 
 Sorts:    663 qps now:  291 Slow qps: 0.0  Threads:    3 (   1/  13) 71/01/06/00 
 Key Efficiency: 100.0%  Bps in/out: 75.9k/25.1M   Now in/out: 55.3k/19.8M

So, after a sampling of nearly 30M queries over the past day or so, no query was found over 0.40 sec.

Will reduce to 0.2 or 0.3 seconds at a later date (the next time I restart mysql) and test again.