Planet MySQL

Prepared Statement Samples

One of our most useful features is Top Queries, which allows users to examine various metrics for families of similar queries. Once a query family is selected, we can drill down into its query samples. Individual samples are shown on a scatterplot. Selecting individual samples will display its captured SQL, latency, errors, and more.

We collect query samples by decoding the MySQL protocol over TCP and extracting the query text. Not only that, we also capture errors, warnings, and more by inspecting traffic over the wire.

Until now, query samples excluded prepared statements. This is because prepared statements are not sent over the wire as strings of SQL text. Statement preparation and execution is quite different from regular query execution, but in the end we generate a good approximation of what the statement SQL would look like and display it on the samples page.

Regenerating the statement text happens in three steps. First, we grab the statement text from a COM_STMT_PREPARE command sent from a client to the MySQL server. The server then sends the client a COM_STMT_PREPARE response with a statement ID. When a statement is to be executed, the client sends a COM_STMT_EXECUTE command to the server with the statement ID and its associated parameters. The parameters sent by the client are sent in the binary protocol format. When our agent sees a COM_STMT_EXECUTE, it decodes the parameters and does a string replace in the original statement text to approximate the SQL query text.

Regenerating SQL from prepared statement executions is not perfect, but should be very good. We understand the argument types, escape strings and text when necessary, and represent parameters in hex when we have to. You'll notice that the metric parameter is a quoted string, not a literal number. That's because we actually send that parameter in ASCII format, as text. The protocol doesn't lie!

Of course, we're showing a sample of SQL that never executed as displayed, which is kind of disingenuous, isn't it? But if you think about it for a moment, this is exactly what you get from sources like the slow query log, which also shows SQL that's reconstructed from the prepared statement with parameters substituted in. The main difference is that the slow query log doesn't tell you it's showing you SQL that never actually executed, whereas here the Action column is execute which tells you it's a prepared statement, not a simple query.

Astute readers will have noticed the EXPLAIN tab to the right of the SQL sample. That's an upcoming feature. Stay tuned for more on that!

If you are interested in seeing how this can benefit your company, please click below for a demo or here for a free trial.


PlanetMySQL Voting: Vote UP / Vote DOWN

Putting MySQL Fabric to Use: July 30 webinar

Martin and I have recently been blogging together about MySQL Fabric (in case you’ve missed this, you can find the first post of the series here), and on July 30th, we’re going to be presenting a webinar on this topic titled “Putting MySQL Fabric to Use.”

The focus of the webinar is to help you get started quickly on this technology, so we’ll include very few slides (mostly just a diagram or two) and then jump straight into shared screen mode, with lots of live console and source code examples.

In order to make the best use of time, we won’t show you how to install and configure MySQL Fabric. However, we can point you to a few resources to help you get ready and even follow our examples as we go:

  • The official manual is an obvious starting point
  • Our second post in the series includes configuration instructions
  • This git repo contains the test environment we’ll use to run our demos. Specifically, we’ll use the sharding branch, so if you intend to follow our examples as we go, we recommend checking that one out.

If you’re interested, you can register for this webinar here, and if there’s something specific you’d like to see (we had a request for PHP examples in the comments to our last post) feel free to post that as a comment. We can’t promise we’ll be able to cover all requests during the webinar, but we’ll incorporate examples to the repo as time allows.

Hope to see you then!

The post Putting MySQL Fabric to Use: July 30 webinar appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

How MariaDB makes Stored Procedures usable

I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.

First, SELECT is not the only SQL statement which returns a resultset. Other examples are DELETE RETURNING, CALL, SHOW, EXPLAIN and administrative commands like ANALYZE TABLE or CHECK TABLE. But these commands cannot be used in place of SELECT in the following contexts:

  • Subqueries, derived tables, JOINs, UNIONs
  • CREATE TABLE ... SELECT
  • INSERT/REPLACE INTO ... SELECT
  • CREATE VIEW ... AS SELECT
  • DECLARE CURSOR ... SELECT
  • SET @my_var := SELECT

This means that there is no way to read the output of these statements within a stored procedure or trigger.

Also, some statements cannot be executed in a stored procedures and/or in prepared statements. An example is CHECK TABLES: even if you don’t care about the resultset (which would be a serious limitation), you cannot execute it in a stored procedure or in a prepared statements.

MariaDB and MySQL support the DO statement, which is identical to SELECT except that it doesn’t return any result. However, stored procedures could return one or more resultsets too, and you cannot invoke them with DO. This means that in some contexts you may have to deal to annoying resultsets, or perhaps you cannot call those procedures (within triggers or functions).

I faced all these problems while developing STK/Unit and in other projects.

