|Tìm kiếm các bài viết theo từ khoá
||Liệt kê theo danh mục
|vB4Mance Part 3: Database performance, vB benchmark with Percona XtraDB / 5.5 / 5.1
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.
Chi tiết bài viết
Lần cập nhật cuối
23rd of July, 2012
|Ý kiến người dùng (57 Bình chọn)
Cảm ơn bạn đã đánh giá câu trả lời này.
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.
Presuming 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.
Wait, 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.
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
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.
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
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)
|File đính kèm
Không có File đính kèm nào được tìm thấy.