Some tuning tips from the MySQL experts

[KrON]

In an attempt to stay on top of our ever-busy forum, we recently enlisted the help of the mysqlperformanceblog.com guys (Peter, Vadim, and Aurimas) to come in, and help us figure out how to speed up vBulletin.

A bit of background to better help those reading this why this is necessary. Our forum (http://pbnation.com) is currently the 4th largest vBulletin forum (16th across all forums listed on big-boards.com) and is consistently in the top 10 as far as weekly posts are concerned.

We have been running vBulletin since the site started in 2001, and have always been pretty active in the community as far as performance optimizations, hacks, etc. At some point we had to start looking beyond the level of advice we could get from the community, and seek out professional help.

Enter the mysql guys. Peter was the former head of the mysql performance group (at mysql ab), and was the obvious choice. The following is some of the findings. I am sharing this information for two reasons. First, in hopes that those changes that are applicable to the majority of vB users will be considered for integration into a future release of vB, and second to help those of us who are struggling to keep our sites afloat.

So, lets get started here. Please note that a lot of this info will not pertain to most sites, as some of these problems only manifest themselves under heavy load.

First off, everyone attempting to profile slow queries in vB needs two tools to do so. If you are capable of compiling MySQL from source, grab the mysqlperformanceblog groups microslow patch, and the mysql slow query log analyzer. By setting your long_query_time to 0 with the microslow patch enabled, and letting it run for a day or so, you'll end up with one really giant slowqueries log file to parse. The slow query log tool produces (in order based on frequency) useful info like:

### 931000 Queries
### Total time: 67768.8358730002, Average time: 0.072791445620838
### Taking 0.000845 to 33 seconds to complete
### Rows analyzed 12 - 369

This is a perfect way to determine what is running slowly, how often it gets hit, and how many rows were analyzed. After we ran this setup for a day, we ended up with 13gb of sql queries that took 1 second or longer, with a few that really stood out.

1) The first:

SELECT postid
FROM post AS post
WHERE threadid = XXX
AND visible = XXX
ORDER BY dateline
LIMIT XXX, XXX;

This query gets hit quite a bit, and often does a filesort because the visible column doesn't contain an index. This is easily fixed by adding an additional index to the post table:

ALTER TABLE post ADD KEY `th_search` (`threadid`, `visible`, `dateline`);

2) This next query is a bit tricky, but just goes to show you how sometimes less isn't better. We have a hack that locks and archives old posts into post_ tables that are selected against using a MySQL merge table. So, even though we have 30 million posts, only about 10 million of those are active any given time in the "post" table. Even with only 10 million rows, this query is very slow:

SELECT
post.*, post.username AS postusername, post.ipaddress AS ip,
IF(post.visible = XXX, XXX, XXX) AS isdeleted,
user.*, userfield.*, usertextfield.*,
icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.userid) AS
hascustomavatar, customavatar.dateline AS avatardateline,
customavatar.width AS avwidth, customavatar.height AS avheight,
editlog.userid AS edit_userid, editlog.username AS edit_username,
editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason,
postparsed.pagetext_html, postparsed.hasimages,
sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline,
sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
IF(displaygroupid=XXX, user.usergroupid, displaygroupid) AS
displaygroupid, infractiongroupid,
'XXX' AS fieldXXX, 'XXX' AS fieldXXX
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid =
user.userid)
LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT
JOIN customavatar AS customavatar ON(customavatar.userid =
user.userid)
LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid
AND postparsed.styleid = XXX AND postparsed.languageid = XXX)
LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND
sigparsed.styleid = XXX AND sigparsed.languageid = XXX)
LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
WHERE post.postid IN (XXX,XXX,XXX,XXX,XXX,XXX,XXX,XXX,XXX)
ORDER BY post.dateline;

Why is it slow? On our server, the available memory necessary to hold all of these queries being executed simultaneously is not enough, and these queries often create temporary tables on disk to sort the results. We all know memory is faster than disk, so why not create a temporary table in memory, split the query up, and temporarily store the results there? This way, we're executing more queries, but by keeping less results in memory rather than hitting the disk. The result is less table locking, less disk I/O and the query is much faster overall. So, onto the fix:

a) First we create a temporary HEAP table:

CREATE TEMPORARY TABLE tmp_sort (
      `postid` int(10) unsigned NOT NULL,
      `dateline` int(10) unsigned NOT NULL
) ENGINE=HEAP;

b) Second, we insert the postids matched with post.postid IN (XXX,...,XXX) into the tmp_sort table:

INSERT INTO tmp_sort
SELECT postid, dateline
FROM post AS post
WHERE post.postid IN (XXX,XXX,XXX);

c) Third, we join against the tmp_sort table with the other joins in the query:

SELECT
post.*, post.username AS postusername, post.ipaddress AS ip,
IF(post.visible = XXX, XXX, XXX) AS isdeleted,
user.*, userfield.*, usertextfield.*,
icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.userid) AS
hascustomavatar, customavatar.dateline AS
avatardateline,customavatar.width AS avwidth,customavatar.height AS
avheight,
editlog.userid AS edit_userid, editlog.username AS edit_username,
editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason,
postparsed.pagetext_html, postparsed.hasimages,
sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline,
sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
IF(displaygroupid=XXX, user.usergroupid, displaygroupid) AS
displaygroupid, infractiongroupid,
'XXX' AS fieldXXX, 'XXX' AS fieldXXX
FROM tmp_sort AS tmp
LEFT JOIN post AS post ON(post.postid = tmp.postid)
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid =
user.userid)
LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT
JOIN customavatar AS customavatar ON(customavatar.userid =
user.userid)
LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid
AND postparsed.styleid = 35 AND postparsed.languageid = 1)
LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND
sigparsed.styleid = 35 AND sigparsed.languageid = 1)
LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
ORDER BY tmp.dateline;

3) The next query is like the last one. In this case executing three queries instead of one results in a speedup of 3-5x over the original query:

SELECT pm.*, pmtext.*, icon.title AS icontitle, icon.iconpath
FROM pm AS pm
LEFT JOIN pmtext AS pmtext ON(pmtext.pmtextid = pm.pmtextid)
LEFT JOIN icon AS icon ON(icon.iconid = pmtext.iconid)
WHERE pm.userid=XXX AND pm.folderid=XXX
ORDER BY pmtext.dateline DESC
    LIMIT XXX, XXX;

Again, this query doesn't look like much, but when you have a ton of PMs, and an active user base, this is quite the hog. The fix is three fold:

a) Create a heap table:

CREATE TEMPORARY TABLE tmp_pm (
      pmid int(10) unsigned NOT NULL,
      pmtextid int(10) unsigned NOT NULL,
      dateline int(10) unsigned NOT NULL
) ENGINE=HEAP;

b) Populate tmp_pm:

INSERT INTO tmp_pm
SELECT pm.pmid AS pmid, pm.pmtextid AS pmtextid, pmtext.dateline AS dateline
FROM pm AS pm
LEFT JOIN pmtext AS pmtext USING (pmtextid)
WHERE pm.userid = XXX AND pm.folderid = XXX;

c) Perform the select against the tmp table:

SELECT pm.*, pmtext.*, icon.title AS icontitle, icon.iconpath
FROM tmp_pm AS tmp
LEFT JOIN pm AS pm ON (tmp.pmid = pm.pmid)
LEFT JOIN pmtext AS pmtext ON(tmp.pmtextid = pmtext.pmtextid)
LEFT JOIN icon AS icon ON(icon.iconid = pmtext.iconid)
ORDER BY tmp.dateline DESC
LIMIT XXX, XXX;

4) PMs are a mess when you have millions of them. A simple change to the index (adding readtime to the index that is now just userid) speeds up this query quite a bit:

SELECT
SUM(IF(readtime <> XXX, XXX, XXX)) AS confirmed,
SUM(IF(readtime = XXX, XXX, XXX)) AS unconfirmed
FROM pmreceipt
WHERE userid = XXX;

