This is the story of cold blocks and mismatched instances and how they will cause you pain and cost you money until you understand why. Most of the clients that we support run on the Amazon cloud using either RDS … Continue reading →
MySQL Fabric is a tool included on MySQL Utilities that helps you
to manage your MySQL instances.
It works by basically adding a new layer between your application
and MySQL instances, which can provide an easy way to use
sharding and build a high available system.
For More information about what is MySQL Fabric, please follow the documentation.
To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial:
fabric1 (192.168.0.200) - fabric mysql1 (192.168.0.201) - mysql master mysql2 (192.168.0.202) - mysql slave mysql3 (192.168.0.203) - mysql slave
Note: I’m running CentOS 6.5 on all servers.
1. Add mysql repo on all 4 machines, please read …
[Read more]On Wednesday I gave a presentation on “How to Avoid Even More Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides.
Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:
Q: Disk bandwidth also not infinite
Indeed, you’re right! …
[Read more]InnoDB has proven to be a reliable data storage engine for modern, high concurrency database systems. It is fully ACID compliant, and supports a wide range of isolation modes, from READ-UNCOMMITEED to SERIALIZABLE.
InnoDB multiversion concurrency control (MVCC) enables records and tables to be updated without the overhead associated with row-level locking mechanisms. The MVCC implementation in InnoDB largely eliminates the need to lock tables or rows during updates, and enables good performance for high concurrency workloads.
To learn more about this subject and related performance tuning topics tuning, take the MySQL Performance Tuning training course. This 4-day, instructor-led course is available as:
- …
In the latest release of MySQL Utilities 1.4.3 GA, the
performance of the mysqldbcompare utility has been considerably
improved along with some bug fixes. This blog briefly explains
some of the improvements that were made and shows evidence of the
increased performance of database comparison.
- A new step was added to the data consistency check. It now
executes a full table checksum, which is faster when no
differences are expected. The algorithm to find row differences
is only executed if this preliminary table
checksum fails.
- A new --skip-checksum-table option was added to skip this new step should you wish to (when you know there are differences it saves a bit of time).
- The current algorithm to find row differences was optimized to internally store and access the generated hash values.
What follows are some examples comparing the world database with …
[Read more]Optimization involves improving the performance of a database server and queries that run against it. Optimization reduces query execution time and optimized queries benefit everyone that uses the server. When the server runs more smoothly and processes more queries with less, it performs better as a whole. To learn more about how a MySQL developer can make a difference with optimization, take the MySQL Developers training course.
This 5-day instructor-led course is available as:
- Live-Virtual Event: Attend a live class from your own desk - no travel required. Choose from a selection of events on the schedule to suit different timezones.
- In-Class Event: Travel to an education center to attend an event. Below is a selection of the …
If you read Yoshinori's post about Semi-sync at Facebook, he lists the objective of using semi-sync as an alternative to running full durability on a master. That is to say that once you can guarantee writes have safely been shipped across the network, you may not strictly need to guarantee that they are safe locally.
This is something that I have been wanting to benchmark for a long time, and reading Jay's post about Semi-sync replication performance in MySQL 5.7 DMR4 and a conversation in last week's #dbhangops inspired me to explore this in more detail. For my tests, I will be using Master-Slave replication and three alternative definitions of durability:
- Local …
The following sentence is brought to you by IBM Legal: The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.
My previous post covered the work needed to get MySQL 5.6.17 running reliably on modern POWER systems. The patch to MySQL 5.6.17 that’s needed is available here.
For those who don’t know, POWER8 is the latest Power Architecture processors from IBM (my employer). These chips will be available in systems from IBM in June 2014 (i.e. Real Soon Now(TM)). There’s some fairly …
[Read more]One interesting feature of MySQL, is that it supports a very large number of integer data types. From the MySQL manual:
Type | Storage | Minimum Value | Maximum Value |
---|---|---|---|
(Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
TINYINT
|
1 |
-128
|
127
|
0
|
255
|
||
SMALLINT
|
2 |
-32768
|
32767 … |
In MySQL, query optimization of single-table UPDATE/DELETE statements is more limited than for SELECT statements. I guess the main reason for this is to limit the optimizer overhead for very simple statements. However, this also means that optimization opportunities are sometimes missed for more complex UPDATE/DELETE statements.
Example
Using the DBT-3 database, the following SQL statement will increase prices by 10% on parts from suppliers in the specified country:
UPDATE part SET p_retailprice = p_retailprice*1.10 WHERE p_partkey IN (SELECT ps_partkey FROM partsupp JOIN supplier ON ps_suppkey = s_suppkey WHERE s_nationkey = 4);
Visual EXPLAIN in MySQL Workbench shows that the optimizer will choose the following …
[Read more]