# MySQL

### Make MySQL 8.0 Better Through Better Benchmarking

MySQL Performance Blog - Fri, 2016-12-02 19:58

This blog post discusses how better MySQL 8.0 benchmarks can improve MySQL in general.

Like many in MySQL community, I’m very excited about what MySQL 8.0 offers. There are a lot of great features and architecture improvements. Also like many in the MySQL community, I would like to see MySQL 8.0 perform better. Better performance is what we always want (and expect) from new database software releases.

Rarely do performance improvements happen by accident – they require running benchmarks, finding bottlenecks and eliminating them. This is the area where I think things could use improvement.

If you come to the MySQL Keynote at Oracle OpenWorld, or if you go to MySQL Benchmarks Page, you find a very limited set of benchmarks. They mostly focus around sysbench performance, with large numbers of connections and large numbers of cores. I’m not convinced this effort is the best use of our time these days.

Don’t get me wrong: as one of the original designers of sysbench, it is a great and simple tool that helps spot many bottlenecks. I still use it to find performance issues. But it is only one tool, which is by no means provides full coverage of real-world MySQL workloads.

I agree with Mark Callaghan (see discussion here): we need to run more benchmarks using a wider set of circumstances, to ensure there are no regressions in new releases. This will help move MySQL performance forward for real users.

Here are some specific ideas on how I think we could benchmark MySQL 8.0 better:

1. Focus on production-recommended settings. Way too often we see benchmarks run with the doublewrite buffer disabled, InnoDB checksum disabled and no binary log (like in this benchmark). While they produce some excitingly high numbers, they have little practical value for real workloads. At very least I would very much like to see separate numbers for the “street legal car,” versus one designed to set a record on the salt flats.
2. Go beyond sysbench. Sysbench focuses on PK-only based access for very simple tables, and does not even do JOINs as part of its workload. I would like to see more benchmarks that have tables with many indexes, using secondary key lookups and joins, involving rows with many fields, and medium and large size blobs that are common bottlenecks. We also need more database features covered. Are foreign keys or triggers getting faster or slower? What about stored procedure execution performance? I would love to see these get covered. Mark Callaghan suggests LinkBench, which I think is a fine benchmark to add, but it shouldn’t be the only one.
3. Do more with sysbench. Sysbench could get more done and cover more important workloads. Workloads with data fitting in memory and not fitting in memory should be shown separately. Testing performance with large numbers of tables is also very important – many MySQL installations for SaaS applications run tens of thousands of tables (sometimes going into millions). I would also suggest running more injection benchmarks with sysbench, as they are more representative of the real world.
4. Look at latency, not just throughput. The benchmarks results we commonly see are focused on the throughput over a long period of time, without looking at the latency and how performance changes over time. Stalls and performance dips are well known in the MySQL space – especially the famous InnoDB checkpointing woes (though this issue has gotten a lot better) There are other cases and circumstances where stalls and slowdowns can happen.
5. Measure resource consumption. I very much like how Mark Callaghan shows the CPU usage and IO usage per transaction/operation, so we can get a better idea of efficiency.
6. Concurrency. Recently, the focus has been on very high concurrency in terms of connections and active connections, typically on very big iron (using as many as 72 cores). And as much as this is important to “future-proofing” MySQL as we get more and more cores per socket every year, it should not be the only focus. In fact, it is extremely rare for me to see sustained loads of more than 20-40  “threads running” for well-configured systems. With modern solutions like ProxySQL, you can restrict concurrency to the most optimal levels for your server through multiplexing. Not to mention the thread pool, which is available in MySQL Enterprise, Percona Server and MariaDB. I would like to see a much more focused benchmark at medium-to-low concurrency. The fact that single thread performance has gotten slower in every Major MySQL version is not a good thing. As MySQL currently runs a single query in a single thread, it impacts query latencies in many real-world situations.
7. Virtualization. We need more benchmarks in virtualized environments, as virtualization and the cloud are where most workloads are these days (by number). Yes, big iron and bare metal are where you get the best performance, but it’s not where most users are running MySQL. Whenever you are looking at full blown virtualization or containers, the performance profile can be substantially different from bare metal. Virtualized instances often have smaller CPU cores – getting the best performance with 8-16 virtual cores might be a more relevant data set for many than the performance with 100+ cores.
8. SSL and encryption. MySQL 5.7 was all about security. We’re supposed to be able to enable SSL easily, but was any work done on making it cheap? The benchmark Ernie Souhrada did a few years back showed a pretty high overhead (in MySQL 5.6). We need more focus on SSL performance, and getting it would allow more people to run MySQL with SSL. I would also love to see more benchmarks with encryption enabled, to understand better how much it costs to have your data encrypted “at rest,” and in what cases.
9. Protocol X and MySQL Doc Store. These were added after MySQL 5.7 GA, so it would be unfair to complain about the lack of benchmarks comparing the performance of those versus previous versions. But if Protocol X is the future, some benchmarks are in order. It would be great to have official numbers on the amount of overhead using MySQL Doc Store has compared to SQL (especially since we know that queries are converted to SQL for execution).
10. Replication benchmarks. There are a lot of great replication features in newer MySQL versions: statement/row/mixed, GTID or no GTID, chose multiple formats for row events, enable various forms of semi-sync replication, two ways of parallel replication and multi-source replication. Additionally, MySQL group replication is on the way. There seems to be very little comprehensive benchmarks for these features, however. We really need to understand how they scale and perform under various workloads.
11. Mixed workloads.  Perhaps one of the biggest differences between benchmarks and real production environments is that in benchmarks the same workload often is used over and over, while in the real world there is a mix of “application workloads.” The real world also has additional tasks such as backups, reporting or running “online” ALTER TABLE operations. Practical performance is performance you can count on while also serving these types of background activities. Sometimes you can get a big surprise from the severity of impact from such background activities.
12. Compression benchmarks. There have been some InnoDB compression benchmarks (both for new and old methods), but they are a completely separate set of benchmarks that are hard to put in context with everything else. For example, do they scale well with high numbers of connections and large numbers of cores?
13. Long-running benchmarks. A lot of the benchmarks run are rather short. Many of the things that affect performance take time to accumulate: memory fragmentation on the process (or OS Kernel) side, disk fragmentation and database fragmentation. For a database that is expected to run many months without restarting, it would be great to see some benchmark runs that last several days/weeks to check long term stability, or if there is a regression or resource leak.
14. Complex queries. While MySQL is not an analytical database, it would still be possible to run complex queries with JOINs while the MySQL optimizer team provides constant improvements to the optimizer. It would be quite valuable to see how optimizer improvements affect query execution. We want to see how these improvements affect scalability with hardware and concurrency as well..

These are just some of ideas on what could be done. Of course, there are only so many things the performance engineering team can focus at the time: one can’t boil the ocean! My main suggestion is this: we have done enough deep optimizing of primary key lookups with sysbench on high concurrency and monster hardware, and it’s time to go wider. This ensures that MySQL doesn’t falter with poor performance on commonly run workloads. Benchmarks like these have much more practical value than beating one million primary key selects a second on a single server.

Categories: MySQL

### Managing Replication with Percona XtraDB Cluster

MySQL Performance Blog - Thu, 2016-12-01 22:56

This blog post discusses managing replication with Percona XtraDB Cluster.

Recently a customer asked me to setup replication between two distinct Percona XtraDB Clusters located in geographically separate data centers. The customer goal was to use one of the clusters only in case of disaster recovery. They tried extending the cluster, but because of the WAN latency impact on their writes and the requirements of a node in a third data center for quorum, they walked away from that setup. Since they were not concerned about losing a few transactions in case of a major disaster, they were OK with regular MySQL replication using GTIDs.

Easy enough right! Both clusters are cloud-based, of course, and the provider can stop/restart any node on short notice. This setup caused some concern for the customer around how to handle replication. Since they don’t have dedicated personnel to monitor replication, or at least handle alerts, they asked if we could find a way to automate the process. So, here we go!

We all try to solve the problems with the tools we know. In my case, I like Pacemaker a lot. So using Pacemaker was my first thought. In a cloud environment, a Pacemaker setup is not easy (wouldn’t that be a cluster in a cluster… a bit heavy). But wait! Percona XtraDB Cluster with Galera replication is already handling quorum, and it provides a means of exchanging information between the nodes. Why not use that?

We can detect quorum status the same way the clustercheck scripts do it. To exchange messages, why don’t we simply write to a table. The Galera replication will update the other nodes. I went on and wrote a bash script that is called by cron every minute. The script monitors the node state and the content of the table. If all is right, it updates the table to report its presence (and if it is acting as a slave or not). The script validates the presence of a slave in the cluster. If no reporting slave is found, the script proceeds to the “election” of a new slave, based on the wsrep_local_index value. Basically, the script is a big bunch of “if” statements. The script is here, and the basic documentation on how to set it up here

Of course, if it works for one cluster, it can work for two. I have configured my customer’s two Percona XtraDB Clusters in a master-to-master relationship using this script. I ran through a bunch of failure scenario cases. The script survived all of them! But of course, this is new. If you are going to implement this solution, run your own set of tests! If you find any problem, file an issue on GitHub. I’ll be happy to fix it!

Categories: MySQL

### Database Daily Ops Series: GTID Replication and Binary Logs Purge

MySQL Performance Blog - Thu, 2016-12-01 17:43

This blog continues the ongoing series on daily operations and GTID replication.