The fix is simple:

ALTER TABLE pmreceipt DROP KEY `userid`, ADD KEY `userid` (`userid`,
`readtime`);

So, that was our initial round of changes. The performance benefit of all steps was clearly evident, but wasn't quite the boost we were looking for, so we went back for round two. Prior to round two, we were running some of our tables as InnoDB so we could get around the nasty row locking issues we had. Since we use the Sphinx search engine instead of the vBulletin engine, we didn't have to worry about losing the fulltext search functionality by switching.

Our initial round of changes had us switching the post, thread and user tables over to InnoDB. This got rid of the row locking we were seeing, but quickly gobbled up the 4gb of memory we had our innodb_buffer_pool_size set to, and was making optimization #1 from above slower than it could be. So, we took the plunge, upgraded our server to 16gb of ram from 8gb, and bumped our innodb_buffer_pool_size up to 8gb.

In addition, it became clear that there were more tables slowing us down due to locking issues. We found that switching the avatar, customavatar, editlog, icon, pm, pmtext and userfield tables to InnoDB helped clear up the locking issues a bit.

Furthermore, we also determined that the following query could benefit from an additional modfication of the stock index.

### 1256 Queries
### Total time: 7326.435305, Average time: 5.83314912818471
### Taking 0.007848  to 84.328134  seconds to complete
### Rows analyzed 3 - 21497
SELECT postid
                        FROM post AS post
                        INNER JOIN thread AS thread ON(thread.threadid = post.threadid)
                        WHERE post.userid = XXX
                        AND thread.forumid IN(XXX,XXX,XXX,XXX)
                        ORDER BY post.dateline DESC
                        LIMIT XXX;

In order to avoid more disk intensive filesorts, we found that extending the userid index to include the dateline speeds up the query which tends to get big, even with the LIMIT.

To remedy:

ALTER TABLE `post` DROP INDEX `userid`, ADD INDEX (userid, dateline);

We also identified a few queries that could benefit from some tuning as well, but that didn't have easy fixes (relating to pagination & limits on things like showthread). In addition, we got some MySQL conf tips that I'm not going to post here as they are sort of specific to the size of your data, and your hardware. Anyway, I'd be interested in hearing from those of you familiar with this sort of tuning.

Thoughts? Comments? Suggestions?

[chitchatter]

It is extremely good of you to share this experience.

1) The first:

SELECT postid
FROM post AS post
WHERE threadid = XXX
AND visible = XXX
ORDER BY dateline
LIMIT XXX, XXX;

This query gets hit quite a bit, and often does a filesort because the visible column doesn't contain an index. This is easily fixed by adding an additional index to the post table:

ALTER TABLE post ADD KEY `th_search` (`threadid`, `visible`, `dateline`);

I realised this. I had an index for this problem. I went and reconfirmed it after reading your post, and guess what? My index also includes the postid column after the dateline column. I wonder why? Let me investigate.

[ALanJay]

KRON thanks for this very interesting analysis which we will be studying in a lot more detail. I'm not sure exactly what your setup is on the hardware front but I was curious as to if you looked at the hard disks as a way of speeding things up?

We have seen a very busy periods issues with the database slowing down due to the queue of write lock requests driving the system to its knees - fortunately it has only happened when we have been extremely busy but still makes us want to improve things.

One of the thoughts we had was that a faster disk might help the situation?

Obviously we already have a pretty fast disk so the thought was to look at "Solid State Disks" did you look at these issues and decide against it as a course either for cost or practicality issues?

From the research I have done there are two types flash (nand) based and RAM based the latter being much more expensive. Although the former isn't cheap I wondered if you had considered it as a store for the databases?

[jason|xoxide]

This is interesting stuff. I've bookmarked this and will try it out on some of my larger boards tomorrow (2-4 Million posts each). Thanks for the info.

[telc]

They show in their website that Intel Compiled Mysql runs almost 2x faster then GCC compiled Mysql when run on a Intel processor. I have not benchmarked the Intel version, but I can going to soon.

