Planet MySQL

Planet MySQL - http://www.planetmysql.org/
  1. Why JSON is bad for applications

    Today I read an article about how company X has improved things by amongst other things ditching JSON after 2 years of using it. Before I start on this subject I should say that JSON does have its place. If you have a web application where a browser is talking to a web server and in particular uses JavaScript then JSON is a good fit. I've discussed this issue several times before with Brian Aker who works with me at HP's Advanced Technology Group and in the past I have been hit with the issues I'm going to talk about here. JSON is human readable and easy to parse, that cannot be denied and for prototyping is good in a pinch. The first problem comes when you need to validate data. I've been stung many times by one end trying to read/write the JSON in a slightly different format to the other end, the end result is always not pretty. This is one advantage that XML and SOAP has going for it over JSON since validation is easier. I'm personally not a fan of XML but there are many who are. There are additional problems when you start using mobile platforms. Mobile networks are unreliable, you may have a good 3G signal but it is possible to only get dial-up speed through it due to all the other users. JSON is verbose, XML more so which requires more data transfer. Whilst this can be resolved with protocol compression it will require additional decoding on the client side to do this. In addition data conversion will be needed in many cases for numeric fields. The biggest problem with JSON is versioning. As you add more features to your application there will likely come a time where you need to change the data structure for your messages. Often you can't guarantee that your client is using the same version of the software as your server so backwards and forwards compatibility problems can arise. Resolving these often makes the JSON messages very complex to create and decode. This is not as much of a problem for web applications because the browser usually grabs an update version of the JavaScript on execution. So changing the data format at any time is easy as long as both ends agree on the format. The solution For many applications the data you are sending is directly from a database or at least data that has been modified since being read from a database. So you will likely want the data model for your messages to match this as much as possible. This is where Google's Protocol Buffers fit nicely. Protocol Buffers allow you to specify a schema for the data in a human readable format, it actually looks a little like a database schema. They will automatically validate the data for you and have versioning built-in. This means you can make your code easily backwards and forwards compatible. There is a positive and negative side to the data transfer of Protocol Buffers. It is a binary protocol. This means it takes up minimal bandwidth on the wire but also means that it is not human readable and difficult to figure out which data is for which field (although should not be used for security through obscurity). The same could be said if you were given InnoDB table data without the schemas. It also means it may be possible to compress the data further with something like LZO or DEFLATE. I recommend application developers consider Protocol Buffers instead of JSON when they are next developing a server/client application. Note I updated this article to explain the binary protocol a little better. Thanks to Antony Curtis for pointing it out.
  2. Monitoring MySQL from Oracle Enterprise Manager

    I wanted to quickly remind everyone that we recently announced the GA release of Oracle Enterprise Manager for MySQL.We know that many existing Oracle Database customers are also using MySQL, so we're very excited that you can now manage your entire hardware and software stack, including MySQL, all from within a single tool.I also wanted to take the opportunity to answer a few common questions that I've seen come up over the last few weeks:Q: Can I use the new Plugin with Oracle Enterprise Manager 11g (Grid Control)?A: No. The Plugin requires Oracle Enterprise Manager 12c release 4 or later.Q: Where can I download the Plugin?A: The Plugin is available via Self Update within Oracle Enterprise Manager 12c release 4 and later. It's also available via My Oracle Support and the Oracle Software Delivery Cloud. You can find more information about the installation process here.Q: Can I monitor MySQL Community Edition (GPL licensed) Targets?A: Yes. There are no technical restrictions or behavioral differences for the Plugin depending on whether the MySQL Target is Community or Enterprise Edition. The licensed use of the Plugin to monitor that Target, however, is only covered by the 30 day trial license or a MySQL Enterprise license. If you have a valid license that covers the MySQL Target being monitored, then it does not matter which Edition of MySQL the Target is running.Q: Where can I find documentation about the Plugin?A: You can find it here and here.We look forward to hearing your feedback on this new edition to MySQL Enterprise! Please let us know if you have any questions, encounter any problems, or have any feature requests. You can also engage in a discussion on our forums. Lastly, feel free to reach out to me directly as well, either here in the comments or via email. Thank you for using MySQL!
  3. Benchmarking Joomla

    This post recently caught my attention on Planet MySQL. If you haven’t read it yet, I suggest that you go and do so, and also read the comments. I think Matthew’s request for the queries so that others can run comparative benchmarks is very interesting, and while I don’t have access to the queries used to produce […] Related posts: Updated mysql-proxy benchmarking script (for proxy 0.7) My previous post contained a lua script for MySQL proxy... Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,... iptables trick to limit concurrent tcp connections This is sort of a self-documenting post, and a self-support... YARPP powered by AdBistroPowered by
  4. Log Buffer #395, A Carnival of the Vanities for DBAs

    This Log Buffer Edition ventures out in the fields of Oracle, SQL Server and MySQL and picks some of the coolest blog posts for this week. Oracle: In Oracle, if we add a column to a table which is NOT NULL, we are allowed to do it directly, in a single statement, as long as we supply a DEFAULT value to populate any pre-existing rows. There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy. Work-around Instance Migration Limits of BPM Suite 11g. Oracle Event Processing 12c: java errors when deploying a new OEP project. Creating a WebLogic 12c Data Source Connection to Pivotal GemFireXD 1.3. SQL Server: It sounds simple enough. Either your column will always have a value or it may not. Yet somehow such a seemingly simple decision can become a never-ending debate where database schema begins to resemble superstition and designing effective tables seems more contentious than you expected it to be. There’s a joke doing the rounds at SQL conferences and seminars: three DBAs walk into a NoSQL bar and leave when they can’t find a table. Stairway to AlwaysOn Level 3: Infrastructure 101. How to compare two databases and email the results to every one who needs to know. Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA. MySQL: Set up an SSL-encrypted connection between Sphinx and MySQL. Sometimes users ask for something that doesn’t really make sense. On the first glance. But then you start asking and realize that the user was right, you were wrong, and it is, actually, a perfectly logical and valid use case. PECL/mysqlnd_ms needs updates for MySQL Group Replication. Why should you migrate from MySQL to MariaDB? The Perfect Server – CentOS 7 (Apache2, Dovecot, ISPConfig 3).
  5. Replace Oracle RAC with MariaDB Galera Cluster?

    Fri, 2014-10-31 11:34erkanyanarIf you want to avoid downtimes in your business, High Availabilty (HA) is a strong requirement which, by definition, makes it possible to access your data all the time without losing (any) data. In this blog we compare two alternatives: Oracle RAC and MariaDB Galera Cluster.  There are several options to implement High Availability. Oracle RAC is a popular and proven HA solution. HA can also be enabled for your data and systems with loadbalancers that make it possible to always access your data. MariaDB Galera Cluster provides similar functionality using synchronous multi-master Galera replication. It is also easier to build and proves to be more cost-effective. Being OpenSource, you may have to pay for support, but not for running the system. Next, the designs of Oracle RAC and MariaDB Galera Cluster are going to be compared, so you can make up your mind on your own. Oracle RAC With RAC, Oracle instances run on separate nodes, while the data is located on shared storage. All instances access the same files. To prevent conflicts, the instances must agree on which instance is actually working on a block of data. If a node wants to change a row, it must get exclusive access to that block and store it in its cache. It therefore asks the other nodes whether they have the block. If no other node does, it gets the block from storage. Even in case of read-access data all the nodes need to communicate this way to get the data as it is done for writing. When the block is modified, the requesting nodes get a consistent read version (which they are not allowed to modify) from the block. This adds latency due to internode communication - there will be read and write access every time a node does not have the block. The need for communication between the nodes for every access on a table adds overhead. On the other hand, having all blocks advance local locks on a node, e.g. for SELECT FOR UPDATE, are cluster wide locks. The advantage of RAC is that losing an Oracle node does not harm the service at all. The other nodes will keep providing data (HA of the access). Therefore, you can shut down a node to perform maintenance tasks such as upgrading hardware or software, while reducing unexpected downtime. However, the shared storage - responsible for the data - is a potential single point of failure. On Oracle RAC distributing read or write access is not optimal because latency is added by additional internode round trips. The best results occur when the application only accesses a fixed part of the data per node, so that no blocks have to be moved around, but it makes the setup more complicated. MariaDB Galera Cluster In contrast to Oracle RAC, MariaDB Galera Cluster is a high availability setup with shared-nothing architecture. Instead of having one shared storage (SAN or NAS), every cluster member has its own copy of all the data, thus eliminating the single point of failure. MariaDB Galera Cluster take care about syncing data even for new nodes. This makes managing the cluster easy, as adding an empty node into the cluster is sufficient. MariaDB Galera Cluster will provide all data for the new node. Unlike Oracle RAC, accessing a node to read data does not result in internode communication. Instead, communication (and so latency) happens at the time transactions are committed. This is faster than the Oracle RAC approach of acquiring all blocks in advance, but this also means conflicts are found at the time a transaction is committed. And conflict are found by the internode communication because of the commit. Thats why the same data should not be accessed (at least not at the same time) on different nodes, as this increases the chance of having conflicts. This will not happen when the data is accessed on different nodes one after another. In the case of Oracle RAC the blocks would have to be copied. This means that a SELECT FOR UPDATE statement is able to fail on commit, as it locks the data locally but not cluster wide. So conflicts with transactions on other nodes can only be found at the time of the commit. That is why the same data should not be accessed at the same time on different nodes, as it increases the chance of having conflicts. This is slightly different to Oracle RAC where accessing data on another node any time later does move the blocks. While Oracle RAC has a lot of latency moving data blocks into the cache of every node, MariaDB Galera Cluster has an increased likelihood of failing commits. Like Oracle RAC, single nodes in a MariaDB Galera Cluster can be taken down for maintenance without stopping the cluster. When a node rejoins the cluster, it automatically gets missing transactions via Incremental State Transfer (IST), or it may sync all data using State Snapshot Transfer (SST). If the missing transactions are in a local (configurable) cache of a node, IST is used, if not SST is used. One drawback of the current Galera version is that Data Definition Language (DDL) commands (CREATE, ALTER, DROP) are run synchronously on the cluster. Therefore the entire cluster stalls until a DDL command finishes. Thats why Magento installations running default configuration do not scale at all on MariaDB Galera Cluster. In general using tools like pt-online-schema-change bypass this limitation. Eliminating this limitation is on the development roadmap. In comparison Oracle RAC and MariaDB Galera Cluster provide similar functionality using different designs. Each one is eliminating maintenance downtime for many tasks and thus gives you more freedom to run applications. In general Oracle RAC has a lot more latency because of internode communication (including moving all requested data blocks) for read and write access. In MariaDB Galera Cluster the changed dataset is sent around by committing. So only changed datasets are sent. Despite the obvious similarities, the two databases have quite different architectures. Oracle RAC uses shared storage, while MariaDB Galera Cluster uses a shared-nothing architecture, which is less expensive. Oracle RACs shared storage is quite expensive. The author has observed EMC or NetApp for that, as it is the single point of failure something reliable is needed. Data on MariaDB Galera Cluster is replicated on all the nodes, which makes it easy to run the cluster spread over different regions. Consequently, your data will be safe even if your datacenter burns down. To have this level of redundancy with Oracle RAC you need a shared storage accordingly, i.e. a Netapp MetroCluster. Beside adding more costs, Netapp MetroCluster requires a network with a round trip latency of less than 10ms, while MariaDB Galera Cluster even runs in Cloud environments in different regions. With Oracle RAC there are two inherent sources of latency: accessing the shared storage and internode communication for read and write access. While in MariaDB Galera Cluster there is latency for every COMMIT needed by the internode communication to check and send the data to be committed. Of course MariaDB Galera Cluster is no one-to-one replacement for Oracle RAC. But if your application runs with either Oracle or MySQL/MariaDB, MariaDB Galera Cluster is more than an alternative. Further reading Success story Greetz.com: successfull migration from Oracle RAC to MariaDB Galera Cluster Tags: ClusteringGaleraMariaDB Enterprise About the Author erkan yanar Erkan Yanar is an independent consultant with strong focus on MySQL, Docker/LXC and OpenStack. He loves to give presentations and do also writes for trade magazines.