In this blog, I’m going to investigate why the error below has been appearing in a special environment I’ve been working with on the last few days:

Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

The error provides the right message, and explains what is going on. But sometimes, it can be a bit tricky to solve this issue: you need additional information discovered after some tests and readings. We try and keep Managed Services scripted, in the sense that our advice and best practices are repeatable and consistent. However, some additional features and practices can be added depending on the customer situation.

Some time ago one of our customer’s database servers presented the above message. At that point, we could see the binary log files in a compressed form on master (gzipped). Of course, MySQL can’t identify a compressed file with a .gz extension as a binary log. We uncompressed the file, but replication presented the same problem – even after uncompressing the file and making sure the UUID of the current master and the TRX_ID were there. Obviously, I needed to go and investigate the problem to see what was going on.

When the server starts, the global value of gtid_purged, which was called before as gtid_lost, is initialized to the set of GTIDs contained by the Previous_gtid_log_event of the oldest binary log. When a binary log is purged, gtid_purged is re-read from the binary log that has now become the oldest one.

That makes me think: if something is compressing binlogs on the master without purging them as expected by the GTID mechanism, it’s not going to be able to re-read existing GTIDs on disk. When the slave replication threads restarts, or the DBA issues commands like reset slave and reset master (to clean out the increased GTID sets on Executed_Gtid_Set from the SHOW SLAVE STATUS command, for example), this error can occur. But if I compress the file:

• Will the slave get lost and not find all the needed GTIDs on the master after a reset slave/reset master?
• If I purge the logs correctly, using PURGE BINARY LOGS, will the slave be OK when restarting replication threads?

Test 1: Compressing the oldest binary log file on master, restarting slave threads

I would like to test this very methodically. We’ll create one GTID per binary log, and then I will compress the oldest binary log file in order to make it unavailable for the slaves. I’m working with three virtual machines, one master and two slaves. On the second slave, I’m going to run the following sequence: stop slave, reset slave, reset master, start slave, and then, check the results. Let’s see what happens.

On master (tool01):

tool01 [(none)]:> show master logs; +-------------------+-----------+ | Log_name          | File_size | +-------------------+-----------+ | mysqld-bin.000001 |       341 | | mysqld-bin.000002 |       381 | | mysqld-bin.000003 |       333 | +-------------------+-----------+ 3 rows in set (0.00 sec) tool01 [(none)]:> show binlog events in 'mysqld-bin.000001'; +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                              | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysqld-bin.000001 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.32-log, Binlog ver: 4                             | | mysqld-bin.000001 | 120 | Previous_gtids |         1 |         151 |                                                                   | | mysqld-bin.000001 | 151 | Gtid           |         1 |         199 | SET @@SESSION.GTID_NEXT= '4fbe2d57-5843-11e6-9268-0800274fb806:1' | | mysqld-bin.000001 | 199 | Query          |         1 |         293 | create database wb01                                              | | mysqld-bin.000001 | 293 | Rotate         |         1 |         341 | mysqld-bin.000002;pos=4                                           | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 5 rows in set (0.00 sec) tool01 [(none)]:> show binlog events in 'mysqld-bin.000002'; +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                              | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysqld-bin.000002 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.32-log, Binlog ver: 4                             | | mysqld-bin.000002 | 120 | Previous_gtids |         1 |         191 | 4fbe2d57-5843-11e6-9268-0800274fb806:1                            | | mysqld-bin.000002 | 191 | Gtid           |         1 |         239 | SET @@SESSION.GTID_NEXT= '4fbe2d57-5843-11e6-9268-0800274fb806:2' | | mysqld-bin.000002 | 239 | Query          |         1 |         333 | create database wb02                                              | | mysqld-bin.000002 | 333 | Rotate         |         1 |         381 | mysqld-bin.000003;pos=4                                           | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 5 rows in set (0.00 sec) tool01 [(none)]:> show binlog events in 'mysqld-bin.000003'; +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                              | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysqld-bin.000003 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.32-log, Binlog ver: 4                             | | mysqld-bin.000003 | 120 | Previous_gtids |         1 |         191 | 4fbe2d57-5843-11e6-9268-0800274fb806:1-2                          | | mysqld-bin.000003 | 191 | Gtid           |         1 |         239 | SET @@SESSION.GTID_NEXT= '4fbe2d57-5843-11e6-9268-0800274fb806:3' | | mysqld-bin.000003 | 239 | Query          |         1 |         333 | create database wb03                                              | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 4 rows in set (0.00 sec)

Here we see that each existing binary log file has just one transaction. That will make it easier to compress the oldest binary log, and then disappear with part of the existing GTIDs. When the slave connects to a master, it will first send all the Executed_Gtid_Set, and then the master sends all the missing IDs to the slave. As Stephane Combaudon said, we will force it to happen! Slave database servers are both currently in the same position:

tool02 [(none)]:> show slave statusG *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.0.10                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysqld-bin.000003           Read_Master_Log_Pos: 333                Relay_Log_File: mysqld-relay-bin.000006                 Relay_Log_Pos: 545         Relay_Master_Log_File: mysqld-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes             ...            Retrieved_Gtid_Set: 4fbe2d57-5843-11e6-9268-0800274fb806:1-3             Executed_Gtid_Set: 4fbe2d57-5843-11e6-9268-0800274fb806:1-3              tool03 [(none)]:> show slave statusG *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.0.10                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysqld-bin.000003           Read_Master_Log_Pos: 333                Relay_Log_File: mysqld-relay-bin.000008                 Relay_Log_Pos: 451         Relay_Master_Log_File: mysqld-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes ...            Retrieved_Gtid_Set: 4fbe2d57-5843-11e6-9268-0800274fb806:1-3             Executed_Gtid_Set: 4fbe2d57-5843-11e6-9268-0800274fb806:1-3

Now, we’ll compress the oldest binary log on master:

[root@tool01 mysql]# ls -lh | grep mysqld-bin. -rw-rw---- 1 mysql mysql  262 Nov 11 13:55 mysqld-bin.000001.gz #: this is the file containing 4fbe2d57-5843-11e6-9268-0800274fb806:1 -rw-rw---- 1 mysql mysql  381 Nov 11 13:55 mysqld-bin.000002 -rw-rw---- 1 mysql mysql  333 Nov 11 13:55 mysqld-bin.000003 -rw-rw---- 1 mysql mysql   60 Nov 11 13:55 mysqld-bin.index

On tool03, which is the database server that will be used, we will execute the replication reload:

tool03 [(none)]:> stop slave; reset slave; reset master; start slave; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) tool03 [(none)]:> show slave statusG *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 192.168.0.10                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File:           Read_Master_Log_Pos: 4                Relay_Log_File: mysqld-relay-bin.000002                 Relay_Log_Pos: 4         Relay_Master_Log_File:              Slave_IO_Running: No             Slave_SQL_Running: Yes               Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                    Last_Error:                  Skip_Counter: 0           Exec_Master_Log_Pos: 0               Relay_Log_Space: 151               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 1236                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                   Master_UUID: 4fbe2d57-5843-11e6-9268-0800274fb806              Master_Info_File: /var/lib/mysql/master.info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp: 161111 14:47:13      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 1 1 row in set (0.00 sec)

Bingo! We broke the replication streaming on the slave. Now we know that the missing GTID on the master was due to the compressed file, and wasn’t able to be passed along to the connecting slave during their negotiation. Additionally, @@GTID_PURGED was not reloaded as per what the online manual said. The test done and we confirmed the theory (if you have additional comments, enter it at the end of the blog).

Test 2: Purge the oldest file on master and reload replication on slave

Let’s make it as straightforward as possible. The purge can be done manually using the PURGE BINARY LOGS command to get it done a proper way as the binary log index file should be considered a part of this purge operation as well (it should be edited to remove the file name index entry together with the log file on disk). I’m going to execute the same as before, but include purging the file manually with the mentioned command.

tool01 [(none)]:> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | mysqld-bin.000001 | 341 | | mysqld-bin.000002 | 381 | | mysqld-bin.000003 | 333 | +-------------------+-----------+ 3 rows in set (0.00 sec) tool01 [(none)]:> purge binary logs to 'mysqld-bin.000002'; Query OK, 0 rows affected (0.01 sec) tool01 [(none)]:> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | mysqld-bin.000002 | 381 | | mysqld-bin.000003 | 333 | +-------------------+-----------+ 2 rows in set (0.00 sec)

Now, we’ll execute the commands to check how it goes:

tool03 [(none)]:> stop slave; reset slave; reset master; start slave; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) tool03 [(none)]:> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.0.10 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 151 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4fbe2d57-5843-11e6-9268-0800274fb806 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 161111 16:35:02 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 1 row in set (0.00 sec)

The GTID on the purged file is needed by the slave. In both cases, we can set the @@GTID_PURGED as below with the transaction that we know was purged, and move forward with replication:

tool03 [(none)]:> stop slave; set global gtid_purged='4fbe2d57-5843-11e6-9268-0800274fb806:1'; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.01 sec) tool03 [(none)]:> start slave; Query OK, 0 rows affected (0.01 sec)

The above adjusts the GTID on @@GTID_PURGED to just request the existing GTIDs, using the oldest existing GTID minus one to make the slave start the replication from the oldest existing GTID. In our scenario above, the replica restarts replication from 4fbe2d57-5843-11e6-9268-0800274fb806:2, which lives on binary log file mysqld-bin.000002. Replication is fixed, as its threads can restart processing the data streaming coming from master.