Kron, what VB search method are you using on your forum?

[KrON]

It is extremely good of you to share this experience.



I realised this. I had an index for this problem. I went and reconfirmed it after reading your post, and guess what? My index also includes the postid column after the dateline column. I wonder why? Let me investigate.

It is possible that our indexes had gotten "out of sync" with the default Jelsoft ones, and this was fixed (or we were broken). I won't rule that out, but I believed we were pretty good about making sure they were the same.

[KrON]

They show in their website that Intel Compiled Mysql runs almost 2x faster then GCC compiled Mysql when run on a Intel processor. I have not benchmarked the Intel version, but I can going to soon.

Kron, what VB search method are you using on your forum?

We (up until about three weeks ago) always used the ICC compiled binaries. However the lag between community releases of MySQL and the enterprise versions forced us to start compiling our own MySQL versions. There were several bugs we were suffering from that were fixed in the enterprise versions, but were not contained in the most recent MySQL community release. If anyone is interested in more info about this, it is covered on the performance blog I linked to early around late december.

Peter asked us if we had actually benchmarked the ICC versions vs the GCC versions and I replied that I always just felt it was faster, but had no concrete numbers. He replied that in his experience, those clients that also used ICC binaries tended to encounter problems related to the ICC compiler that made them switch back.

Since ICC is a commercial compiler, and nobody that I've seen that has access to it is releasing up to date MySQL binaries, we'll continue to use homegrown binaries. MySQL has backed down from their whole "use our binaries, they are optimized" stance, and I'd rather be running the most recent MySQL version than rely on my gut feeling about the ICC.

If anyone is planning on doing the same, I can share the configure flags we use to compile MySQL, as I have cobbled together some that are based on the flags used to build the Redhat Linux versions and some based on advice from the MySQL performance guys.

As for the search, we use the Sphinx search hack pioneered by orban over at vb.org. It is pretty much amazing, and I can't suggest it highly enough. There is a minor bug in the current "version" with the annoying side effect of out of order search results, and it can't handle the "find all posts by user" searches yet, but it is soooo much faster than fulltext searching that we wouldn't dream of using anything else.

[KrON]

This is interesting stuff. I've bookmarked this and will try it out on some of my larger boards tomorrow (2-4 Million posts each). Thanks for the info.

I think you'll find that implementing the two three part query enhancements is quite easy, and will result in a nice speed up. Be forewarned however that changing the indexes on post (if we're not mixed up about what is in the default vB install) can take some time to do.

[KrON]

KRON thanks for this very interesting analysis which we will be studying in a lot more detail. I'm not sure exactly what your setup is on the hardware front but I was curious as to if you looked at the hard disks as a way of speeding things up?

We have seen a very busy periods issues with the database slowing down due to the queue of write lock requests driving the system to its knees - fortunately it has only happened when we have been extremely busy but still makes us want to improve things.

One of the thoughts we had was that a faster disk might help the situation?

Obviously we already have a pretty fast disk so the thought was to look at "Solid State Disks" did you look at these issues and decide against it as a course either for cost or practicality issues?

From the research I have done there are two types flash (nand) based and RAM based the latter being much more expensive. Although the former isn't cheap I wondered if you had considered it as a store for the databases?

We had considered this. Our primary DB server is a Quad CPU Dualcore 2.0ghz Opteron box with 3x250gb scsi disks in Raid 5 (I know), and 16gb of ram.

I realize that Raid 5 isn't really great as far as disk I/O is concerned, but looking at the info we graph, the read:write ratio is really low on the write/update side (at least compared to the selects), and I didn't think it would really help us THAT much. That, coupled with the fact that we lease our hardware (through our colo) and they don't actually offer SSD.

I could be totally wrong. Our DB server doesn't appear to really be I/O bound now that we've got so much memory, and have reasonably fast disks, so I'm hoping it won't come down to this being a problem.

