Sometimes people protest that they just don’t have time to benchmark the server for 8 or 12 hours at 10 different levels of concurrency on two or three server versions. If you don’t have the time to do the benchmarks right, any time you do spend is wasted; it is better to trust other people’s results, instead of doing an incomplete benchmark and getting the wrong answers. (查看原文)
The Cisco server has lower per-CPU performance than the EC2 servers. Surprised? We were a bit surprised ourselves.。。。Thus, the explanation for our results is probably as follows: the Cisco
server’s CPUs are a couple of years old, and are slower than the EC2 servers. But for
more complex tasks such as running a database server, the overhead of virtualization
places the EC2 servers at a disadvantage. It’s not always easy to distinguish between
slow CPUs, slow memory access, and virtualization overhead, but in this instance the
differences seem a bit clearer. (查看原文)
Amazon RDS isn’t the only DBaaS game in town for MySQL users. There are also
services such as FathomDB (http://fathomdb.com) and Xeround (http://xeround.com). (查看原文)
There are currently two solutions that take advantage of this by permitting so-called
NoSQL access to MySQL. The first is a daemon plugin called HandlerSocket, which
was created at DeNA, a large Japanese social networking site. It permits you to access
an InnoDB Handler object through a simple protocol. In effect, you’re reaching past
the upper layers of the server and connecting directly to InnoDB over the network.
There are reports of HandlerSocket achieving over 750,000 queries per second.
HandlerSocket is distributed with Percona Server, and the memcached access to InnoDB
is available in a lab release of MySQL 5.6.
The second option is accessing InnoDB through the memcached protocol. The lab releases
of MySQL 5.6 have a plugin that permits this. (查看原文)
As the creators of Percona Server, we’re biased to some extent, but we think this appendix
is fairly objective because we provide services, support, consulting, training,
and engineering for all of the variants of MySQL. We also invited Brian Aker and Monty
Widenius, who created the Drizzle and MariaDB projects, respectively, to contribute
to this appendix, so that it wouldn’t just be our version of the story. (查看原文)
MySQL, on the other hand, does offer choices. Its storage engines can implement their
own locking policies and lock granularities. Lock management is a very important decision
in storage engine design; fixing the granularity at a certain level can give better
performance for certain uses, yet make that engine less suited for other purposes (查看原文)
The locking style that offers the greatest concurrency (and carries the greatest overhead)
is the use of row locks. Row-level locking, as this strategy is commonly known, is
available in the InnoDB and XtraDB storage engines, among others. (查看原文)
Most of MySQL’s transactional storage engines don’t use a simple row-locking mechanism.
Instead, they use row-level locking in conjunction with a technique for increasing
concurrency known as multiversion concurrency control (MVCC). (查看原文)
The SHOW PROFILE command is a community contribution from Jeremy Cole that’s included
in MySQL 5.1 and newer, and some versions of MySQL 5.0. It is the only real
query profiling tool available in a GA release of MySQL at the time of writing. It is
disabled by default, but can be enabled for the duration of a session (connection) simply
by setting a server variable:
mysql> SET profiling = 1;
mysql> SELECT * FROM sakila.nicer_but_slower_film_list;
[query results omitted]
997 rows in set (0.17 sec)
The query returned 997 rows in about a sixth of a second. Let’s see what SHOW PRO
FILES (note the plural) knows about this query:
mysql> SHOW PROFILES;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------... (查看原文)
Using the Performance Schema
．．．
mysql> SELECT event_name, count_star, sum_timer_wait
-> FROM events_waits_summary_global_by_event_name
-> ORDER BY sum_timer_wait DESC LIMIT 5; (查看原文)
MySQL doesn’t always optimize correlated subqueries badly. If you hear advice to
always avoid them, don’t listen! Instead, measure and make your own decision. (查看原文)
Prepared statements have a few limitations and caveats:
• Prepared statements are local to a connection, so another connection cannot use
the same handle. For the same reason, a client that disconnects and reconnects
loses the statements. (Connection pooling or persistent connections can alleviate
this problem.)
• Prepared statements cannot use the query cache in MySQL versions prior to 5.1.
• It’s not always more efficient to use prepared statements. If you use a prepared
statement only once, you might spend more time preparing it than you would just
executing it as normal SQL. Preparing a statement also requires two extra roundtrips
to the server (to use prepared statements properly, you should deallocate them
after use).
• You cannot currently use a prepared statement inside a stored f... (查看原文)
Placing all of your application’s data in a single MySQL instance simply will not scale
well. Sooner or later you’ll hit performance bottlenecks. The traditional solution in
many types of applications is to buy more powerful servers. This is what’s known as
“scaling vertically” or “scaling up.” The opposite approach is to divide your work across
many computers, which is usually called “scaling horizontally” or “scaling out.” We’ll
discuss how to combine scale-out and scale-up solutions with consolidation, and how
to scale with clustering solutions. Finally, most applications also have some data that’s
rarely or never needed and that can be purged or archived. We call this approach “scaling
back,” just to give it a name that matches the other strategies. (查看原文)
作者: Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling 副标题: Optimization, Backups, Replication, and More isbn: 0596101716 书名: High Performance MySQL Second Edition 页数: 712 定价: USD 49.99 出版社: O'Reilly Media 装帧: Paperback 出版年: 2008-6-28