You will need to execute additional steps in checksum and sync for the set of transactions that were jumped when setting a new value for @@GTID_PURGED. If replication continues to break after restarting, I advise you rebuild the slave (possibly the subject of future blog).

• MySQL Bugs: #72635: Data inconsistencies when master has truncated binary log with GTID after crash;
• MySQL Bugs: #73032: Setting gtid_purged may break auto_position and thus slaves;

Conclusion

Be careful when purging or doing something manually with binary logs, because @@GTID_PURGED needs to be automatically updated when binary logs are purged. It seems to happen only when expire_logs_days is set to purge binary logs. Yet you need to be careful when trusting this variable, because it doesn’t consider fraction of days, depending the number of writes on a database server, it can get disks full in minutes. This blog showed that even housekeeping scripts and the PURGER BINARY LOGS command were able to make it happen.

Categories: MySQL

### The Queueing Knee, Part 1

Xaprb, home of innotop - Thu, 2016-12-01 03:02

The “knee” in the queueing theory response time curve is a topic of some debate in the performance community. Some say “the knee is at 75% utilization; everyone knows that.” Others say “it depends.” Others say “there is no knee.”

Depending on the definition, there is a knee, but there are several definitions and you may choose the one you want. In this post I’ll use a definition proposed by Cary Millsap: the knee is where a line from the origin is tangent to the queueing response time curve. The result is a function of the number of service channels, and although we may argue about the topics in the preceding paragraph and whether this is the right definition, it still serves to illustrate important concepts.

The image above shows the response time stretch factor curve for a queueing system with 8 service channels. This is analogous to a server with 8 CPUs, for example. A line drawn from the origin, tangent to the curve, touches it at 0.7598, or 76% utilization.

The important thing to note is that this curve is a function of $$m$$, the number of service channels. In this case, $$m=8$$. As you increase the number of service channels in the system, the curve remains flat longer and the “knee,” where the curve appears to lift upwards and start to climb steeply, moves towards the right—towards higher utilization, signified by $$\rho$$.

You can experiment interactively with this, using this Desmos calculator.*

Here’s the derivation. Using the heuristic approximation,

$R = \frac{1}{1-\rho^m}$

The line is tangent to the curve where response time divided by utilization is at a minimum. The equation for $$R/\rho$$ is

$R/\rho = \frac{1}{\rho - \rho^{m+1}}$

The minimum of this equation is where its derivative is zero; the derivative is

$\frac{\left(m+1\right)\rho^m-1}{\rho^2\left(\rho^m-1\right)^2}$

The root of this expression is a function of $$m$$ as expected.

$\left(m+1\right)^{-\frac{1}{m}}$

Here’s how that function looks when plotted.

The graph shows that as the number of service channels increases, the the knee occurs at increasingly high utilization.

Despite the debate over exactly what the definition of the knee is, this illustrates two fundamental truths about queueing systems:

1. As you add service channels (servers) to a queueing system, queueing delay is tolerable at increasingly high utilization.
2. The rule of thumb that you can’t run a system at greater than 75% utilization is invalid. For systems with many service channels (CPUs, disks, etc) that is wasteful, and you should strive for higher utilization.

For more on this topic, please read my free ebook on queueing theory.

* Note that the calculator uses an approximation to the queueing theory response time curve, which is easier to differentiate than the Erlang C formula but underestimates how steeply the curve climbs at higher utilizations. I discussed this heuristic approximation at length in my previous blog post. Even though it’s an approximation, again, it serves the purposes of this blog post.

Categories: MySQL

### Galera Cache (gcache) is finally recoverable on restart

MySQL Performance Blog - Wed, 2016-11-30 22:38

This post describes how to recover Galera Cache (or gcache) on restart.

Recently Codership introduced (with Galera 3.19) a very important and long awaited feature. Now users can recover Galera cache on restart.

Need

If you gracefully shutdown cluster nodes one after another, with some lag time between nodes, then the last node to shutdown holds the latest data. Next time you restart the cluster, the last node shutdown will be the first one to boot. Any followup nodes that join the cluster after the first node will demand an SST.

Why SST, when these nodes already have data and only few write-sets are missing? The DONOR node caches missing write-sets in Galera cache, but on restart this cache is wiped clean and restarted fresh. So the DONOR node doesn’t have a Galera cache to donate missing write-sets.

This painful set up made it necessary for users to think and plan before gracefully taking down the cluster. With the introduction of this new feature, the user can retain the Galera cache.

How does this help ?

On restart, the node will revive the galera-cache. This means the node can act as a DONOR and service missing write-sets (facilitating IST, instead of using SST). This option to retain the galera-cache is controlled by an option named gcache.recover=yes/no. The default is NO (Galera cache is not retained). The user can set this option for all nodes, or selective nodes, based on disk usage.

gcache.recover in action

The example below demonstrates how to use this option:

• Let’s say the user has a three node cluster (n1, n2, n3), with all in sync.
• The user gracefully shutdown n2 and n3.
• n1 is still up and running, and processes some workload, so now n1 has latest data.
• n1 is eventually shutdown.
• Now the user decides to restart the cluster. Obviously, the user needs to start n1 first, followed by n2/n3.
• n1 boots up, forming an new cluster.
• n2 boots up, joins the cluster, finds there are missing write-sets and demands IST but given that n1 doesn’t have a gcache, it falls back to SST.

n2 (JOINER node log):

2016-11-18 13:11:06 3277 [Note] WSREP: State transfer required: Group state: 839028c7-ad61-11e6-9055-fe766a1886c3:4680 Local state: 839028c7-ad61-11e6-9055-fe766a1886c3:3893

n1 (DONOR node log), gcache.recover=no:

2016-11-18 13:11:06 3245 [Note] WSREP: IST request: 839028c7-ad61-11e6-9055-fe766a1886c3:3893-4680|tcp://192.168.1.3:5031 2016-11-18 13:11:06 3245 [Note] WSREP: IST first seqno 3894 not found from cache, falling back to SST

Now let’s re-execute this scenario with gcache.recover=yes.

n2 (JOINER node log):

2016-11-18 13:24:38 4603 [Note] WSREP: State transfer required: Group state: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:1495 Local state: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:769 .... 2016-11-18 13:24:41 4603 [Note] WSREP: Receiving IST: 726 writesets, seqnos 769-1495 .... 2016-11-18 13:24:49 4603 [Note] WSREP: IST received: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:1495

n1 (DONOR node log):

2016-11-18 13:24:38 4573 [Note] WSREP: IST request: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:769-1495|tcp://192.168.1.3:5031 2016-11-18 13:24:38 4573 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

You can also validate this by checking the lowest write-set available in gcache on the DONOR node.

mysql> show status like 'wsrep_local_cached_downto'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | wsrep_local_cached_downto | 1 | +---------------------------+-------+ 1 row in set (0.00 sec)

So as you can see, gcache.recover could restore the cache on restart and help service IST over SST. This is a major resource saver for most of those graceful shutdowns.

gcache revive doesn’t work if . . .

If gcache pages are involved. Gcache pages are still removed on shutdown, and the gcache write-set until that point also gets cleared.

Again let’s see and example:

• Let’s assume the same configuration and workflow as mentioned above. We will just change the workload pattern.
• n1, n2, n3 are in sync and an average-size workload is executed, such that the write-set fits in the gcache. (seqno=1-x)
• n2 and n3 are shutdown.
• n1 continues to operate and executes some average size workload followed by a huge transaction that results in the creation of a gcache page. (1-x-a-b-c-h) [h represent transaction seqno]
• Now n1 is shutdown. During shutdown, gcache pages are purged (irrespective of the keep_page_sizes setting).
• The purge ensures that all the write-sets that has seqno smaller than gcache-page-residing write-set are purged, too. This effectively means (1-h) everything is removed, including (a,b,c).
• On restart, even though n1 can revive the gcache it can’t revive anything, as all the write-sets are purged.
• When n2 boots up, it requests IST, but n1 can’t service the missing write-set (a,b,c,h). This causes SST to take place.
Summing it up

Needless to say, gcache.recover is a much needed feature, given it saves SST pain. (Thanks Codership.) It would be good to see if the feature can be optimized to work with gcache pages.

And yes, Percona XtraDB Cluster inherits this feature in its upcoming release.

Categories: MySQL

### Using the InnoDB Buffer Pool Pre-Load Feature in MySQL 5.7

MySQL Performance Blog - Wed, 2016-11-30 21:16

In this blog post, I’ll discuss how to use the InnoDB buffer pool pre-load feature in MySQL 5.7

Starting MySQL 5.6, you can configure MySQL to save the contents of your InnoDB buffer pool and load it on startup. Starting in MySQL 5.7, this is the default behavior. Without any special effort, MySQL saves and restores a portion of buffer pool in the default configuration. We made a similar feature available in Percona Server 5.5 – so the concept has been around for quite a while.

Frankly, time has reduced the need for this feature. Five years ago, we would typically store databases on spinning disks. These disks often took quite a long time to warm up with normal database workloads, which could lead to many hours of poor performance after a restart. With the rise of SSDs, warm up happens faster and reduces the penalty from not having data in the buffer pool. Typically, a system reaches 90% of its fully warmed up performance in 10 minutes or less. But since it takes virtually no effort to use, saving the contents of the InnoDB buffer pool is a great feature to enable by default.

This blog post looks into some issues with this feature that might not be totally obvious from its name or documentation.

