In the MySQL ecosystem there are few load balancers there are also open-source, and ProxySQL is one of the few proxies that works at the application layer and therefore is SQL aware.
In this blog post we will benchmark ProxySQL against MaxScale, another popular proxy for MySQL.
The idea to compare ProxySQL vs MaxScale came after reading an interesting blog post of Krzysztof Książek on SQL Load Balancing Benchmark, comparing performance of MaxScale vs HAProxy.

Disclaimer: ProxySQL is not GA yet, therefore please do not use it in production.



Sysbench setupI wanted to setup a similar sysbench setup to what Krzysztof used in his benchmark, but it is slightly different:
a) instead of using a MySQL cluster with Galera, I setup a cluster with 1 master …

Following the release of ClusterControl 1.2.10 a week ago, we are excited to demonstrate this latest version of the product on Tuesday next week, June 9th.

Join our CTO, Johan Andersson, who will be discussing and demonstrating the new ClusterControl DSL, Integrated Developer Studio and Database Advisors, which are some of the cool new features we’ve introduced with ClusterControl 1.2.10.

New Features Webinar: ClusterControl 1.2.10

DATE & TIME

Europe/MEA/APAC
Tuesday, June 9th at 09:00 (UK) / 10:00 CEST (Germany, France, Sweden)
…

Some applications have a heavy write workload on a few records – for instance when incrementing a global counter: this is called a write hotspot. Because you cannot update the same row simultaneously from multiple threads, this can lead to performance degradation. When using Percona XtraDB Cluster (PXC), some users try to solve this specific issue by writing on multiple nodes at the same time. Good idea or bad idea? Read on!

Simultaneous writes on a standalone InnoDB server

Say you have these 3 transactions being run simultaneously (id is the primary key of the table):

# T1
UPDATE t SET ... WHERE id = 100
# T2
UPDATE t SET ... WHERE id = 100
# T3
UPDATE t SET ... WHERE id = 101

All transactions will require a row lock on the record they want to modify. So T3 can commit at the same time than T1 and/or T2, because it will …

When I started writing this post I planned to follow up on my series of posts by creating a unique constraint with more than 16 columns using a MariaDB virtual column the same way I used a MySQL generated column in my most recent post. During my testing I abandoned that plan when I discovered two things:

1. MariaDB virtual columns impose a 252 character limit on the expression that defines …

Tue, 2015-06-02 19:07geoff_montee_g

Most MariaDB users are probably aware of the privilege system available in MariaDB and MySQL. Privileges control what databases, tables, columns, procedures, and functions a particular user account can access. For example, if an application stored credit card data in the database, this data should probably be protected from most users. To make that happen, the DBA might disallow access to the table or column storing this sensitive data.

However, sometimes the privilege system isn't sufficient to secure data. Sometimes data needs to be secured beyond tables and columns. In those cases, row-level security (sometimes abbreviated RLS) may be necessary. Possible use cases for row-level security are:

• A government agency might only allow a user to see a row based on classification (CONFIDENTIAL, …

In my previous post, I showed you a way to store tweets in MySQL, and then use Perl to automatically publish them on Twitter.

In this post, we will look at automatically sending a “thank you” to people who retweet your tweets — and we will be using Perl and MySQL again.

Just like in the first post, you will need to register your application with Twitter via apps.twitter.com, and obtain the following:

consumer_key
consumer_secret
access_token
access_token_secret

One caveat: Twitter has a rate limit on how often you may connect with your application — depending upon what you are trying to do. See the Rate Limiting and …

It seems these days if anyone knows anything about tuning InnoDB, it’s that you MUST tune your innodb_buffer_pool_size to 80% of your physical memory. This is such prolific tuning advice, it seems engrained in many a DBA’s mind.  The MySQL manual to this day refers to this rule, so who can blame the DBA?  The question is: does it makes sense?

What uses the memory on your server?

Before we question such advice, let’s consider what can take up RAM in a typical MySQL server in their broad categories.  This list isn’t necessarily complete, but I think it outlines the large areas a MySQL server could consume memory.

• OS Usage: Kernel, running processes, filesystem cache, etc.
• MySQL fixed usage: query cache, InnoDB …

Setting up a database backup is a primary task for database administrators and we see perl and shell scripts wrapped around few of the backup-tools in practice. With right tools…

The post Setup and configure MySQL backup using Holland and Xtrabackup first appeared on Change Is Inevitable.

If you have chosen to split your data across boxes, and architected your app to not query across boxes there is still a case where you will need to. Data mining, reports and data health checks require hitting all servers at some point. The case I am going over is sessions and figuring out the Session Length without taking averages of averages which is wrong.


Let's assume you have a session table of the following

mysql> describe sessions;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| user_id  | bigint(20) unsigned | NO   | PRI | 0       |       |
| added_ms | bigint(20) unsigned | NO   | PRI | 0       |       |
| appVer   | varchar(8)          | YES  |     | NULL    |       |
| device   | bigint(20) unsigned | YES  | MUL | NULL    | …

Unless you’ve been hiding under a rock for a few years, you probably know that JSON is quickly gaining support in major database servers. Due to its use in the web front-end, JSON has overtaken XML in APIs, and it’s spreading through all the layers in the stack one step at a time.

Most major databases have supported XML in some fashion for a while, too, but developer uptake wasn’t universal. JSON adoption amongst developers is nearly universal today, however. (The king is dead, long live the king!) But how good is JSON support in the databases we know and love? We’ll do a comparison in this blog post.

What is JSON Support?

First – what does it even mean for a database to support JSON?

You could easily argue that JSON support has been in databases for a long time. After all, all you have to do is store a blob of text that is correctly formatted in the database, and applications can store, retrieve, and …