But MariaDB lets us workaround these limitations – and the funny thing is that probably its developers are not aware about this! Here is an example: CREATE OR REPLACE TABLE show_master_status ENGINE = CONNECT TABLE_TYPE = MYSQL SRCDEF = 'SHOW MASTER STATUS' CONNECTION = '_' ; DELIMITER || CREATE PROCEDURE test() BEGIN DECLARE eof BOOL DEFAULT FALSE; DECLARE v_file, v_position TEXT; DECLARE cur CURSOR FOR SELECT `File`, `Position` FROM show_master_status; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET eof := TRUE; END; OPEN cur; `lbl_loop`: LOOP FETCH cur INTO v_file, v_position; IF eof IS TRUE THEN LEAVE lbl_loop; END IF; SELECT CONCAT('I''ll do something with ', v_file, v_position); END LOOP; CLOSE cur; END || DELIMITER ; MariaDB [_]> CALL test(); +--------------------------------------------------------+ | CONCAT('I''ll do something with ', v_file, v_position) | +--------------------------------------------------------+ | I'll do something with binlog.00000242965 | +--------------------------------------------------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)

As you can see, with this trick I was able to use a cursor with SHOW MASTER STATUS.

How does the trick work? If you know CONNECT, probably you already guessed. CONNECT is a storage engine which allows the users to read data from several kind of data sources as if they were regular SQL tables. Supported data sources include several file formats, remote DBMS’s and more. In this case I defined a table which connects to a MariaDB/MySQL server (TABLE_TYPE=MYSQL). The server is… localhost. Since I specified a SRCDEF table option, the data source is not a table in the server, it is the resultset of the specified query (SHOW MASTER STATUS). This means that, when the table is queried, the CONNECT engine opens a connection to the local server, it executes SHOW MASTER STATUS, and it returns the results of that statement. But since this work is done by the CONNECT engine, from the MariaDB point of view we are just querying a table. So, we can use that query in all contexts where SELECT works.

Also note that we don’t have to specify the table structure. CONNECT supports table discovery, which means that it automatically knows which columns and types are needed.

Confused? I hope not. Look again at the example – everything will be clear.