#1

By default, MySQL only saves 25% of the most actively accessed pages (by the LRU) in the InnoDB buffer pool (not the whole buffer pool).

This is a reasonable choice for many use cases: it saves the most valuable pages, which can then be loaded faster than if you try to load every page in the buffer pool (many of which might not be relevant for continuing workload).

You can change this number by setting the innodb_buffer_pool_dump_pct variable. If you’re using InnoDB essentially as an in-memory database, and want to ensure all data is memory resident and can be accessed without a disk read, set it to 100.

Note that this variable is based on the actual amount of data present in memory, not the buffer pool size, For example, if you have a 100GB buffer pool but it only contains 10GB of data, by default only 25% of 10GB (2.5GB) gets saved. (As the manual explains, it will not take nearly as much on disk as only the page identifiers are stored, not full page contents.)

#2

MySQL starts and becomes accessible through the network before the buffer pool load on startup is complete. Immediately before the start, a lot of resources fetch buffer pool contents from the disk as quickly as possible, possibly affecting performance. If you have multiple MySQL nodes – like using MySQL Replication or running Percona XtraDB Cluster – you might consider bringing them back for production traffic only after the buffer pool load operation completes. You can monitor the buffer pool load progress by watching the GLOBAL STATUS variable:

Buffer pool load is in progress:

As a side note, it would be great if MySQL would provide a clearer concept of the “State” of the node: being UP versus being READY to serve the traffic in an optimal way are often not the same.

#3

InnoDB’s buffer pool preload is not very efficient, at least with fast storage. In my test environment, with a rather capable NVMe storage, I get a more than 400MB/sec warmup rate if I run read-only sysbench workload. The InnoDB’s buffer pool preload warmup rate is around 100MB/sec or so.  I would guess the problem is that it doesn’t drive as many parallel IO requests as SSD storage needs to perform optimally. I did not investigate it further.

#4

Innodb buffer pool save/restore only stores the buffer pool contents on a clear shutdown.  If the server crashes MySQL still does a buffer pool preload, but with the content information saved on last clean shutdown (stored in the ib_buffer_pool  file). This might end up wasting time loading data that is not relevant for the current workload. Periodically running the following ensures a fresh set of pages is available for a quick warmup, even if MySQL crashed:

SET GLOBAL innodb_buffer_pool_dump_now=ON;

This preserves the current list of buffer pool pages.

Note that while you (hopefully) do not see your MySQL crash that often, the same issue exists with backups, MySQL slave cloning with Percona XtraBackup, or LVM snapshot. This causes these operations to be less efficient.

I hope the observations in this blog help you put this feature to better use!

Categories: MySQL

### Percona XtraBackup 2.4.5 is now available

MySQL Performance Blog - Tue, 2016-11-29 18:26

Percona announces the GA release of Percona XtraBackup 2.4.5 on November 29th, 2016. You can download it from our download site and from apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

