With the imminent release of ProxySQL as stable version, and its presentation at PerconaLive Amsterdam , I decide to write few blog posts to describe how to install, configure and use it without digging too much through documentation.
Moreover, practical examples are often easier to understand.

This tutorial is a long one, although none of the topics are discussed in details to avoid making it even longer (or boring).

For this tutorial, we will use a small setup with 4 nodes:
- node1 (192.168.124.101) , mysql master
- node2 (192.168.124.102) , mysql slave
- node3 (192.168.124.103) , mysql slave
- app (192.168.124.104) , application server where proxysql will also run


Replication setup
Without going into …

In our previous post, we showed you how to interpret reports generated by pt-query-digest. Today we’d like to cover some of its more advanced features, as it is a pretty extensive tool with lots of functionality. We’ll also show you what information you should be looking for, and how to derive conclusions based on that data. 

This is the eleventh installment in the ‘Become a MySQL DBA’ blog series. Our previous posts in the DBA series include Analyzing SQL Workload with pt-query-digest, Query Tuning Process, …

MySQL sys version 1.5.0 has just been released.

It has significant contributions from both external contributors (thank you Daniël and Shlomi) and internal (thank you Jesper and Morgan). Jesper particularly made many significant improvements in this release.

Here’s a full summary of the changes:

Improvements

• The `format_bytes` function now shows no decimal places when outputting a simple bytes value
• The `processlist`/`x$processlist` views where improved, changes include:
  • The `pid` and `program_name` of the connection are shown, if set within the `performance_schema.session_connect_attrs` table (Contributed by Daniël van Eeden)
  • Issue #50 – The current statement progress is reported via …

Auditing your database means tracking access and changes to your data and db objects. The Audit Log Plugin has been shipped with Percona Server since 5.5.37/5.6.17, for a little over 12 months. Prior to the Audit Log Plugin, you had to work in darker ways to achieve some incarnation of an audit trail.

We have seen attempts at creating audit trails using approaches such as ‘sniffing the wire’, init files, in-schema ‘on update’ fields, triggers, proxies and trying to parse the traditional logs of MySQL (slow, general, binary, error). All of these attempts miss a piece of the pie, i.e. if you’re sniffing tcp traffic you’ll miss local connections, parsing binary logs you’re missing any reads. Your reasons for audit logging might be down to compliance requirements (HIPAA, PCI DSS) or you may need a way to examine database activity or track the connections incoming.

Over the past …

There are cases where we need to implement MySQL partitioning, or at least test if it is an option to solve an issue. However, how are we able to test it when the table on our production environment has hundreds of millions of rows, several composite indexes and/or the size on disk is hundreds of gigabytes?

Testing environments usually don’t have all the data that production has and if they have, probably you are not testing all the use-cases at a database level. Therefore, is it possible to test MySQL Partitioning on production impacting as less as possible?

When we execute pt-online-schema-change, it creates a table, triggers, and then copies the data. As we are going to test partitioning we are going to need both tables – with and without partitioning – and we are going to use triggers to keep both tables consistent. A good thing about changing a table to use partitioning is that, usually, …

Originally Posted by Adam Bird

There needed to be a better way for agents in Zendesk to track the tasks that had been assigned them. Using the Cronofy calendar API we created an app that keeps those assignments synchronized with the agent’s calendar.

This post shares our learnings from interacting with the Zendesk API, hopefully to help you with your own Zendesk project.

The Ruby source code for our integration is also available.

When a Ticket is a Task

Tickets are the core entity in the Zendesk domain. Tasks are just Tickets of type : ‘task’. This gives them an additional attribute due_at.

We also need to track the status of the ticket to know when it’s …

There needed to be a better way for agents in Zendesk to track the tasks that had been assigned them. Using the Cronofy calendar API we created an app that keeps those assignments synchronized with the agent's calendar.

This post shares our learnings from interacting with the Zendesk API, hopefully to help you with your own Zendesk project.

The Ruby source code for our integration is also available.

When a Ticket is a Task

Tickets are the core entity in the Zendesk domain. Tasks are just Tickets of type : 'task'. This gives them an additional attribute due_at.

We also need to track the …

In Python it is easily possible to findout the name of a Unicode character and findout some properties about that character. The module which does that is called unicodedata.

An example:

>>> import unicodedata
>>> unicodedata.name('☺')
'WHITE SMILING FACE'

This module uses the data as released in the UnicodeData.txt file from the unicode.org website.

So if UnicodeData.txt is a 'database', then we should be able to import it into MySQL and use it!

I wrote a small Python script to automate this. The basic steps are:

• Download UnicodeData.txt
• Create a unicodedata.ucd table
• Use LOAD DATA LOCAL INFILE to load the data

This isn't difficult especially because the file doesn't have the actual characters in it. It is …

Recently, we had an issue in our application which is a single page application(SPA). We have all the functionality in a single page and most of the data and view changes happen through ajax requests and iframe page displays.We are using CodeIgniter framework for this application. So sometimes what happened was the user was logged out while accessing the application and this occurred once a while for 1 or 2 users, not a reproducible case one would say.Before starting on the case, I would like to specify the steps which CodeIgniter takes for checking session for any request.Steps:
1. Read session cookie sent from browser.
2. Read session cookie process proceeds with below step checks (If any one step fails below, it results in create one session cookie)
2a. Checks if session cookie is present
2b. Checks if it is a valid session cookie by using it's decryption algorithm check
2c. If  valid session, then check if …

Percona’s CTO Vadim Tkachenko wrote a response to my Galera Snapshot Isolation post last week. I think Tkachenko may have misunderstood some of my results, and I’d like to clear those up now. I’ve ported the MariaDB tests to Percona XtraDB Cluster, and would like to confirm that using exclusive write locks on all reads, as Tkachenko recommends, can recover serializable histories. Finally, we’ll address Percona’s documentation.

I didn’t use the default isolation levels

But there I need to add quite IMPORTANT addition: it may leave data in inconsistent state if you use SPECIAL TYPE of transactions in default isolation levels that Aphyr uses in his test.

My tests did not use the default isolation …