Announcement

Collapse
No announcement yet.

vB4Mance Part 3: Database performance, vB benchmark with Percona XtraDB / 5.5 / 5.1

Collapse
X
Collapse

  • vB4Mance Part 3: Database performance, vB benchmark with Percona XtraDB / 5.5 / 5.1

    Article Contributors: Mike Anders (IBxAnders), Pritesh Shah
    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://www.percona.com/software/percona-server/
    http://www.mysqlperformanceblog.com/...arks-15x-gain/
    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


    • Shamil.
      #22
      Shamil. commented
      Editing a comment
      Damn no Windows binary. I'll stick to MySql Oracle.

    • RedFoxy
      #23
      RedFoxy commented
      Editing a comment
      i installed tha db server on a test server but I cannot delete fulltext search from searchcore_* tables because innodb doesn't supports fulltext index, how can I change support from fulltext search to another supported by tha dbrm?

    • hugh_
      #24
      hugh_ commented
      Editing a comment
      Took the plunge a few days ago which went smoothly enough. One important consideration regarding innodb and percona, you're likely to need a new backup and restore policy. Make sure you have working procedures in place ASAP after the move to avoid unwanted complications...
    Posting comments is disabled.

Related Topics

Collapse

  • Kaelon
    MySQL 4.1.22 or MySQL 5?
    Kaelon
    Hi there,

    My dedicated servers (1 for database, 1 for Apache) are currently running on MySQL 4.1.22 (with PHP 5.2.5, 4 Gigs of RAM, Dual Intel Quad Xeon processors, and the latest versions...
    Tue 13th May '08, 8:44am
  • Karl Bambas
    MySQL version for vb3.7
    Karl Bambas
    I have been running vb3.5.1 OK using MySQL 4.0.27 on a shared server.
    Upgrading to vb3.7 the server has access to PHP5, but not to MSQL 5.0.51 as called for by the vb3.7 install notes.
    ...
    Sat 10th May '08, 5:51am
  • ChrisLM2001
    WHM/cPanel: Apache 2.0 and Mysql 5.x
    ChrisLM2001
    After some reading I learned that Apache 2.0 can break WHM/cPanel, and doesn't get updated (or can be rewritten by WHM's update).

    So how many vBulletin admins are using Apache 2.0 with Mysql...
    Yes -- WHM/cPanel -- Got it to work! Performance is great!
    50.00%
    1
    Yes -- WHM/cPanel -- Got it to work! Performance is mediocre.
    0.00%
    0
    Yes -- WHM/cPanel -- Got it to work! Performance is marred with problems (please explain).
    0.00%
    0
    No --- WHM/cPanel -- Doesn't work or have no need for it -- Please explain which.
    50.00%
    1
    Yes -- Direct Admin -- Performance is superb (and I even did my own tweaking!).
    0.00%
    0
    Yes -- Direct Admin -- Performance is equal to Apache 1.3 and Mysql 4.1 despite tweaks.
    0.00%
    0
    Yes -- Direct Admin -- Performance is full of errors to fix (please explain).
    0.00%
    0
    No --- Direct Admin -- Please explain why.
    0.00%
    0
    Yes -- Other CP -- Performance is terrific!
    0.00%
    0
    Yes -- Other CP -- Performance hasn't increased much.
    0.00%
    0
    Yes -- Other CP -- Performance has been a pain in the neck (please explain below)!
    0.00%
    0
    No --- Other CP -- Please explain why.
    0.00%
    0
    Mon 5th Dec '05, 1:52pm
  • arkadia2006
    mysql or mysqli ?
    arkadia2006
    In the config php i have this option :

    // ****** DATABASE TYPE ******
    // This is the type of the database server on which your vBulletin database will be located.
    // Valid...
    Sun 17th Sep '06, 3:07am
  • tpac
    Redhat Fedora Core 5 Database Connect Failure
    tpac
    Hello,

    I'm trying to install vBulletin v3.6.0 on a new install of Redhat Fedora Core 5 but I can't get past Step 2 (Connect to the database). It fails with the following error:
    ...
    Sat 30th Sep '06, 12:17pm
Working...
X