Tweet
Liked this post? Want to contribute vBulletin optimization / tech advice and knowledge? Please join Team Skunkworks on Twitter, http://www.twitter.com/INETSkunkworks.
In an earlier blog entry on “vB High Performance” for vBulletin4 I provided a general overview of why vBulletin4 database model is far superior to its predecessor(s), owed mostly to the changes that allow a painless conversion to InnoDB table storage engine in order to escape the inadvertent slowness and locked-table conditions that MyISAM storage is known for.
And after we've sorted out the differences between the MyISAM / InnoDB storage engines - the follow up was the hands-on vB4Mance blog entry - “How to Convert vBulletin4 to InnoDB Guide”, where I provided the exact queries and other fine examples needed to be executed for a successful InnoDB conversion.
dsvrl.jpgPresuming that after reading the vB4Mance blog entries you suddenly felt enlightened, perhaps even inspired to successfully convert your site to use InnoDB – you now may be asking yourself – “What’s next?” Rest assured that after tweaking and tuning MySQL configuration (my.cnf), converting to InnoDB, installing vBulletin 4 Sphinx Search, I too sat perched high upon a mountain of available choices contemplating the next steps in vBulletin 4 optimization. Seemingly, web-server optimization appeared to be the next logical step in my conquest for vBulletin high-performance. However, my focus was suddenly drawn to further experimentation with database performance using the Percona MySQL Server with XtraDB. It was getting tremendous reviews from the MySQL-dev community – why not try it?
First and foremost however – you are probably wondering as to what Percona XtraDB is exactly. Let’s briefly talk about it -
What is Percona MySQL / XtraDB plugin and how is it different from the MySQL server that I am currently using? (Wikipedia: http://en.wikipedia.org/wiki/XtraDB)
In technical terms: XtraDB is a fork of the InnoDB storage engine for MySQL. It improves performance and scalability on modern hardware and includes a variety of other features useful in high performance environments. It is backwards compatible, and can be used as a drop-in replacement for standard InnoDB.
u12604425.jpgWait, what? Explain! Percona MySQL server with XtraDB is identical to the standard MySQL 5.1 server release and InnoDB storage engine plug-in. The difference is that the Percona team is known for high cutting edge database performance optimization and they’ve released their own optimized version of both the InnoDB plug-in (XtraDB) and MySQL 5.1 server. Think of this software stack as MySQL/ InnoDB on steroids. Percona MySQL server with XtraDB is not an add-on to your existing MySQL server – it is a complete replacement that requires a server rebuild or starting from scratch on a new machine. Because of its “backwards compatibility” – the changes are transparent – meaning that your backup strategy and everything else should still work. (Yes, I said “should”).
The timing for the MySQL 5.1 / XtraDB benchmark test worked out particularly well since MySQL 5.5 server (Beta) was released concurrently (no pun intended) and seemed to have offered significant performance increases and improvements over MySQL 5.1. Accordingly, MySQL 5.5 was thrown into the ring to fight till death against MySQL 5.1 and Percona XtraDB.
Getting Down to Business: Testing MYSQL 5.1/InnoDB versus MYSQL 5.5/InnoDB versus Percona Server with XtraDB.
With the three contenders for the title of the MySQL performance champion inside the ring, we kicked off the testing.
Now, I would like to be very upfront and honest about the test as to avoid hardcore-techies crying foul or accusing me of decisive misleading; I assure that such ill intention is not the case. While most variables were held constant during and in-between the tests – some variation was possible and admittedly the exact metrics are not 100% scientific although statistically correct within the constraints of our test and environment. On a personal note, I am happy with the validity of these results with the full understanding of these small deviations in the numbers. More so I’d like to stress that we wanted to get a general idea of the performance differential – which we were able to capture successfully; we wanted to prove the concept and generate discussion on this topic.
Test Environment
A quick word on my test server and vBulletin environment: the server is a mutli-cpu-core dedicated machine with 16G of available RAM; vBulletin test database consists of approximately 170,000 threads, almost 2 million posts.
Test #1
100 concurrent users online, executing queries against POST table; each user is generating 1 database “write” and 3 database “reads”. (1w3r @ 100). In other words; each of the 100 users is putting 1 item into the database and requesting 3 different items from the database at the same time.
Test #2
50 concurrent users online, executing queries against POST table; each user is generating 1 database “write” and 3 database “reads” – however, additional 50 concurrent users are generating 3 database reads; additionally – we are invalidating MySQL cache. (1w3R @ 50 + 3R @ 50, theoretically – 1w6R @ 50). In other words; there are 100 users online at the same time, 50 are reading 150 posts, 50 additional users are creating 50 posts while reading 150 posts, this is all happening at the same time.
The Big Question: How long do these requests take to complete in each of the three database server environments?
Test #1 Results:
Standard MySQL 5.1 with InnoDB: 1w3R @ 100C : 82 Seconds (Green Bar)
MySQL 5.5 Beta with InnoDB: 1w3R @ 100C: 40 Seconds ( Purple Bar)
Percona MySQL 5.1 Server / Patches / XtraDB: 1w3R @ 100C : 20 Seconds (Orange Bar)
Test #2 Results:
Standard MySQL 5.1 with InnoDB: 1w3R+3R @ 50/50C : 72 Seconds (Green Bar)
MySQL 5.5 Beta with InnoDB: 1w3R+3R @ 50/50C : 38 Seconds ( Purple Bar)
Percona MySQL 5.1 Server / Patches / XtraDB: 1w3R+3R @ 50/50C : 18 Seconds (Orange Bar)
H4YLl.gif
Interpreting the Results
The performance differences between the three competitors are drastic, especially as illustrated in a side-by-side comparison chart. In both of the tests, Percona MySQL Server + XtraDB emerged as the clear winner in the database server showdown. MySQL 5.5 Beta demonstrated a significant improvement in performance as compared to its standard 5.1 predecessor – but not nearly as much as XtraDB.
Additional Reading:
http://en.wikipedia.org/wiki/XtraDB
Liked this post? Want to contribute vBulletin optimization / tech advice and knowledge? Please join Team Skunkworks on Twitter, http://www.twitter.com/INETSkunkworks
The performance differences between the three competitors are drastic, especially as illustrated in a side-by-side comparison chart. In both of the tests, Percona MySQL Server + XtraDB emerged as the clear winner in the database server showdown. MySQL 5.5 Beta demonstrated a significant improvement in performance as compared to its standard 5.1 predecessor – but not nearly as much as XtraDB.
Additional Reading:
http://en.wikipedia.org/wiki/XtraDB
Liked this post? Want to contribute vBulletin optimization / tech advice and knowledge? Please join Team Skunkworks on Twitter, http://www.twitter.com/INETSkunkworks