Of course, having to CREATE a TABLE each time is not very comfortable, even with table discovery. So I wrote a stored procedure which does the boring work for us: DELIMITER || CREATE PROCEDURE `_`.`materialize_sql`(IN p_sql TEXT) MODIFIES SQL DATA BEGIN SET @v_materialized_sql := CONCAT_WS('', 'CREATE OR REPLACE TABLE `_`.`materialized_results`' , ' ENGINE = CONNECT' , ' TABLE_TYPE = MYSQL' , ' SRCDEF = ''', REPLACE(p_sql, '''', ''''''), '''' , ' CONNECTION = ''_''' ); PREPARE stmt_materialized_sql FROM @v_materialized_sql; EXECUTE stmt_materialized_sql; DEALLOCATE PREPARE stmt_materialized_sql; SET @v_materialized_sql := NULL; SELECT * FROM _.materialized_results; END || DELIMITER ;

Occasionally, this doesn’t work for me, and fails with a weird error about non-existing columns. I think that a bug affects this technique, but I wasn’t able to isolate it still. When I will find a stable way to reproduce the bug, I will report it. If you are able to isolate the bug before I do, please report it. But in the meanwhile, this technique seems to me usable in most cases. Test it with real queries, before using those queries in production. If it doesn’t work in your specific case, manually create the table. You can do it in a stored procedure, with an hardcoded query – not a prepared statement.

There is also a limitation you must be aware of: CONNECT doesn’t support BLOB and TEXT types. If you need a query which returns a BLOB or TEXT, you can probably convert it to VARCHAR, but this probably means that you can’t use table discovery. This limitation affects mainly ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE and REPAIR TABLE. All these statements returns the same columns, so I wrote a stored procedure and a table which handle them: -- contains resultsets for OPTIMIZE, ANALYZE, CHECK, REPAIR CREATE OR REPLACE TABLE `_`.`administrative_sql` ( `Table` VARCHAR(64) NOT NULL , `Op` VARCHAR(255) NOT NULL , `Msg_type` VARCHAR(255) NOT NULL , `Msg_text` VARCHAR(255) NOT NULL ) ENGINE = CONNECT TABLE_TYPE = MYSQL SRCDEF = 'CHECK TABLE t' CONNECTION = '_' ; DROP PROCEDURE IF EXISTS `_`.`administrative_sql`; DELIMITER || CREATE PROCEDURE `_`.`administrative_sql`(IN p_sql TEXT) MODIFIES SQL DATA BEGIN SET @v_administrative_sql := CONCAT_WS('', 'ALTER TABLE _.administrative_sql SRCDEF = \'', p_sql, '\''); PREPARE stmt_administrative_sql FROM @v_administrative_sql; EXECUTE stmt_administrative_sql; DEALLOCATE PREPARE stmt_administrative_sql; SET @v_administrative_sql := NULL; SELECT * FROM _.administrative_sql; END || DELIMITER ; MariaDB [_]> CALL administrative_sql('ANALYZE TABLE t'); +-------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------+---------+----------+-----------------------------------------+ | _.t | analyze | status | Engine-independent statistics collected | | _.t | analyze | status | OK | +-------+---------+----------+-----------------------------------------+ 2 rows in set (0.22 sec) Query OK, 0 rows affected (0.22 sec)

Enjoy!



PlanetMySQL Voting: Vote UP / Vote DOWN

DBaaS, OpenStack and Trove 101: Introduction to the basics

We’ll be publishing a series of posts on OpenStack and Trove over the next few weeks, diving into their usage and purpose. For readers who are already familiar with these technologies, there should be no doubt as to why we are incredibly excited about them, but for those who aren’t, consider this a small introduction to the basics and concepts.

What is Database as a Service (DBaaS)?
In a nutshell, DBaaS – as it is frequently referred to – is a loose moniker to the concept of providing a managed cloud-based database environment accessible by users, applications or developers. Its aim is to provide a full-fledged database environment, while minimizing the administrative turmoil and pains of managing the surrounding infrastructure.

Real life example: Imagine you are working on a new application that has to be accessible from multiple regions. Building and maintaining a large multiregion setup can be very expensive. Furthermore, it introduces additional complexity and strain on your system engineers once timezones start to come into play. The challenge of having to manage machines in multiple datacenters won’t simplify your release cycle, nor increase your engineers’ happiness.

Let’s take a look at some of the questions DBaaS could answer in a situation like this:

- How do I need to size my machines, and where should I locate them?
Small environments require less computing power and can be a good starting point, although this also means they may not be as well-prepared for future growth. Buying larger-scale and more expensive hardware and hosting can be very expensive and can be a big stumbling block for a brand new development project. Hosting machines in multiple DC’s could also introduce administrative difficulties, like having different SLA’s and potential issues setting up WAN or VPN communications. DBaaS introduces an abstraction layer, so these consideration aren’t yours, but those of the company offering it, while you get to reap all the rewards.

- Who will manage my environment from an operational standpoint?
Staffing considerations and taking on the required knowledge to properly maintain a production database are often either temporarily sweeped under the rug or, when the situation turns out badly, a cause for the untimely demise of quite a few young projects. Rather than think about how long ago you should have applied that security patch, wouldn’t it be nice to just focus on managing the data itself, and be otherwise confident that the layers beyond it are managed responsibly?

- Have a sudden need to scale out?
Once you’re up and running, enjoying the success of a growing use base, your environment will need to scale accordingly. Rather than think long and hard on the many options available, as well as the logistics attached to those changes, your DBaaS provider could handle this transparently.

Popular public options: Here are a few names of public services you may have come across already that fall under the DBaaS moniker:

- Amazon RDS
- Rackspace cloud databases
- Microsoft SQLAzure
- Heroku
- Clustrix DBaaS

What differentiates these services from a standard remote database is the abstraction layer that fully automates their backend, while still offering an environment that is familiar to what your development team is used to (be it MySQL, MongoDB, Microsoft SQLServer, or otherwise). A big tradeoff to using these services is that you are effectively trusting an external company with all of your data, which might make your legal team a bit nervous.

Private cloud options?
What if you could offer your team the best of both worlds? Or even provide a similar type of service to your own customers? Over the years, a lot of platforms have been popping up to allow effective management and automation of virtual environments such as these, allowing you to effectively “roll your own” DBaaS. To get there, there are two important layers to consider:

  • Infrastructure Management, also referred to as Infrastructure-as-a-Service (IaaS), focusing on the logistics of spinning up virtual machines and keeping their required software packages running.
  • Database Management, previously referred to DBaaS, transparently coordinating multiple database instances to work together and present themselves as a single, coherent data repository.

Examples of IaaS products:
- OpenStack
- OpenQRM

Ecample of DBaaS:
- Trove

Main Advantages of DBaaS
For reference, the main reasons why you might want to consider using an existing DBaaS are as follows:

- Reduced Database management costs

DBaaS removes the amount of maintenance you need to perform on isolated DB instances. You offload the system administration of hardware, OS and database to either a dedicated service provider, or in the case where you are rolling your own, allow your database team to more efficiently manage and scale the platform (public vs private DBaaS).

- Simplifies certain security aspects

If you are opting to use a DBaaS platform, the responsibility of worrying about this or that patch being applied falls to your service provider, and you can generally assume that they’ll keep your platform secure from the software perspective.

- Centralized management

One system to rule them all. A guarantee of no nasty surprises concerning that one ancient server that should have been replaced years ago, but you never got around to it. As a user of DBaaS, all you need to worry about is how you interface with the database itself.

- Easy provisioning

Scaling of the environment happens transparently, with minimal additional management.

- Choice of backends

Typically, DBaas providers offer you the choice of a multitude of database flavors, so you can mix and match according to your needs.

Main Disadvantages
- Reduced visibility of the backend

Releasing control of the backend requires a good amount of trust in your DBaaS provider. There is limited or no visibility into how backups are run and maintained, which configuration modifications are applied, or even when and which updates will be implemented. Just as you offload your responsibilities, you in turn need to rely on an SLA contract.

- Potentially harder to recover from catastrophic failures

Similarly to the above, unless your service providers have maintained thorough backups on your behalf, the lack of direct access to the host machines means that it could be much harder to recover from database failure.

- Reduced performance for specific applications

There’s a good chance that you are working on a shared environment. This means the amount of workload-specific performance tuning options is limited.

- Privacy and Security concerns

Although it is much easier to maintain and patch your environment. Having a centralized system also means you’re more prone to potential attacks targeting your dataset. Whichever provider you go with, make sure you are intimately aware of the measures they take to protect you from that, and what is expected from your side to help keep it safe.

Conclusion: While DBaaS is an interesting concept that introduces a completely new way of approaching an application’s database infrastructure, and can bring enterprises easily scalable, and financially flexible platforms, it should not be considered a silver bullet. Some big tradeoffs need to be considered carefully from the business perspective, and any move there should be accompanied with careful planning and investigation of options.

Embracing the immense flexibility these platforms offer, though, opens up a lot of interesting perspectives too. More and more companies are looking at ways to roll their own “as-a-Service”, provisioning completely automated hosted platforms for customers on-demand, and abstracting their management layers to allow them to be serviced by smaller, highly focused technical teams.

Stay tuned: Over the next few weeks we’ll be publishing a series of posts focusing on the combination of two technologies that allow for this type of flexibility: OpenStack and Trove.

The post DBaaS, OpenStack and Trove 101: Introduction to the basics appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Fabric – Part 1 – Installing

Send to Kindle

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 Installing latest version of MySQL via yum for more info:

rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update

2. Install mysql mysql-server mysql-utilities:

yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start

3. On mysql1,mysql2,mysql3 add the follow to my.cnf:

[mysqld] ... binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 report-host=192.168.0.201 report-port=3306 server-id=1 log-bin=mysql1-bin.log

4. On mysql1 add a replication user for each mysql ip:


GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; " [root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; " [root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "

5. On mysql1 add the privileges to fabric user from fabric node ip:


GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"

6. On mysql2 and mysql3, setup replication:

CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

7. On fabric1, add the fabric MySQL user:


GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';

[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"

8. On fabric1,, configure user and password on [storage] and [servers] group on /etc/mysql/fabric.cfg :

[storage] ... password = fabricpwd ... [servers] password = reppwd user = replication

9. On fabric1, start the fabric db, it will ask to create a password, this password will be used on all next mysqlfabric commands:


mysqlfabric manage setup

[root@fabric1 ~]# mysqlfabric manage setup [INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric). Finishing initial setup ======================= Password for admin user is not yet set. Password for admin/xmlrpc: Repeat Password: Password set.

10. On fabric1, start fabric:


mysqlfabric manage start &

11. On fabric1,, add a group:

mysqlfabric group create GLOBAL1

[root@fabric1 ~]# mysqlfabric group create GLOBAL1 Password for admin: Procedure : { uuid = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35, finished = True, success = True, return = True, activities = }

12. On fabric1,, add mysql1, mysql2 and mysql3 to GLOBAL1 group:


mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203

[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201 Password for admin: Procedure : { uuid = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202 Password for admin: Procedure : { uuid = c8babfb9-d836-44c0-b4fd-015cd1df8298, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203 Password for admin: Procedure : { uuid = c86bba70-69ac-4923-9c54-1a8aaab6d97e, finished = True, success = True, return = True, activities = }

13. On fabric1, get the uuid of your master:

mysqlfabric group lookup_servers GLOBAL1

[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1 Password for admin: Command : { success = True return = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}] activities = }

14. On fabric1, add your master as master on GLOBAL1 group:

mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'

[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242' Password for admin: [WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)). [INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242. Procedure : { uuid = 733ae69d-fb12-447b-b86b-041703491315, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1 Password for admin: Command : { success = True return = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}] activities = } [root@fabric1 ~]#

That is it, we now have our MySQL Fabric environment working and ready.
Watch out for my next few posts to learn more about MySQL Fabric.

Send to Kindle
PlanetMySQL Voting: Vote UP / Vote DOWN

Showing all available MySQL data types when creating a new table with MySQL for Excel

In this blog post we are going to talk about one of the features included since MySQL for Excel 1.3.0, a new advanced option was added to the Export Data dialog to show all available MySQL data types on its Data Type combo-box to override its default behavior that shows only the most commonly used data types.

Remember you can install the latest GA or maintenance version using the MySQL Installer or optionally you can download directly any GA or non-GA version from the MySQL Developer Zone.


PlanetMySQL Voting: Vote UP / Vote DOWN

Why TokuDB hates Transparent HugePages

If you try to install the TokuDB storage engine on a modern Linux distribution it might fail with following error message:

2014-07-17 19:02:55 13865 [ERROR] TokuDB will not run with transparent huge pages enabled.
2014-07-17 19:02:55 13865 [ERROR] Please disable them to continue.
2014-07-17 19:02:55 13865 [ERROR] (echo never > /sys/kernel/mm/transparent_hugepage/enabled)

You might be curious why TokuDB refuses to start with Transparent HugePages. Are they not a good thing… allowing smaller kernel page tables and less TLB misses when accessing data in the buffer pool? I was curious, so I asked Tim Callaghan this very question.

This problem originates with TokuDB using jemalloc memory allocator, which uses a particular trick to deal with memory fragmentation. The classical problem with memory allocators is fragmentation – if you allocated a say 2MB chunk from the operating system (typically using mmap),  as the process runs it is likely some of that 2MB memory block will become free but not all of it, hence it can’t be given back to operating system completely. jemalloc uses a clever trick being able to give back portions of memory allocated in such a way through madvise(…, MADV_DONTNEED) call.

Now what happens when you use transparent huge pages? In this case the operating system (and CPU, really) works with pages of a much larger size which only can be unmapped from the address space in its entirety – which does not work when smaller objects are freed which produce smaller free “holes.”

As a result, without being able to free memory efficiently the amount of allocated memory may grow unbound until the process starts to swap out – and in the end being killed by “out of memory” killer at least under some workloads. This is not a behavior you want to see from the database server. As such requiring to disable huge pages is a better choice.

Having said that this is pretty crude requirement/solution – disabling huge pages on complete operating system image to make one application work while others might be negatively impacted. I hope with a future jemalloc version/kernel releases there will be solution where jemalloc simply prevents huge pages usage for its allocations.

Using jmalloc and its approach to remove pages from resident space also makes TokuDB a lot different than typical MySQL instances running Innodb from the process space. With Innodb VSZ and RSS are often close. In fact we often monitor VSZ to ensure it is not excessively large to avoid danger of process starting to swap actively or be killed with OOM killer. TokuDB however often can look like this

[root@smt1 mysql]# ps aux | grep mysqld
mysql 14604 21.8 50.6 12922416 4083016 pts/0 Sl Jul17 1453:27 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/lib/mysql/smt1.pz.percona.com.err –pid-file=/var/lib/mysql/smt1.pz.percona.com.pid
root 28937 0.0 0.0 103244 852 pts/2 S+ 10:38 0:00 grep mysqld

In this case TokuDB is run with defaults on 8GB system – it takes approximately 50% of memory in terms of RSS size, however the VSZ of the process is over 12GB – this is a lot more than memory available.

This is completely fine for TokuDB. If I would not have Transparent HugePages disabled, though, my RSS would be a lot closer to VSZ causing intense swapping or even process killed by OOM killer.

In addition to explaining this to me, Tim Callaghan was also kind enough to share some links on this issue from other companies such as Oracle, NuoDB , Splunk, SAP, SAP(2), which provide more background information on this topic.

The post Why TokuDB hates Transparent HugePages appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

My talks about MySQL, Galera and LXC (and friends)

Im giving some talks this year:

MySQL Hochverfügbar mit Galera

Location: FrOSCon

About: Learn about Galera and deploy it using LXC and Ansible

LBaaS-Loadbalancer as a Service

Place: GUUG Frühjahrsgespräche

Topic: It is a workshop ( together with Jan Walzer and Jörg Jungermann). We are going to show how to use LXC to provide slim loadbalancers.

Medley der Containertechniken

Place: GUUG Frühjahrsgespräche

Topic: Learn about all the basic techniques vanilla based Container technology uses/shares (Namespaces, Cgroups und Chroot). Have a look at some of them (LXC, Libvrit, systemd-nspawn and Docker)

MySQL Replikation: Von den Anfängen in die Zukunft

Place: DOAG 2014

Topic: Learn about the past and the future of MySQL (and MariaDB) replication.


PlanetMySQL Voting: Vote UP / Vote DOWN

View Per-Process Detail with VividCortex

You probably know about VividCortex's Top Queries view, which shows MySQL query activity on one or many systems in industry-leading detail. But did you know you can also see what's happening on the operating system? Not only do we capture fine-grained metrics about CPU, network, and so on, but we capture per-process metrics.

Behold:

This is pretty life-changing when you're trying to figure out what's happening in your database. How many times have you gotten tunnel vision trying to find the cause of a performance problem by looking at MySQL log files or metrics, and later discovered that the problem was due to a rogue cron job or other process?

The Top Processes view also serves as an easy way to demonstrate how lightweight the VividCortex agents are. The screenshot above was taken over a period of 12 hours on our busiest MySQL server. Obviously, MySQL is the 800-lb gorilla on this system. The second process is our query agent, which comes in at just under 1% of total CPU (system and user CPU together; the metrics are percentages of a single CPU, but this is a multi-CPU system).

You can also see the Share button near the top right. Clicking this button will generate a share link you can paste into email, chat or whatnot. (There's also a Hubot script).

If you're not seeing operating system activity in per-process detail at 1-second resolution, you're not getting the whole picture! Sign up for a free 30-day trial, no credit card required. You'll be up and running in less than a minute.


PlanetMySQL Voting: Vote UP / Vote DOWN

Oklahoma City MySQL Group Forming

I’m excited to learn, just one day before the event, that Oklahoma City has formed a MySQL User Group. OKCMySQL.org

July 23, 2014 will be the inaugural meet-up for the OKC MySQL user group. As a special guest speaker, Peter Zaitsev (CEO of Percona and co-author of High Performance MySQL) will be giving a presentation on “Best Indexing Practices“, followed by a Q/A session.

Time is very short.  RPVP on Meetup.

I hope to see you at The Paramount – 701 West Sheridan, Oklahoma City, OK (map).

Tweet


PlanetMySQL Voting: Vote UP / Vote DOWN

Inaugural Meet-up Oklahoma City MySQL Meetup

The inaugural Meet-up Oklahoma City MySQL Meetup is Wednesday, July 23, 2014!
As a special guest speaker, Peter Zaitsev (CEO of Percona and co-author of High Performance MySQL) will be giving a presentation on “Best Indexing Practices”, followed by a Q/A session.

Please RSVP if you plan to attend so we can get a good headcount for food and beverages that will be provided and as always, please spread the word to friends or colleagues in the industry.

It should be a great night and great first event for the MySQL user group in OKC!



PlanetMySQL Voting: Vote UP / Vote DOWN

Reference architecture for a write-intensive MySQL deployment

We designed Percona Cloud Tools (both hardware and software setup) to handle a very high-intensive MySQL write workload. For example, we already observe inserts of 1bln+ datapoints per day. So I wanted to share what kind of hardware we use to achieve this result.

Let me describe what we use, and later I will explain why.

Server:

  • Chassis: Supermicro SC825TQ-R740LPB 2U Rackmount Chassis
  • Motherboard: Supermicro X9DRI-F dual socket
  • CPU: Dual Intel Xeon Ivy Bridge E5-2643v2 (6x 3.5Ghz cores, 12x HT cores, 25M L3)
  • Memory: 256GB (16x 16GB 256-bit quad-channel) ECC registered DDR3-1600
  • Raid: LSI MegaRAID 9260-4i 4-port 6G/s hardware RAID controller, 512M buffer
  • MainStorage: PCIe SSD HGST FlashMAX II 4.8TB
  • Secondary Storage (OS, logs): RAID 1 over 2x 3TB hard drives

Software:

When selecting hardware for your application, you need to look at many aspects – typically you’re looking for a solution for which you already have experience in working with and has also proved to be the most efficient option. For us it has been as follows:

Cloud vs Bare Metal
We have experience having hardware hosted at the data center as well as cash for upfront investments in hardware so we decided to go for physical self-hosted hardware instead of the cloud. Going this route also gave us maximum flexibility in choosing a hardware setup that was the most optimal for our application rather than selecting one of the stock options.

Scale Up vs Scale Out
We have designed a system from scratch to be able to utilize multiple servers through sharding – so our main concern is choosing the most optimal configuration for the server and provisioning servers as needed. In addition to raw performance we also need to consider power usage and overhead of managing many servers which typically makes having slightly more high-end hardware worth it.

Resource Usage
Every application uses resources in different ways so an optimal configuration will be different depending on your application. Yet all applications use the same resources you need to consider. Typically you want to plan for all of your resources to be substantially used – providing some margin for spikes and maintenance.

CPU

  • Our application processes a lot of data and uses the TokuDB storage engine which uses a lot of CPU for compression, so we needed powerful CPUs.
  • Many MySQL functions are not parallel, think executing single query or Alter table so we’re going for CPU with faster cores rather than larger amount of cores. The resulting configuration with 2 sockets giving 12 cores and 24 threads is good enough for our workloads.
  • Lower end CPUs such as Xeon E3 have very attractive price/performance but only support 32GB of memory which was not enough for our application.

Memory

  • For database boxes memory is mainly used as a cache, so depending on your application you may be better off investing in memory or storage for optimal performance. Check out this blog post for more details.
  • Accessing data in memory is much faster than even on the fastest flash storage so it is still important.
    For our workload having recent data in memory is very important so we get as much “cheap” memory as we can populating all 16 slots with 16GB dimms which have attractive cost per GB at this point.

Storage
There are multiple uses for the storage so there are many variables to consider

  • Bandwidth
    • We need to be able access data on the storage device quickly and with stable response time. HGST FlashMax II has been able to meet these very demanding needs.
  • Endurance
    • When using flash storage you need to worry about endurance – how much beating with writes flash storage can handle before it wears out. Some low cost MLC SSDs would wear out in the time frame of weeks if being written with maximum speed. HGST FlashMax II has endurance rating of 10 Petabytes written (for a random workload) – 30 Petabytes written (for a sequential workload)
    • We also use TokuDB storage engine which significantly reduces amount of writes compared to Innodb.
  • Durability
    • Does the storage provide true durability with data guaranteed to be persisted when write is acknowledged at the operating system level when power goes down or is loss possible?
      We do not want to risk database corruption in case of power failure so we were looking for storage solution which guarantees durability.
      HGST FlashMax II guarantees durability which has been confirmed by our stress tests.
  • Size
    • To scale application storage demands you need to scale both number of IO operations storage can handle and storage size. For flash storage it is often the size which becomes limiting factor.
      HGST FlashMax II 4.8 TB capacity is best available on the market which allows us to go “All Flash” and achieve very quick data access to all our data set.
  • Secondary Storage
    • Not every application need requires flash storage properties.
    • We have secondary storage with conventional drives for operating system and logs.
      Sequential read/write pattern works well with low cost conventional drives and also allow us to increase flash life time, having it handling less writes.
    • We’re using RAID with BBU for secondary storage to be able to have fully durable binary logs without paying high performance penalty.

Why PCIe SSD over SATA SSD?
There are arguments that SATA SSD provides just a good enough performance for MySQL and there is no need for PCIe. While these arguments are valid in one dimension, there are several more to consider.

First, like I said PCIe SSD still provides a best absolute response time and it is an important factor for an end user experience in SaaS systems like Percona Cloud Tools.
Second, consider maintenance operations like backup, ALTER TABLES or slave setups. While these operations are boring and do not get as much attention as a response time or throughput in benchmarks, it is still operations that DBAs performs basically daily, and it is very important to finish a backup or ALTER TABLE in a predictable time, especially on 3-4TB datasize range. And this is where PCIe SSD performs much better than SATA SSDs. For SATA SSD, especially bigger size, write endurance is another point of concern.

Why TokuDB engine?
The TokuDB engine is the best when it comes to insert operations to a huge dataset, and few more factors makes it a no-brainer:

  • TokuDB compression is a huge win. I estimate into this storage ( FlashMAX II 4.8TB) we will fit about 20-30TB of raw data.
  • TokuDB is SSD friendly, as it performs much less data writes per INSERT operation than InnoDB, which greatly extends SSD (which is, well, expensive to say the least) lifetime.

The post Reference architecture for a write-intensive MySQL deployment appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

Announcing ClusterControl Support for MariaDB 10

July 22, 2014 By Severalnines

We just wanted to make it official: Severalnines ClusterControl now supports MariaDB 10!

 

As most of you know will know by now, MariaDB 10 is the newest and most advanced version of the popular MariaDB relational database system. Whilst remaining application-compatible with the MySQL database, it adds many new capabilities to address the most challenging web and enterprise application use cases. Cluster deployments would be based on MariaDB Galera Cluster 10, which is a complete merge of MariaDB 10.0.12 and Galera Cluster. 

 

ClusterControl for MariaDB Clusters

 

ClusterControl gives you the power to deploy, manage, monitor and scale entire MariaDB Galera clusters efficiently and reliably. ClusterControl acts as a virtual DBA assistant and frees system administrators and developers from the complexity and learning curves associated with database clusters. 

 

 

MariaDB European Roadshow - Summer 2014

 

We had the opportunity to showcase ClusterControl for MariaDB 10 recently during the European MariaDB Roadshow, which took us to Helsinki, Amsterdam, Paris, Hamburg, Berlin and Frankfurt. We were delighted of course to be invited by SkySQL as guest speakers and to have the opportunity to talk to existing and future MariaDB users. 

 

There is one more date scheduled at the moment for this roadshow - London in September.

 

Joint live webinar: Management & Automation of MariaDB Galera Clusters

 

If you’ve missed the European Roadshow and/or would like a more in-depth presentation and demo of ClusterControl, please do register for our joint webinar with SkySQL on 30th September 2014.

 

read more


PlanetMySQL Voting: Vote UP / Vote DOWN

InfiniDB Expands Global Partner Program

InfiniDB Adds New Resources and Incentives for Channel Partners, Value Added Resellers and System Integrators to Sell and Build Applications with the InfiniDB High Performance Analytic Database


PlanetMySQL Voting: Vote UP / Vote DOWN

#DBHangOps 07/24/14 -- More Indexing!

#DBHangOps 07/24/14 -- More Indexing!

Hello everybody!

Join in #DBHangOps this Thursday, July, 24, 2014 at 11:00am pacific (18:00 GMT), where we pick up on our last conversation about indexing:

  • Indexing
    • More discussion on geo spatial indexes
    • Fulltext indexing and ranking
    • MariaDB indexing features (From Gerry!)
    • Anything else indexing!

Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!

See all of you on Thursday!


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/NET 6.9.2 RC has been released

Dear MySQL users,

MySQL Connector/Net 6.9.2 a new version of the all-managed .NET driver for MySQL has been released. This is a RC release for 6.9.x and is not recommended for production environments.

It is appropriate for use with MySQL server versions 5.5-5.7.

It is now available in source and binary form fromhttp://dev.mysql.com/downloads/connector/net/#downloadsandmirrorsites

(note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.)

Enjoy and thanks for the support!

On behalf of the MySQL Connector/NET and the MySQL/ORACLE RE Team.


PlanetMySQL Voting: Vote UP / Vote DOWN

Percona XtraDB Cluster 5.6.19-25.6 is now available

Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on July 21st 2014. Binaries are available from downloads area or from our software repositories. We’re also happy to announce that Ubuntu 14.04 LTS users can now download, install, and upgrade Percona XtraDB Cluster 5.6 from Percona’s software repositories.

Based on Percona Server 5.6.19-67.0 including all the bug fixes in it, Galera Replicator 3.6, and on Codership wsrep API 25.6, Percona XtraDB Cluster 5.6.19-25.6 is now the current General Availability release. All of Percona‘s software is open-source and free, and all the details of the release can be found in the 5.6.19-25.6 milestone at Launchpad.

New Features:

  • Percona XtraDB Cluster now supports storing the Primary Component state to disk by setting the pc.recovery variable to true. The Primary Component can then recover automatically when all nodes that were part of the last saved state reestablish communications with each other. This feature can be used for automatic recovery from full cluster crashes, such as in the case of a data center power outage and graceful full cluster restarts without the need for explicitly bootstrapping a new Primary Component.
  • When joining the cluster, the state message exchange provides us with gcache seqno limits. That information is now used to choose a donor through IST first, and, if this is not possible, only then SST is attempted. The wsrep_sst_donor setting is honored, though, and it is also segment aware.
  • An asynchronous replication slave thread was stopped when the node tried to apply the next replication event while the node was in non-primary state. But it would then remain stopped after the node successfully re-joined the cluster. A new variable, wsrep_restart_slave, has been implemented which controls if the MySQL slave should be restarted automatically when the node re-joins the cluster.
  • Handling install message and install state message processing has been improved to make group forming a more stable process in cases when many nodes are joining the cluster.
  • A new wsrep_evs_repl_latency status variable has been implemented which provides the group communication replication latency information.
  • Node consistency issues with foreign key grammar have been fixed. This fix introduces two new variables: wsrep_slave_FK_checks and wsrep_slave_UK_checks. These variables are set to TRUE and FALSE respectively by default. They control whether Foreign Key and Unique Key checking is done for applier threads.

Bugs Fixed:

  • Fixed the race condition in Foreign Key processing that could cause assertion. Bug fixed #1342959.
  • The restart sequence in scripts/mysql.server would fail to capture and return if the start call failed to start the server. As a result, a restart could occur that failed upon start-up, and the script would still return 0 as if it worked without any issues. Bug fixed #1339894.
  • Updating a unique key value could cause the server to hang if a slave node had enabled parallel slaves. Bug fixed #1280896.
  • Percona XtraDB Cluster has implemented threadpool scheduling fixes. Bug fixed #1333348.
  • garbd was returning an incorrect return code, ie. when garbd was already started, return code was 0. Bug fixed #1308103.
  • rsync SST would silently fail on joiner when the rsync server port was already taken. Bug fixed #1099783.
  • When gmcast.listen_addr was configured to a certain address, the local connection point for outgoing connections was not bound to the listen address. This would happen if the OS has multiple interfaces with IP addresses in the same subnet. The OS would pick the wrong IP for a local connection point and other nodes would see connections originating from an IP address which was not listened to. Bug fixed #1240964.
  • An issue with re-setting galera provider (in wsrep_provider_options) has been fixed. Bug fixed #1260283.
  • Variable wsrep_provider_options couldn’t be set in runtime if no provider was loaded. Bug fixed #1260290.
  • Percona XtraDB Cluster couldn’t be built with Bison 3.0. Bug fixed #1262439.
  • MySQL wasn’t handling exceeding the max writeset size wsrep error correctly. Bug fixed #1270920.
  • Fixed the issue which caused a node to hang/fail when SELECTs/SHOW STATUS was run after FLUSH TABLES WITH READ LOCK was used on a node with wsrep_causal_reads set to 1 while there was a DML on other nodes. Bug fixed #1271177.
  • Lowest group communication layer (evs) would fail to handle the situation properly when a large number of nodes would suddenly start recognizing each other. Bugs fixed #1271918 and #1249805.
  • Percona XtraBackup SST would fail if the progress option was used with a large number of files. Bug fixed #1294431.

NOTE: When performing an upgrade from an older 5.6 version on Debian/Ubuntu systems, in order to upgrade the Galera package correctly, you’ll need to pin the Percona repository and run: apt-get install percona-xtradb-cluster-56. This is required because older Galera deb packages have an incorrect version number. The correct wsrep_provider_version after upgrade should be 3.6(r3a949e6).

This release contains 50 fixed bugs. The complete list of fixed bugs can be found in our release notes.

Release notes for Percona XtraDB Cluster 5.6.19-25.6 are available in our online documentation along with the installation and upgrade instructions.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Percona XtraDB Cluster Errata can be found in our documentation.

The post Percona XtraDB Cluster 5.6.19-25.6 is now available appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

Pages