At one point we had tried using the vBulletin master/slave functionality, but found that our slave server (Dual CPU Dualcore Opteron 2.0ghz w/ 2gb of ram, and non raided SCSI disks) would start lagging behind in replication, and users would freak out seeing stale data (like PMs showing up as unread when they were read).

We went so far as to hack our config.php to only toss read-only queries against the slave for certain pages to reduce the overall traffic, but it didn't help much.

I'm hoping that when we actually grow out of our current setup, we can scale horizontally and that a server identical in specs to our existing master database could keep the replication going under load.

[KrON]

chitchatter, I went and looked in install/mysql-scheme.php and see in the initial post table creation the keys are:

        PRIMARY KEY (postid),
        KEY userid (userid),
        KEY threadid (threadid, userid),
        FULLTEXT KEY title (title, pagetext)

So you must have added the postid column based on your own research?

[jason|xoxide]

I think you'll find that implementing the two three part query enhancements is quite easy, and will result in a nice speed up. Be forewarned however that changing the indexes on post (if we're not mixed up about what is in the default vB install) can take some time to do.

Yeah, and it takes me twice as long as most to alter the `post` table because it has to happen on the master and then replicate to the slave...

[adalren]

This looks very interesting. Can you share your MySQL my.cnf and compile flags?

I'll definately add/change the indexes. As for the 3 part queries, I'm not sure how it will work with a master/slave config. I'll have to do some testing.

Thank you for sharing.

[KrON]

This looks very interesting. Can you share your MySQL my.cnf and compile flags?

I'll definately add/change the indexes. As for the 3 part queries, I'm not sure how it will work with a master/slave config. I'll have to do some testing.

Thank you for sharing.

Here are my compile flags, use with a grain of salt (and adjust paths)

./configure \ 
--prefix=/path/to/mysql \
--host=x86_64-redhat-linux \
--mandir=/usr/share/man \
--infodir=/usr/share/info \
--localstatedir=/path/to/mysql/var \
--libexecdir=/path/to/mysql/bin \
--enable-thread-safe-client \
--enable-local-infile \
--with-pic \
--with-fast-mutexes \
--enable-shared \
--enable-threads=posix \
--with-system-zlib \
--enable-__cxa_atexit \
--disable-libunwind-exceptions \
--with-big-tables \
--with-readline \
--with-innodb \
--with-extra-charsets=all 'CC=gcc' 'CFLAGS=-O3 -unroll2'

As far as master/slave goes, it works just fine, provided you setup your slave not to replicate tables with the tmp_ prefix, otherwise your slave will be constantly creating tmp tables for no reason.

This can be done by adding the following to your slave server's my.cnf:

replicate-wild-ignore-table=vbdb.tmp_%

You obviously have to change vbdb to your database name. I think you could probably get away with just tmp_% but I'm not sure.

As far as my my.cnf goes, I will post it, but please take it with a grain of salt. Much of this information is very specific to our hardware, the size of our data, and our usage patterns. What works for us may not work for you. When we had the mysql guys looking at our configs, I was hoping they would tell me I was doing everything wrong, and provide us with fixes that would result in magical performance gains. I was wrong, and they told me that I was pretty much dead on (boohoo?).

Anyway, check out http://junglist.org/my.cnf if you are interested. Please note that I am willing to discuss these settings, but can't really provide hints specific to your setup

[chitchatter]

chitchatter, I went and looked in install/mysql-scheme.php and see in the initial post table creation the keys are:

        PRIMARY KEY (postid),
        KEY userid (userid),
        KEY threadid (threadid, userid),
        FULLTEXT KEY title (title, pagetext)

So you must have added the postid column based on your own research?

Yes, that is what I originally said. I added this index after I realised vb had a problem there. What I cannot remember is why I added the postid column there. Let me remove the postid column and check if there is any difference in performance. Will post here is there is anything significant to report.

[KrON]

Yes, that is what I originally said. I added this index after I realised vb had a problem there. What I cannot remember is why I added the postid column there. Let me remove the postid column and check if there is any difference in performance. Will post here is there is anything significant to report.

Oh, I misunderstood you, please do report back with your findings!