New features:
• Percona XtraBackup now supports SHA256 passwords. Using the SHA256 algorithm requires either SSL encrypted connection, or using public key encryption for password exchange which is only available when both client and server are linked with OpenSSL.
• Percona XtraBackup now supports Command Options for Secure Connections.
• NOTE: Due to xbcrypt format changes, backups encrypted with this Percona XtraBackup version will not be recoverable by older versions.
Bugs fixed:
• Percona XtraBackup would crash while preparing the backup, during the shutdown, when the master thread was performing a checkpoint and purge thread was expecting that all other threads completed or were idle. Bug fixed #1618555.
• Safe slave backup algorithm performed too short delays between retries which could cause backups to fail on a busy server. Bug fixed #1624473.
• Percona XtraBackup didn’t check the logblock checksums. Bug fixed #1633448.
• Fixed new compilation warnings with GCC 6. Bug fixed #1641612.
• xbcrypt was not setting the Initialization Vector (IV) correctly (and thus is was not using an IV). This was causing the same ciphertext to be generated across different runs (for the same message/same key). The IV provides the extra randomness to ensure that the same ciphertext is not generated across runs. Bug fixed #1643949.
• target-dir was no longer relative to the current directory but to datadir instead. Bug fixed #1611568.
• Backup would still succeed even if xtrabackup would fail to write the metadata. Bug fixed #1623210.
• xbcloud now supports EMC ECS Swift API Authorization requests. Bugs fixed #1638017 and #1638020 (Txomin Barturen).
• Some older versions of MySQL did not bother to initialize page type field for pages which are not index pages (see upstream #76262 for more information). Having this page type uninitialized could cause xtrabackup to crash on prepare. Bug fixed #1641426.
• Percona XtraBackup would fail to backup MariaDB 10.2 with the unsupported server version error message. Bug fixed #1602842.

Other bugs fixed: #1639764, #1639767, #1641596, and #1641601.

Release notes with all the bugfixes for Percona XtraBackup 2.4.5 are available in our online documentation. Please report any bugs to the launchpad bug tracker.

Categories: MySQL

### Percona XtraBackup 2.3.6 is now available

MySQL Performance Blog - Tue, 2016-11-29 17:55

Percona announces the release of Percona XtraBackup 2.3.6 on November 29, 2016. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

This release is the current GA (Generally Available) stable release in the 2.3 series.

New Features
• Percona XtraBackup now supports SHA256 passwords. Using the SHA256 algorithm requires either SSL encrypted connection, or using public key encryption for password exchange which is only available when both client and server are linked with OpenSSL.
• Percona XtraBackup now supports Command Options for Secure Connections.
• NOTE: Due to xbcrypt format changes, backups encrypted with this Percona XtraBackup version will not be recoverable by older versions.
Bugs Fixed:
• Fixed intermittent assertion failures that were happening when Percona XtraBackup couldn’t correctly identify server version. Bug fixed #1568009.
• Safe slave backup algorithm performed too short delays between retries which could cause backups to fail on a busy servers. Bug fixed #1624473.
• Fixed new compilation warnings with GCC 6. Bug fixed #1641612.
• xbcrypt was not setting the Initialization Vector (IV) correctly (and thus is was not using an IV). This was causing the same ciphertext to be generated across different runs (for the same message/same key). The IV provides the extra randomness to ensure that the same ciphertext is not generated across runs. Bug fixed #1643949.
• Backup would still succeed even if XtraBackup would fail to write the metadata. Bug fixed #1623210.
• xbcloud now supports EMC ECS Swift API Authorization requests. Bugs fixed #1638017 and #1638020 (Txomin Barturen).
• Percona XtraBackup would fail to backup MariaDB 10.2 with the unsupported server version error message. Bug fixed #1602842.

Other bugs fixed: #1639764 and #1639767.

Release notes with all the bugfixes for Percona XtraBackup 2.3.6 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

Categories: MySQL

### Percona Server for MongoDB 3.0.14-1.9 is now available

MySQL Performance Blog - Mon, 2016-11-28 18:18

Percona announces the release of Percona Server for MongoDB 3.0.14-1.9 on November 28, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB 3.0.14-1.9 is an enhanced, open source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.0 protocol and drivers. Based on MongoDB 3.0.14, it extends MongoDB with MongoRocks and PerconaFT storage engines, as well as features like external authentication and audit logging. Percona Server for MongoDB requires no changes to MongoDB applications or code.

NOTE: PerconaFT has been deprecated and will be removed in the future.

This release includes all changes from MongoDB 3.0.13 and MongoDB 3.0.14. We implemented no additional fixes or features.

You can find the release notes in the official documentation.

Categories: MySQL

### Percona Server 5.7.16-10 is now available

MySQL Performance Blog - Mon, 2016-11-28 17:58

Percona announces the GA release of Percona Server 5.7.16-10 on November 28, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.7.16, including all the bug fixes in it, Percona Server 5.7.16-10 is the current GA release in the Percona Server 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.16-10 milestone at Launchpad.

Deprecated Features:
• Metrics for scalability measurement feature is now deprecated. Users who have installed this plugin but are not using its capability are advised to uninstall the plugin due to known crashing bugs.
Bugs Fixed
• When a stored routine would call an administrative command such as OPTIMIZE TABLE, ANALYZE TABLE, ALTER TABLE, CREATE/DROP INDEX, etc. the effective value of log_slow_sp_statements was overwritten by the value of log_slow_admin_statements. Bug fixed #719368.
• Server wouldn’t start after crash with with innodb_force_recovery set to 6 if parallel doublewrite file existed. Bug fixed #1629879.
• Thread Pool thread limit reached and failed to create thread messages are now printed on the first occurrence as well. Bug fixed #1636500.
• INFORMATION_SCHEMA.TABLE_STATISTICS and INFORMATION_SCHEMA.INDEX_STATISTICS tables were not correctly updated for TokuDB. Bug fixed #1629448.

Other bugs fixed: #1633061, #1633430, and #1635184.

The release notes for Percona Server 5.7.16-10 are available in the online documentation. Please report any bugs on the launchpad bug tracker .

Categories: MySQL

### Percona Server 5.6.34-79.1 is now available

MySQL Performance Blog - Mon, 2016-11-28 17:21

Percona announces the release of Percona Server 5.6.34-79.1 on November 28, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.6.34, including all the bug fixes in it, Percona Server 5.6.34-79.1 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release are available in the 5.6.34-79.1 milestone on Launchpad.

Deprecated features:
• Metrics for scalability measurement feature is now deprecated. Users who have installed this plugin but are not using its capability are advised to uninstall the plugin due to known crashing bugs.
Bugs fixed:
• When a stored routine would call an administrative command such as OPTIMIZE TABLE, ANALYZE TABLE, ALTER TABLE, CREATE/DROP INDEX, etc. the effective value of log_slow_sp_statements was overwritten by the value of log_slow_admin_statements. Bug fixed #719368.
• Thread Pool thread limit reached and failed to create thread messages are now printed on the first occurrence as well. Bug fixed #1636500.
• INFORMATION_SCHEMA.TABLE_STATISTICS and INFORMATION_SCHEMA.INDEX_STATISTICS tables were not correctly updated for TokuDB. Bug fixed #1629448.

Other bugs fixed: #1633061, #1633430, and #1635184.

Release notes for Percona Server 5.6.34-79.1 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

Categories: MySQL

### A Great Mid-Priced Stereo System

Xaprb, home of innotop - Thu, 2016-11-24 23:21

You probably know that I like to be pretty minimalistic, and don’t accumulate a lot of “stuff” in my life. Yet for the few material things I value, I try to find the sweet spot: quality above average, price no higher than needed.

Music is one of the things I care a lot about: I have bought thousands of CDs. But I also value when my music sounds as good as possible. I treated myself to an upgraded stereo system so I’d enjoy better audio quality. Here’s my current system.

It’s quite simple: it’s designed to listen to stereo audio with as little processing and as few components as possible, but still be convenient. Here’s the full list of components and widgets:

I bought a couple of the items from Amazon Warehouse Deals and spent less than \$700 on the whole setup. I cannibalized an old rubber doormat, cut in half, for padding under the speaker stands.

How does it sound? It’s entry-level audiophile, well above average performance on a small budget. For my purposes, it’s great: it fills a medium-sized room with accurate, uncolored sound.

The speakers are by far the most important part of the setup. Read anywhere online and you’ll see all the audiophiles raving about the ELAC speakers. They outperform speakers many times the cost. There’s a newer generation of ELACs that are supposed to be even better, for a small uptick in price. A reasonable upgrade for this setup would also be an ELAC subwoofer.

The amplifier is a now-discontinued generation from Cambridge Audio. Cambridge Audio is an audiophile brand with a near-legendary reputation. I wanted it to be simple (no fancy processing), slightly above bare-bones, but have plenty of horsepower (45 watts per channel) to drive through dynamic peaks and power good-quality components. There’s only a few controls, including a bypass switch to remove the equalizer and so on from the circuit. Getting a discontinued model saved me a lot of money. If I wanted to step this up and spend a few hundred dollars more, ELAC’s amplifiers are a great combination of price and value.

The speaker stands are serviceable, nothing fancy, but they place the speakers at the right height and they don’t cause issues with sound quality. The rubber doormat keeps them stable and vibration-free.

The rest of the components are things I already had: some headphone-to-coax adapters, 14-gauge speaker wire, and so on. The UPS is important to protect the components from electrical damage.

I mostly listen to my music from an iPhone or iPad over AirPlay via an Apple AirPort Express. Despite the theoretical problems with this (lossy compression, inferior components), the reality is lossy compression artifacts at 256kbps AAC or 320kbps MP3 are minor compared to the quality degradation of nearly any speakers you can buy.

I listen this way because it’s extremely convenient, and convenience enables more listening, which is where I get my enjoyment. (I used to design and build speaker enclosures myself. I learned to appreciate fine audio from a relative who’s made his living designing custom systems for wealthy people. I’ve listened to music on systems costing hundreds of thousands of dollars. I don’t think the high price makes it more enjoyable.)

The result sounds amazing overall. Yes: if I’d spent thousands of dollars more on my stereo, that one passage in that one song could have sounded better. But! I built a system that sounds incredible for a few hundred dollars. If I want to hear my music on a better system, I can visit someone who owns one.

Pro tip: if, like me, you listen via AirPlay for the convenience, you can Option-Click on your speaker icon in your Mac’s menu bar and select an AirPlay device for audio output.

Want to listen to some music that sounds great on a good system? Here’s a Spotify playlist just for you. Make sure you toggle high-quality playback in your Spotify preferences. Enjoy!

Categories: MySQL

### 6 Steps to Better Security and Privacy

Xaprb, home of innotop - Thu, 2016-11-24 15:52

I wrote previously about securing your digital life. Technology and digital threats are advancing so fast that we’re almost inevitably all going to be attacked in some way. Here are a few more steps I’ve taken recently.

To repeat some of the recommendations from my previous post, you should absolutely use the following:

• 2-factor authentication everywhere possible (email, social media, etc)
• long, strong, randomly generated, unique, never-reused passwords
• full-device/full-disk encryption on hard drives, laptops, tablets, phones etc

Although I don’t think it is realistic to think we can avert a catastrophic attack forever on a personal or global scale (think “digital 9-11”), I think mass attacks against easily identifiable vulnerable populations are much more common and damaging, so here are some additional steps to avoid being the “tallest poppy.”

1. Use a VPN (It’s Easy)

When you access the internet or use an app, you’re opening a series of data connections between your device and another computer somewhere. Many common vulnerabilities are at or near the start of this chain: from your device to the WiFi router, from the router to the DSL device down the street, etc. A virtual private network (VPN) creates an encrypted tunnel between your device and at least part of the chain.

A VPN is a big step up in security and privacy. For example:

• A VPN can prevent people snooping on what you’re doing if you’re connected to an insecure WiFi point at the airport or coffee shop.
• A VPN can prevent your internet service provider from logging and inspecting your browsing or other usage—some internet service providers even modify what you browse, injecting ads, tracking, and other stuff into websites!
• If someone hacks into your home router or cable modem, they won’t be able to intercept VPN-tunneled traffic before it leaves the house.

These are really legitimate things to worry about: millions of cheap, old, unsecured, underscrutinized devices such as routers and modems are sitting exposed to the internet, and tons of them have known security holes.

VPNs sound obscure and hard to set up, but they’re not. You can get a subscription to a VPN service easily and cheaply. I use Private Internet Access and there are many others. Just search and read ratings from a few objective review sites.

A VPN service is also flexible. You can use a standard VPN client to connect; you don’t have to use the one they probably provide for you. I use Tunnelblick to connect to Private Internet Access, for example, because Tunnelblick is open source so I trust it more, and I already use it for other VPNs I connect to.

I’ve become increasingly convinced that we’re on the brink of widespread sophisticated automated telephone phishing attacks. Consider the following entirely realistic attack against your family’s personal information and finances:

1. A robot telephones your parents and listens to the sound of their voice, then hangs up.
2. The robot calls you and imitates the sound of your parent’s voice, engaging you in a simple conversation. “Baron, it’s mom. I am at the hospital. I need your social security number, quick.”

What would you do? Most people would blurt their SSN and ask questions later. You need a previously-agreed way to validate that it’s really your mother on the phone. There are a lot of other scenarios you can imagine where you’d give the robot some really sensitive information.

Does this attack sound far-fetched? It’s not. In the last year I’ve gotten amazingly sophisticated robot calls. They’ve been for relatively innocuous purposes (“can I count on you to donate to the veterans fund?”) but they illustrate how adept computers are at carrying on pretty convincing conversations with humans. I’ve been fascinated at how quickly they’ve gotten good at this. And a sophisticated attacker could easily ask me for some information that seems harmless, call someone else and ask for more, put the pieces together quickly to form the whole puzzle, then a human could use the information to call the bank and convince the agent that they’re me.

One way to avoid this is to agree on a family password or other cue. Many families have passwords for unexpected situations such as sending a coworker to pick up the kids from school when there’s an emergency, for example.

I’m not sure I have the right answer for this yet. Please comment if you have a suggestion. We need a technique that works for humans under stressful situations, but doesn’t fall prey to robo calls trying to create those situations and trick the humans into bypassing the system or revealing the secret.

3. Use Signal or WhatsApp

Encryption provides a host of benefits. We really, really, really need to normalize encryption as a global society. Plaintext communication needs to become weird, and encrypted needs to become easy and expected.

Right now it’s the reverse, and people who use encryption for some types of communication are outliers. We need herd immunity.

WhatsApp and several other messenger apps can use end-to-end encryption for text messages and the like. Sometimes by default, sometimes as a configuration option. Signal is a popular option; I like that it’s open-source so it’s verifiable.

4. Use Keybase

One way of normalizing encryption, and making yourself easily and publicly reachable for private communications, is to join Keybase.io. A simplified way to explain what Keybase does:

1. It creates trustworthy identification: I am who you think I am, so you can trust that you’re communicating with me, not someone impersonating me.
2. It provides a trustworthy way to encrypt that communication: if you encrypt something meant for me, only I can decrypt it.
3. It ensures that you receive exactly what I communicate to you, without modification.

Keybase is very popular among engineers and techies, but we need more. The more people who join Keybase, the closer we are to critical mass and adoption thresholds; the closer we are to practical herd immunity.

5. Use HTTPS On Your Blog

If you have a personal blog or website, please use HTTPS (SSL) for it. There are several ways to do this. I use Netlify to host this blog, so SSL is provided for me. You can also use Let’s Encrypt. Setting up SSL on a personal site used to be hard. It’s now so easy that nobody should use plain-HTTP anymore.

Why? Again, encryption needs to become normalized and expected everywhere. Your website’s users deserve it. Even if they’re just reading a blog, having an HTTPS connection will prevent someone from snooping or modifying the information that is exchanged between their device and the blog server.

Google has a nice article about why every site should use HTTPS.

6. Don’t Use Fingerprint Unlock

Fingerprint readers on computers, phones, and tablets are useful. But it’s risky to unlock the device itself with them. (They are still good for things like unlocking apps that you’d otherwise have to unlock with a long, hard-to-type password.)

The problem is that you can’t change your fingerprint. You should never use something unchangeable for a password, especially on a device that has a lot of sensitive data and access to services. And there are validated cases of fingerprint-locked devices being unlocked by law enforcement, malicious people, etc.

The US government’s attempted power-grab during the 2016 San Bernadino Shooter case, where they tried to use the fear surrounding the case to expand their powers of search and seizure, should give every thoughtful person serious pause.

Use a passcode, and configure your device to reset itself after 10 failed attempts.

Conclusions

Photo Credit

Categories: MySQL

### Percona Server for MySQL 5.5.53-38.5 is now available

MySQL Performance Blog - Wed, 2016-11-23 18:59

Percona announces the release of Percona Server for MySQL 5.5.53-38.4 on November 23, 2016. Based on MySQL 5.5.53, including all the bug fixes in it, Percona Server for MySQL 5.5.53-38.5 is now the current stable release in the 5.5 series.

Percona Server for MySQL is open-source and free. You can find release details in the 5.5.53-38.5 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

Metrics for scalability measurement feature is being built by default but deprecated. Users who have installed this plugin but are not using its capability are advised to uninstall the plugin due to known crashing bugs. This feature was accidentally removed instead of deprecated in the previous release which could cause issues for users that had this feature enabled.

Find the release notes for Percona Server for MySQL 5.5.53-38.5 in our online documentation. Report bugs on the launchpad bug tracker.

Categories: MySQL

### Webinar Q/A: MySQL High Availability with Percona XtraDB Cluster 5.7

MySQL Performance Blog - Tue, 2016-11-22 20:34

In this blog I will provide answers to the questions and queries that some of you have raised during the webinar on Nov 17th.

I would like to say thank you to all of the audience who attended the talk on November 17, 2016. You can also check the recording and slides here.

Q. How is storage distribution done across the node?

A. Each node has independent storage and other resources. There is no sharing of resource. Only the write-sets are replicated.

Q. If write-set propagation fails in some manner is there any retry mechanism?

A. write-set are written to group channel and originating node waits for ack from all the nodes of the cluster. If some nodes fails to respond back then it may be loose its cluster membership. Each node needs to consume all write-sets and in given order only.

Q. Normally, we point only to one write node, can we point in Percona XtraDB Cluster 5.7 to two writing nodes balanced ? Or should the solution be ProxySQL ?

A. Percona XtraDB Cluster (PXC) being multi-master you can execute writes on multiple-nodes. (This is possible even with 5.6). ProxySQL will help you load-balance your traffic but facility to write to any node is inherent to PXC.

Q. Which service call does a joining node have to be to get cluster membership? Is there some kind of registry service?

A. There is no special registry service. This is transparent to the end-user and is handled as part of gcomm communication layer.

Q. Would it be possible to get more information about setting up proxy-sql as we are currently using haproxy but would like a more aware balancer.

A. These articles should help:

Q. Is there a recommended setup for Cluster (White Paper)? I did hear a lot of conflict issues between nodes. So I would like to see if there is a recommended setup.

A. There is not a single way to do this but there are a lot of blogs based on your use-case. Simplest one is 3 node cluster in LAN. Conflicts generally happens if user tend to update same data through multiple nodes. Dis-joint workload distribution will help avoid conflict. Said that if conflicts are inherent part of application or workload Percona XtraDB Cluster (PXC) is well armed to handle it.

Q. What is best way to figure out timeouts for geo clusters?

A. Studying latency and ensuring timeout > latency.

Q. Lets say we are running Percona XtraDB Cluster 5.6 version with 2 cluster. Can i join new node with latest version of Percona XtraDB Cluster 5.7?

A. This scenario is possible as part of Percona XtraDB Cluster (PXC) support rolling upgrade a new node demanding SST from 5.6 node will surely not work. Also, this should be a temporary solution with plan for full upgrade not something you want to continue working with.

Q. Currently i am using Percona XtraDB Cluster 5.6. Mostly i am facing a deadlock situation. When insert query is running on big table. Then Percona trys to synch with another node. At that time ant dml query won’t be executed. So at that time i need to shutdown another node. Then query execution will be fine. Then i need to start another node one by node. I even changed may Gelera/percona wrep_xx configuration, but it did not work. So is this kind of issue solved in Percona XtraDB Cluster 5.7?

A. I am not sure I understood the complete setup but let me try to summarize my understanding. You have DML running on node-1 that is replication to node-2 and node-2 workload is trying to touch the same big-table that is getting replicated write-set. Local transaction may face a abort as replicated transaction always take priority over local running transaction. There shouldn’t be a need to shutdown any of the node. If you still face this problem you can file the detailed report on lp or forum. We can discuss what is going wrong.

Q. I need to make DR platform. which replication will be suitable for this. Do i need to upgrade with Percona XtraDB Cluster 5.7 at DR side or Replication manager requires?

A. For DR you can either use extended cluster so that DR site get instant write-set or setup a new cluster and enable cluster-cluster replication using MySQL MASTER-MASTER async replication. (Given DR one way MASTER-SLAVE should also work). You don’t need to upgrade it but it is better to use consistent and updated version for all node especially mix-match of MASTER-SLAVE may have compatibility issue.

Q. What are the major differences/benefits between Percona XtraDB Cluster 5.7 and MariaDB Cluster with Galera ?

A. Percona XtraDB Cluster (PXC) is 5.7 GA. MariaDB 10.2 is proposed to be GA by Dec 2016. Besides this PXC is fully PS compatible that uses XtraDB engine and there are some small functional/usage difference and stability difference.

Q. How much time a node can be out of a cluster and still can rejoin applying writesets ? How is managed writesets retention ?

A. Time node can be offline without need for SST depends on 2 factors: rate of replicating transaction (including size) and size of galera-cache that caches these write-sets. If you think you need longer offline time and then you should set galera cache accordingly.

Q. Can we have a sample config file for geo-clusters?

A. We will try to come up with one in due-course through an upcoming blog. In the meantime, you can look at existing blogs on the Percona Database Performance blog.

Q. Whats is the limit for max_rows and max_tnx_size in Percona XtraDB Cluster (PXC) 5.7..specially for batch datalaods across multi-region cluster nodes

A. wsrep_max_ws_rows (DEFAULT 0: no limit, max: 1048576). wsrep_max_ws_size (DEFAULT: 2G, range: 1024, 2G)

Q: Does Percona XtraDB Cluster (PXC) support MySQL’s GTIDs?

A. Yes. But for Percona XtraDB Cluster (PXC) replication it uses its own GTID. This blog will help clear confusion.

Q. How does Percona XtraDB Cluster (PXC) compare to MySQL’s Group Replication?

A. Both are trying to solve the same problem, except Percona XtraDB Cluster (PXC) is matured and has been in market for quite sometime. GR is being built.

Q. Does Percona XtraDB Cluster (PXC) have a size limitations? I recently tried to setup a 2TB PXC cluster, however, during load tests there were a few instances where one node got out of sync. The server did a full copy of the data, but could not complete because the load tests kept filling up the gcache.

A. There is no such known limitation. Generally if the node received queue fills up then it will emit a FLOW CONTROL signal. Generally you will receive a queue that is small enough not to fill up gcache. If you still have log files you can share them through LP or forum. We will try to look at them.

Q. How do you perform a major version upgrade. Per MySQL’s documentation, you can not replicate from a major version to the last major version. But it is fine to replicate from one major version to the next. So how would you do this in the cluster?

A. As per MySQL you may face issues if you try to replicate from lower version (master in 5.6) to higher version slave (slave in 5.7) but it is not blocked. Some of the semantics may be different. Percona XtraDB Cluster (PXC) write-sets are different though as it shares binlog events and this write-set format has not changed in 5.7.

Q. Does Galera set a max number of nodes that can be part of the cluster?

A. No such realistic limitation.

Q. Are there docker images with this configured? Dockerhub or something?

A. This should help.

Q. What is the maximum latency that would be supported on the LAN before you would say that running a Percona XtraDB Cluster is not a good idea?

A. I guess this is configurable based on timeout. So there is no such recommended latency threshold for LAN. Lesser the better.

Q. When you start a cluster and bootstrap Node 1, then start Node 2 and Node 3. If you restart Node 1, it will rejoin the cluster but not has a bootstrap state, but it does not matter because it will join a live cluster. If my understanding is correct Bootstrap only matter for the first node starting Is that correct ? What would happens if node 1 restart with bootstrap option, will it force the other node to sync against it ? will it join the running cluster?

A. When you start node-1 for the first time it will create a new cluster and node-2 and node-3 will join the existing cluster. Depending on how node-1 is restarted it can join the existing cluster or create one more independent cluster. Recommended way is to use a valid value of wsrep_cluster_address for all nodes and just pass following extra param –wsrep_new_cluster to the bootstrap node. If you happen to restart this node avoid passing this param. The node will try to join the existing cluster.

Q. What is the overhead of running Percona Monitoring and Management (PMM)

A. Percona Monitoring and Management (PMM) installs an agent on the node to collect a lot of other statistics. From Percona XtraDB Cluster (PXC) perspective it will only run to show a status, so pretty lightweight for PXC.

Q. Is it easy (any procedure) to move from codership galera to Percona XtraDB Cluster (PXC)?

A. I don’t think there is blog about it but they are fully compatible so moving should be easy. I will findout if there is set process for this.

Q. Where is the documentation for Cluster Safe Mode and other new features discussed here?

A. pxc_strict_mode. for PFS you can check this out. ProxySQL and Percona Monitoring and Management (PMM) has blog too.

Q. Is there some integrity issues that a client believes a node is up while this one has lost the cluster ?

A. No known issue.

Q. Is there any limit of running a huge number of databases ? Say several millions ?

A. No known issue.

Q. How are the performance of proxy sql compared with ha proxy?

A. You can check this out.

Q. We use Nagios for monitoring, will a plug-in be added for monitoring the cluster, or will it be only Percona Monitoring and Management (PMM)?

A. Check this out.

Q. “Cross data center replication”. We have two data centers that have a ping latency of 2ms (consistent) and I would like to replicate between the two for DR (disaster recovery) purposes.

A. 2 ms latency between 2 DC and consistent network sounds pretty good. Just tune timeout and things will work.

Q. Do you guys have a sample config files for a quick spin off of a 3 node cluster?

A. This should help.

Q. i see that there is added features like pam authentication,thraed pool which is given fro free in percona can you elobrate on it

A. Percona XtraDB Cluster (PXC) is PS compatible. So any feature that is present in PS will be part of Percona XtraDB Cluster (PXC).

Q. In the example that you showed, where you had a 6 node cluster , where 3 was in Site A and 3 was in Site B. If the WAN link goes down, how does the cluster determine what data set is the master set, once the wan link comes back up after a few hours?

A. In the example I have used 2 DCs. Recommended is to use 3 DCs to avoid split-brain. If you have 6 nodes in 2 DCs and WAN link goes off it will create split-brain and no node will accept workload unless user set weight to form quorum or re-bootstrap primary.

I hope I was able to answer most of the questions/queries. If you happen to have follow-up questions please post them on forum.

Categories: MySQL

### Remembering Alan And Harry

Xaprb, home of innotop - Tue, 2016-11-22 00:39

Harry Weller died suddenly this week. Harry was one of the best venture capital investors in history. He led NEA’s investment in my company, VividCortex. Harry was an advocate and mentor to me, but he was more: in both life and death, he reminded me of Alan Rimm-Kaufman, a boss and mentor who died a handful of years ago.

Harry Weller

My first contact with Harry was when he reached out to me in early 2013. I was fundraising for VividCortex, and many potential investors were highly skeptical. It was discouraging at times, because some of the more deeply technical investors, those who’d run databases themselves, were more skeptical than those without domain knowledge. There were lots of helpful and supportive venture capitalists, but there was also meeting after meeting after meeting of “I don’t see this solving a problem. This isn’t a business. This is niche at best. Nobody has pain that this addresses.” Some were polite about it, some barely concealed their feelings.

In just a few words, Harry proved that he got it in a way few others did. He knew there was a need and opportunity, even though the timing wasn’t right. Over the following couple of years, we stayed in touch frequently. He never wrote more than a sentence or two, but he always did something helpful: an introduction, an encouragement, something. I sent him occasional updates that would fit onto an iPhone screen.

We brought the company through an existential crisis, then reached cash-flow positive and the business’s fundamental strength became obvious in the numbers. At each milestone, Harry wrote me a few words. When we won a huge deal, for example, Harry just wrote “can u call me” and his phone number. When I got him on the phone he celebrated our success. He cheered me more than I cheered myself.

One day his message was simply “is there a dollar amount of investment that gets me X% of the company with X% postmoney option pool? Hello from a ship in the Atlantic.” And that was how it started. I had a pitch deck, I had financials, a roadmap, a slide full of logos in a quadrant with us at the top right corner, all that jazz. But he barely glanced at it. We found a time between two board meetings, and I drove to his house and waited to meet him in person for the first time. He arrived ravenously hungry, and started pitching me on why we should work together while he made a smoothie, which he shared with me.

He was like a kid who couldn’t stand still. He paced restlessly, interrupting himself midsentence to call other partners at NEA and tell them how big this could be, then hanging up and resuming his thoughts. He told me to stop explaining why this was a good idea—he didn’t need convincing. He pushed me to think even bigger. It wasn’t a question of whether we’d make it. Hah! His biggest fear was that we wouldn’t execute fast enough to outrun the competition. He asked a few questions, did some mental math that was startlingly accurate, summed up the business in a few sentences, and told me if we didn’t fund up and sweep the market, our raw efficiency would become obvious to other investors and they’d fund someone to outgun us. “I’m telling, you, I’ve seen this again and again,” he said. “You’ve built a great business, and you’re not going to be able to hide it. In enterprise infrastructure software, my one worry is that you can’t stay ahead of the pack that will come after you. And make no mistake. They will come after you.”

I wasn’t even officially fundraising, but I took his investment and we hit the ground running. Harry frequently called me out of the blue. When I called him I usually got voicemail, then a call back a short while later. I have no idea how he did it. He was on 14 boards and leading the East Coast practice at the world’s largest venture firm, among 99 other things. How can one person have so much bandwidth?

I didn’t have enough time with Harry to get to know him well, not even a year, and only a couple of hours all together. We weren’t close on a personal level. I was still adjusting to some of the ways he worked, still forming opinions about how he operated. He was teaching me to lead more strongly, and the way he did it was brilliantly simple and effective: he told me what the job needed and that if I didn’t do it, a new CEO would. His point was unmistakeable: there is no room in a fast-moving company to leave authority or responsibility vague or let it be owned by the group. It had the effect of pressuring everyone to meet a higher level of performance or leave. I’m still trying to learn how to do that myself.

If I tell the unvarnished truth, I was sometimes nervous of Harry’s motives, because I’d never worked with someone who could instinctively sense an area where I was slightly passive, and feint at it to force me to engage more strongly and produce better results. But in the last couple months, I honestly started to believe he was doing it just to make me be my best.

Then I got the call. Harry was dead. I was stunned.

Alan Rimm-Kaufman

As I said, Harry and I weren’t close, but my reaction was still very emotional, because Harry isn’t the first person to believe in me and create an amazing opportunity before dying unexpectedly. Before Harry, there was Alan Rimm-Kaufman, who shaped my career tremendously.

There were so many parallels and similarities between Harry and Alan, and my personal experience with the two of them.

I joined Alan in 2006 at the Rimm-Kaufman Group, a bootstrapped startup he founded in Charlottesville, Virginia. Alan had an infectious love of life and was unstoppably energetic. He’d enter a room with his face lit up, bubbling with an idea he couldn’t resist sharing. He was unable to stand still. He hopped around like a soccer goalie.

Alan would start a thought and someone else would venture a related idea. Alan would pounce on it. “Yes!” he’d say. With Alan, just as with Harry, you had zero doubt that he believed in you. I sometimes wondered if the ideas were all his anyway, and he just withheld parts of them, letting us complete the thoughts he’d already had so we’d believe in ourselves too.

One day I said I might want to write a book someday. Alan didn’t even blink. “Oh yeah,” he said, as he bounced up and down on my yoga ball. “Oh, YEAH! You’ll be published in less than 18 months.” It wasn’t a question. It was foretold. When the opportunity to coauthor High Performance MySQL appeared a short time later, Alan switched me to half-time work with full-time benefits and gave me a private office to work in. He told me to shut the door in the morning and write until lunchtime. I don’t remember how long this went on—perhaps a year.

Alan also sent me to conferences, where Google tried to recruit me. I didn’t want to move to Silicon Valley. Alan thought I was crazy and sternly told me to go interview with them. The day before I left, he handed me a letter full of reasons I’d be better off not taking the job. It was classic Alan: ten hilarious statistics like the number of poisonous snake species in California versus Virginia. I didn’t take a job at Google, but I got to know many wonderful people there.

Then I broke Alan’s heart anyway: I left to work at Percona. When I walked into his office he knew. I remember him doing his best to be happy for me.

While I got absorbed into my new life, working all hours of the day and night, Alan’s leukemia returned. I later found out it was a big part of why he was so energetic. After it went into remission the first time, he’d attacked life and all of the crazy dreams he had for himself and his family with renewed vigor, no holds barred. This time, the disease was not to be beaten. It was looking hopeful, and he was feeling good, he wrote me in emails. But then, suddenly he was gone in a matter of days.

When I re-read my emails, I realized Alan was asking me to come see him before he died, but I had been too busy to pay attention.

I was devastated at Alan’s death. It was the first time anyone I cared about had died, and Alan had been like a father to me. But far worse, I was consumed with guilt at being too selfish to go see him, wracked with regret at taking him for granted. It took me a long time to forgive myself. Eventually, I realized that the regret itself was a waste. It was just a form of self-hatred so I could feel better for punishing myself. When I recovered enough to see this, I realized Alan would have wanted nothing more than for me to stop wallowing in guilt and live fully as he had. That was part of the reason I left my job to found VividCortex.

Alan and Harry’s Legacy

When Harry died, a flood of thoughts and feelings came back: all of the things I’d processed after Alan’s death. I was reminded of what came from that, my own growth, how the experience of knowing Alan changed me. There are so many parallels in the brief time I had with Harry.

Alan and Harry were intensely focused on all of life, and accomplished extraordinary things. They live on through family, work, religious community, and many other ways including me. I am part of their legacy, and I try to honor them by doing what they did: greet each day with joy, optimism, and energy. Put the pedal to the metal and don’t even think about the brakes.

I was looking forward to seeing Harry soon, and telling him what’s taking shape right now. But I don’t need to, because I already know what he’d do. He’d cheer me, because it’s risky and gutsy and crazy and of course it will work! He’d tell me damn the torpedoes and full speed ahead, he’d tell me to run, run, run! and do it before anyone else does. I know Alan would say exactly the same thing.

Words can never express my gratitude to Alan and Harry. Words are feeble. You know what’s sufficient? Actions, and actions alone.

PS: To the countless others who’ve also believed, supported, helped, and encouraged me: thank you, it means more than I can say, but watch me, and I’ll put my gratitude into action; and someday I hope I can do as you’ve done, too.

Categories: MySQL

### Excel Hacks To Ignore Missing Data

Xaprb, home of innotop - Sun, 2016-11-20 16:11

I’ve done quite a bit of work with Excel over the last few years, and I’ve found a couple of recurring problems when there’s missing or error data in ranges. I’ve had to work around this enough times that I thought it was worth sharing the solutions I’ve used.

Aggregate functions such as MIN() and SUM() often produce errors when they’re defined over a range with missing or bogus data. Although this is good, I often want to create a spreadsheet that operates over a fixed range, with computations based on that range. And I want to avoid complicated IF() or error-handling in long formulas, which make them hard to maintain and understand. In some cases, I’ve found that there’s really no way to solve the problems through that approach anyway. For example, if I use IFERROR() and output a 0 when there’s an error, the next set of cells that are derived from that output will divide-by-zero and it’s just turtles all the way down.

With the AGGREGATE() function, I have found a way to solve some of these issues. In return for using a function whose meaning and syntax is a little confusing, I can centralize all the mess into one place instead of smearing it all over the entire spreadsheet.

Here’s an example. I’ve entered some numbers in a range, and then filled the rest of the range with =LOG(0) to create an error. Then I’ve tried to take the MIN() of B6:B15 in cell C6. This results in an error, of course.

You can solve this with the AGGREGATE() function. You have to look up the documentation for the syntax and meaning of each set of options, but in short, you can specify an aggregate operation, extra arguments, and what to do if there are errors. Here’s how to get the MIN() result I want:

Using OFFSET() To Create Ranges

Sometimes I haven’t been able to work around all the challenges using AGGREGATE() and I’ve needed to specify a variable-sized range. This is possible with the OFFSET() function.

For example, suppose I want to do linear regression using the LINEST() function, but part of the range might contain errors or non-values. After typing in the formula in cell D6 and using the “array formula” command (Control-Shift-Enter), I get the following:

OFFSET() can help. It defines a range relative to a specified cell. You specify where the range begins, and how many rows and columns it covers. As long as the range contains a contiguous set of values (all the non-values are at the end), OFFSET() together with COUNT() can define a variable-sized range that adapts to the valid set of cells. COUNT() counts the number of valid values in the range, so you can use it as the argument to OFFSET() to set the size of the resulting range.

Here’s the solution in action on my example spreadsheet. I’m using OFFSET() to specify the first two parameters to LINEST(), defining the ranges C6:C10 and B6:B10.

The caveat with OFFSET() is that, unlike explictly setting a range with cell identifiers, Excel won’t adjust the range when you do things like deleting rows from the middle of it.

Conclusion

Both of these workarounds introduce their own complexities: with AGGREGATE() you’re adding a more obscure syntax, and with OFFSET() you’re circumventing some of Excel’s features to help maintain and adapt your spreadsheet. In both cases, though, I’ve found that in some circumstances the net outcome is a simpler, more comprehensible spreadsheet overall.

Pic Credit (which has nothing to do with anything, but it’s pretty)

Categories: MySQL

### Percona Server 5.5.53-38.4 is now available

MySQL Performance Blog - Fri, 2016-11-18 21:33

Percona announces the release of Percona Server 5.5.53-38.4 on November 18, 2016. Based on MySQL 5.5.53, including all the bug fixes in it, Percona Server 5.5.53-38.4 is now the current stable release in the 5.5 series.

Percona Server is open-source and free. You can find release details in the 5.5.53-38.4 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

Removed Features:
Bugs Fixed:
• When a stored routine would call an “administrative” command such as OPTIMIZE TABLE, ANALYZE TABLE, ALTER TABLE, CREATE/DROP INDEX, etc. the effective value of log_slow_sp_statements was overwritten by the value of log_slow_admin_statements. Bug fixed #719368.
• Thread Pool thread limit reached and failed to create thread messages are now printed on the first occurrence as well. Bug fixed #1636500.

Other bugs fixed: #1612076, #1633061, #1633430, and #1635184.

Find the release notes for Percona Server 5.5.53-38.4 in our online documentation. Report bugs on the launchpad bug tracker.

Categories: MySQL

### WiredTiger B-Tree versus WiredTiger In-Memory: Q & A

MySQL Performance Blog - Fri, 2016-11-18 18:15

In this blog, I will provide answers to the Q & A for the WiredTiger B-Tree versus WiredTiger In-Memory webinar.

First, I want to thank everybody for attending the October, 13 webinar. The recording and slides for the webinar are available here. Below is the list of questions that I wasn’t able to fully answer during the webinar, with responses:

Q: Does the In-Memory storage engine have an oplog? Do we need more RAM if the oplog is set to be bigger?
Q: So we turn off the oplog?
Q: How is data replicated without oplog? Do you confound it with journaling?

A: Percona Memory Engine for MongoDB can be started with or without oplog, depending on whether it started as part of a replica set or standalone (you cannot explicitly turn oplog on or off). But if created, oplog will be stored in memory as well. You can still control its size with the option --oplogSize .

The recovery log (journal) is disabled for the Percona Memory Engine.

Q: After a crash of the In-Memory storage engine, does it need a complete initial sync? Means, cloning all databases?

A: Yes.

Q: WiredTiger reserves 50% of RAM for de-compression. Is this also true for the In-Memory engine?

A: Where did you find this information? Please point to its location in the docs in the comments section below. I asked Percona developers to confirm or deny this for the Percona Memory Engine, and this was their answer:

WT decompresses data block-wise, and each block is of some reasonable size (usual numbers are couple of Megs, let’s say). Decompressor knows the size of uncompressed data by reading this info from compressed block (this info is stored during compression). It creates an extra buffer of uncompressed block size, decompresses data into this buffer, then uses that decompressed buffer and frees the initial one. So there’s no reserve of memory for either compression or decompression, and no docs stating that.

Please note that this comment applies only to block compression, which is only used during disk I/O when WiredTiger reads and writes blocks, thus not available for Percona Memory Engine.

Q: There is no compression of data in this engine?

A: The Percona Memory Engine uses only prefix compression for indexes. Theoretically, it can use other types of compression: dictionary and Huffman (but they both disabled in MongoDB).

Q: With all the data in memory, is there much benefit to having indexes on the data?

A: Yes, because with index access you will read less data. While reading from memory is much faster than from disk, it is faster to read just few rows from memory instead of scanning millions.

Q: Our db is 70g. Will we need 70g memory to use Percona In-Memory?
Q: How much memory should be allocated for 70g db size?

A: What storage engine do you use? How do you calculate size? If this is WiredTiger and you count the space it allocates, answer is “yes, you need 70G RAM to use Percona Memory Engine.”

Q: What is the difference in size of data between WiredTiger on disks versus WiredTiger In-Memory?

A: There is no difference: the size is same. Please note that WiredTiger (on which the Percona Memory Engine is based) itself can additionally allocate up to 50% of the amount specified in the --inMemorySize option. You can check db.serverStatus().inMemory.cache to find out how much of the specified memory is used for storing your data. "bytes currently in the cache"  shows the total number of bytes occupied by the physical representation of all MongoDB’s databases, and "maximum bytes configured"  shows what is passed in option --inMemorySize. The difference between the two can be used to calculate the amount of memory in bytes available.

Q: What is the way to convert data from disk to In-Memory? Using mongodump and rebuild the indexes?

A: Yes

Q: An enhancement request is to enable regular and In-Memory engines on the same MongoDB instance.

A: This is a MongoDB limitation, but noted and reported for Percona at https://jira.percona.com/browse/PSMDB-88.

Categories: MySQL

### Help Us Shape the Future of Percona

MySQL Performance Blog - Thu, 2016-11-17 19:05

Let us know what you think about Percona, and what we should be thinking about for the future.

Over the last ten years, Percona has grown considerably. We’ve moved from being a strictly MySQL company, to a company that supports MongoDB and other open source databases. Percona Live and Percona Live Europe have become key meeting places for the open source community, and now are important hubs for learning about and discussing open source database solutions.

As we look forward to the next ten years of business, we want to get an idea of what you think of us. As we plan for the future, we’d like to hear about your experience with Percona today and get your input on how we can continue to evolve.

To achieve that end, we’ve put together a survey of questions about us, our services, our products and the open source community’s perception of us. We would appreciate you taking the time to fill it out so we can know your thoughts. Your feedback helps us shape our company and grow the community.

Take the survey here: http://survey.newkind.com/r/rUkjDHPd

It should take 10-15 minutes to complete and will remain open until Friday, Dec. 2. Thanks again for helping us prepare for the future.

Categories: MySQL