MySQL

Percona Monitoring and Management (PMM) Information Script

MySQL Performance Blog - Tue, 2016-11-01 20:30

This blog post discusses an information script for the Percona Monitoring and Management (PMM) tool.

In recent news, we announced the fresh-of-the-press Percona Monitoring and Management (or PMM for short) platform. Given the interaction of the different components that together make up PMM, I developed a script that helps provide you information about the status of your PMM installation.

You can use this script yourself, or one of our support might point you to this page to obtain the information they need to troubleshoot an issue you are experiencing.

You will likely want to execute this script once on the PMM server (i.e., the server on which you installed the docker image), and once on the client (i.e., where you installed the PMM client rpm/apt package), if they are not the same (virtual) machine. It provides a different output for each. When sending this information back to us, please ensure to identify which output belongs to which machine (either the server or the client).

To get/run the script, use (please note that this script requires sudo privileges):

wget https://raw.githubusercontent.com/Percona-QA/percona-qa/master/pmm-info.sh && sh ./pmm-info.sh

If you would like to examine the script contents before executing it, you can split the command:

wget https://raw.githubusercontent.com/Percona-QA/percona-qa/master/pmm-info.sh vi pmm-info.sh sh ./pmm-info.sh

If you have both the PMM server and the PMM client on a single machine, the output looks similar to the following:

[roel@localhost ~]$ wget https://raw.githubusercontent.com/Percona-QA/percona-qa/master/pmm-info.sh && sh ./pmm-info.sh --2016-11-01 09:49:22-- https://raw.githubusercontent.com/Percona-QA/percona-qa/master/pmm-info.sh Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.100.133 Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.100.133|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 1451 (1.4K) [text/plain] Saving to: ‘pmm-info.sh’ 100%[=========================================================================================================================================>] 1,451 --.-K/s in 0s 2016-11-01 09:49:23 (218 MB/s) - ‘pmm-info.sh’ saved [1451/1451] QA PMM Info Script v0.07 ==================== uname -a Linux localhost.localdomain 3.10.0-123.13.2.el7.x86_64 #1 SMP Thu Dec 18 14:09:13 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux ==================== /proc/version Linux version 3.10.0-123.13.2.el7.x86_64 (builder@kbuilder.dev.centos.org) (gcc version 4.8.2 20140120 (Red Hat 4.8.2-16) (GCC) ) #1 SMP Thu Dec 18 14:09:13 UTC 2014 ==================== OS Release (filtered cat /etc/*-release): CentOS Linux release 7.2.1511 (Core) HOME_URL="https://www.centos.org/" ID="centos" ID_LIKE="rhel fedora" NAME="CentOS Linux" PRETTY_NAME="CentOS Linux 7 (Core)" VERSION="7 (Core)" VERSION_ID="7" ==================== Docker release (docker --version): Docker version 1.10.3, build cb079f6-unsupported ==================== SELinux status if present (sestatus): SELinux status: enabled SELinuxfs mount: /sys/fs/selinux SELinux root directory: /etc/selinux Loaded policy name: targeted Current mode: enforcing Mode from config file: enforcing Policy MLS status: enabled Policy deny_unknown status: allowed Max kernel policy version: 28 ==================== PMM server images (sudo docker images | grep pmm): docker.io/percona/pmm-server 1.0.5 0eade99a1612 2 weeks ago 652.9 MB docker.io/percona/pmm-server 1.0.4 1c83d650105e 6 weeks ago 677.3 MB docker.io/percona/pmm-server 1.0.4-dev20160908.24845ea 4406c13d0ba3 7 weeks ago 676 MB ==================== PMM server state (sudo docker ps -a | grep pmm): fdf5e6adca7e percona/pmm-server:1.0.4 "/opt/entrypoint.sh" 5 days ago Exited (137) 5 days ago pmm-server3 843a2ee31c96 percona/pmm-server:1.0.4 "/opt/entrypoint.sh" 5 days ago Created pmm-server2 f075314b529f percona/pmm-server:1.0.4 "/bin/true" 5 days ago Created pmm-data2 2090c072b56a percona/pmm-server:1.0.5 "/opt/entrypoint.sh" 6 days ago Up 7 minutes 0.0.0.0:80->80/tcp, 443/tcp pmm-server 653fb58ce723 percona/pmm-server:1.0.5 "/bin/true" 6 days ago Created pmm-data ==================== Exporter status (ps -ef | grep exporter): root 2748 1 0 09:44 ? 00:00:00 /bin/sh -c /usr/local/percona/pmm-client/node_exporter -web.listen-address=192.168.0.13:42000 -collectors.enabled=diskstats,filesystem,loadavg,meminfo,netdev,netstat,stat,time,uname,vmstat >> /var/log/pmm-linux-metrics-42000.log 2>&1 root 2749 1 0 09:44 ? 00:00:00 /bin/sh -c /usr/local/percona/pmm-client/mysqld_exporter -collect.auto_increment.columns=true -collect.binlog_size=true -collect.global_status=true -collect.global_variables=true -collect.info_schema.innodb_metrics=true -collect.info_schema.processlist=true -collect.info_schema.query_response_time=true -collect.info_schema.tables=true -collect.info_schema.tablestats=true -collect.info_schema.userstats=true -collect.perf_schema.eventswaits=true -collect.perf_schema.file_events=true -collect.perf_schema.indexiowaits=true -collect.perf_schema.tableiowaits=true -collect.perf_schema.tablelocks=true -collect.slave_status=true -web.listen-address=192.168.0.13:42002 >> /var/log/pmm-mysql-metrics-42002.log 2>&1 root 2750 2748 1 09:44 ? 00:00:37 /usr/local/percona/pmm-client/node_exporter -web.listen-address=192.168.0.13:42000 -collectors.enabled=diskstats,filesystem,loadavg,meminfo,netdev,netstat,stat,time,uname,vmstat root 2751 2749 0 09:44 ? 00:00:05 /usr/local/percona/pmm-client/mysqld_exporter -collect.auto_increment.columns=true -collect.binlog_size=true -collect.global_status=true -collect.global_variables=true -collect.info_schema.innodb_metrics=true -collect.info_schema.processlist=true -collect.info_schema.query_response_time=true -collect.info_schema.tables=true -collect.info_schema.tablestats=true -collect.info_schema.userstats=true -collect.perf_schema.eventswaits=true -collect.perf_schema.file_events=true -collect.perf_schema.indexiowaits=true -collect.perf_schema.tableiowaits=true -collect.perf_schema.tablelocks=true -collect.slave_status=true -web.listen-address=192.168.0.13:42002 roel 4445 4392 0 10:24 pts/0 00:00:00 grep exporter ==================== PMM agent (sudo pmm-admin --version): 1.0.5 ==================== PMM info (sudo pmm-admin info): pmm-admin 1.0.5 PMM Server | 192.168.0.13 Client Name | localhost.localdomain Client Address | 192.168.0.13 Service manager | linux-systemd ==================== PMM network check (sudo pmm-admin check-network): PMM Network Status Server | 192.168.0.13 Client | 192.168.0.13 * Client --> Server --------------- ------- SERVER SERVICE STATUS --------------- ------- Consul API OK QAN API OK Prometheus API OK Connection duration | 102.017µs Request duration | 211.962µs Full round trip | 313.979µs * Client <-- Server -------------- ----- ---------------------- ------- SERVICE TYPE NAME REMOTE ENDPOINT STATUS -------------- ----- ---------------------- ------- linux:metrics test 192.168.0.13:42000 OK mysql:metrics test 192.168.0.13:42002 OK ==================== PMM list (sudo pmm-admin list): pmm-admin 1.0.5 PMM Server | 192.168.0.13 Client Name | localhost.localdomain Client Address | 192.168.0.13 Service manager | linux-systemd -------------- ----- ------------ -------- --------------------------------------------------------------------------------------------- ------------------------ SERVICE TYPE NAME CLIENT PORT RUNNING DATA SOURCE OPTIONS -------------- ----- ------------ -------- --------------------------------------------------------------------------------------------- ------------------------ linux:metrics test 42000 YES - mysql:queries test 42001 YES root:***@unix(/sda/COMP8-PS131016-percona-server-5.6.33-78.0-linux-x86_64-debug/socket.sock) query_source=perfschema mysql:metrics test 42002 YES root:***@unix(/sda/COMP8-PS131016-percona-server-5.6.33-78.0-linux-x86_64-debug/socket.sock)

Support might also ask you to run the extended version of the script. This produces a lot of output, and it easier to sent the output to a log file:

wget https://raw.githubusercontent.com/Percona-QA/percona-qa/master/pmm-info.sh && sh ./pmm-info.sh ext > pmm-server.log  # Please execute this on the PMM server, and sent us pmm-server.log wget https://raw.githubusercontent.com/Percona-QA/percona-qa/master/pmm-info.sh && sh ./pmm-info.sh > pmm-client.log   # Please execute this on the PMM client, and sent us pmm-client.log

Categories: MySQL

The Future of TokuDB at Percona

MySQL Performance Blog - Tue, 2016-11-01 16:42

In this blog post, I’ll discuss the future of TokuDB at Percona. Spoiler: solid.

As soon as we announced the fact that MyRocks was coming to Percona Server for MySQL at Percona Live Europe, rumors appeared that this means we’re going to phase out TokuDB at the same time.  

I can understand why those rumors would start: just a few months ago we deprecated Fractal Trees technology (called PerconaFT) in favor of MongoRocks and RocksDB for Percona Server for MongoDB.

As much as this might look like the same situation as with PerconaFT, TokuDB is very different. PerconaFT was actually a new port of Fractal Trees technology for MongoDB. It used the MongoDB storage engine API and focused on replacing TokuMX (a full fork of an earlier version of MongoDB). PerconaFT was new, and MongoRocks was already well-established and battle-tested by Parse. The MongoDB internal design was also much more friendly towards the RocksDB storage engine than Fractal Trees technology (as David Murphy explains here).    

Our research revealed that PerconaFT had very few current users, and MongoRocks was a superior choice for new users. This is why we depreciated it.

For MySQL, TokuDB is a rather mature storage engine. It has been in production for more than five years and is trusted by many businesses and large enterprises (in industries ranging from banking and finance to gaming) for use with critical applications.

MyRocks however, with as much promise as it shows, has not been extensively battle-tested outside of Facebook. And as it is hard to beat “Facebook scale,” it hasn’t exposed its performance under a large variety of workloads. There are also some known limitations in MyRocks that need work.

Ultimately though, I believe MyRocks has a great future, and we at Percona are going to take part in making this future reality. But this does not mean we plan to immediately abandon TokuDB, or place it on “life support”.    

You can see new development going on for TokuDB: we have dramatically improved block allocation performance and added a new, more convenient TokuDB file layout option. We are also working on adding support for new compression algorithms in TokuDB, support for Performance Schema and refactoring the checkpoint process to ensure uniform performance on all workloads. It is true TokuDB is not changing as rapidly as RocksDB, but that is to be expected – as the more mature storage engine, it should embrace stability first and foremost.

Our overall principle at Percona is to keep our ego in check. We want to provide the best open source ecosystem solutions available to our customers, whether they were developed in-house or not. This means that within 3-5 years MyRocks could possibly be superior to TokuDB in functionality and performance for all imaginable workloads. Or an even better open source storage engine technology might get developed and come to market. If or when this happens, we will consider depreciating TokuDB in favor of such technology – while providing the customers with enough notice, tools and support to ensure they can successfully migrate.

What this really means is that TokuDB is still being actively supported and developed for at least Percona Server 5.7 and Percona Server 8.0 (and longer). We will continue this as long as a reasonable use case remains for this technology.

Please feel free to contact us with any questions, or add them in the comments below.

Categories: MySQL

Percona Server for MongoDB 3.2.10-3.0 is now available

MySQL Performance Blog - Mon, 2016-10-31 17:58

Percona announces the release of Percona Server for MongoDB 3.2.10-3.0 on October 31, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB 3.2.10-3.0 is an enhanced, open-source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.2 protocol and drivers. It extends MongoDB with MongoRocks, Percona Memory Engine, and PerconaFT storage engine, as well as enterprise-grade features like external authentication and audit logging at no extra cost. Percona Server for MongoDB requires no changes to MongoDB applications or code.

Note:

We deprecated the PerconaFT storage engine. It will not be available in future releases.

This release is based on MongoDB 3.2.10 and includes the following additional new features and improvements:

New Features

  • Universal Hot Backup
    This release introduces an integrated hot backup system for the default WiredTiger and alternative MongoRocks engine. It creates a physical data backup on a running server without performance degradation.
  • Profiling Rate Limit
    Rate limiting enables to seed logged queries and thus decrease the impact of profiling on database performance.

Bug Fixes

  • Fixed crash when running out of WiredTiger cache under Percona Memory Engine.

The release notes are available in the official documentation.

 

Categories: MySQL

Webinar Wednesday November 2: MongoDB Backups, All Grown Up!

MySQL Performance Blog - Mon, 2016-10-31 17:42
Please join us on Wednesday, November 2, 2016 at 10:00 am PDT / 1:00pm EDT (UTC-7) for the webinar MongoDB Backups, All Grown Up, featuring David Murphy, Percona’s Mongo Practice Manager.   

It has been a long road to stable and dependable backups in the MongoDB space. This webinar covers the current types of backups and their limitations when it comes to sharding. From there we will move into why you can’t be consistent with a single node, and how you can take sharded or unsharded consistent backups. 

The webinar also covers more about the “mongodb_consistent_backup.py” tool, and the features it offers to the open source community: how to use it, what it looks like and why it’s our preferred backup methodology.

Click here to register for the webinar MongoDB Backups, All Grown Up!

David Murphy, MongoDB Practice Manager

David joined Percona in October 2015 as Practice Manager for MongoDB. Prior to that, David joined the ObjectRocket by Rackspace team as the Lead DBA in Sept 2013. With the growth involved with a any recently acquired startup, David’s role covered a wide range from evangelism, research, run book development, knowledgebase design, consulting, technical account management, mentoring and much more.

Prior to the world of MongoDB, David was a MySQL and NoSQL architect at Electronic Arts. There, he worked with some of the largest titles in the world like FIFA, SimCity, and Battle Field providing tuning, design, and technology choice responsibilities. David maintains an active interest in database speaking and exploring new technologies.

Categories: MySQL

The Response Time Stretch Factor

Xaprb, home of innotop - Sun, 2016-10-30 14:00

Computer systems, and for that matter all types of systems that receive requests and process them, have a response time that includes some time waiting in queue if the server is busy when a request arrives. The wait time increases sharply as the server gets busier. For simple systems there is a simple equation that describes this exactly, but for more complicated systems this equation is only approximate. This has rattled around in my brain for a long time, and rather than keeping my notes private I’m sharing them here (although since I’m still trying to learn this stuff I may just be putting my ignorance on full display).

I’ll skip all the derivations and go through only the basics to get to the results.

The Stretch Factor Heuristic and Exact Formula

The equation describing the curve above is derived as follows. When a request arrives at the server, it will have to wait time \(S\), the service time, to be completed. But if the server is busy, which happens with some probability, then it will be delayed some additional wait time \(W\) for the request in process, and potentially for other requests are already waiting, before it enters service. The total time is the residence time, \(R = W + S\).

The wait time \(W\) is some fraction of the total residence time, which can be at most 100%, and if this is denoted by \(\rho\) then \(W = \rho R\). Thus,

$$ R = W + S = \rho R + S $$

Which, when rearranged and divided by service time to make it a relative “stretch factor,” becomes

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

None of this is original; I’ve basically cribbed this from Neil Gunther’s book Analyzing Computer Systems Performance with Perl::PDQ. Later in that same book, Gunther shows the derivation of a related formula for the stretch factor in a multiserver queue with \(m\) servers,

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

Where \(\rho\) denotes server utilization. This heuristic approximation does arise analytically, but is not exact when there are more than 2 servers. It underestimates wait time when utilization is high, especially with large numbers of servers (say, 64). The exact solution is given by

$$ R = \frac{C(m, \rho) S}{m(1-\rho)} + S $$

Where the first term is just \(W\), and the function \(C(m,\rho)\) is the Erlang C function. If we put it all together and rearrange into “stretch factor” form, the Erlang equation for stretch factor is

$$ R(m, \rho) = 1 + \frac{ \frac{(m \rho)^m}{m!} }{ (1-\rho) \sum_{n=0}^{m-1} \frac{(m \rho)^n}{n!} + \frac{(m \rho)^m}{m!} } \frac{1}{m(1-\rho)} $$

If you’d like to see how this looks, check out this Desmos calculator.

Now here’s the part my brain has been trying to connect, almost idly in shower-thought time, for a while. If intuition led from one direction to the heuristic approximation \(R=\frac{1}{1-\rho^m}\), and an exact derivation leads to a formula that in the single- and dual-server case is the same as the heuristic, then what is the heuristic missing to extend to an exact multiserver queueing system? Can it be extended, or is it just a coincidence that it’s an exact solution for \(m=1, m=2\)?

A few trains of thought have sprung into my mind.

First, I observed that the Erlang C formula includes \(m!\), and it happens to be the case that 1! = 1, and 2! = 2. A clue, or a distraction? What if I add a term to the heuristic, multiplied by \(m/m!\) or similar, which would just reduce to the same thing for the single- and dual-server cases? What form would that term take?

Secondly, what if I work backwards from the Erlang formula and see if it reduces to a different form of the heuristic?

Thirdly, what if the heuristic’s exactness for the base cases is just a coincidence after all? In that case, perhaps a new, simpler approximation to the Erlang formula is waiting to be invented. Approximations are highly useful to me in tools such as spreadsheets and the like, or even in using intuition, which is workable with the heuristic form. Approximations are easy to think about and a lot easier to type and troubleshoot. They’re also faster to compute, should performance matter.

I’ll take each of these cases in turn.

A Missing Term

If the heuristic is exact for 1- and 2-server cases because of the coincidence that the factorial function for these values is the value itself, then what is the missing term? What shape might it have?

To gain some intuition about this, I wrote a simple Desmos calculator to show the value of the hypothetical “missing term” in the context of the heuristic’s value. In other words, the heuristic’s error function. Here’s a picture of that for several values of \(m\). Red is 1 server, green is 8, purple is 16, orange is 64.

Note that I showed utilization extending out beyond the value 1 because the shape of the function is interesting, but that value is impossible—a server can never be more than 100% utilized. What’s interesting, to me anyway, is that the error function looks kind of like the PDF of a Gamma distribution. I’ll leave that thought there.

I’m not sure what form a term including, say, \(m/m!\) would take. This is something I haven’t explored a lot yet. TODO.

Reducing Erlang

What does the Erlang formula reduce to in the \(m=1\) case? The Erlang C formula itself reduces to \(\rho\), and when decorated with the additional stuff to get it into stretch-factor form, it reduces to

$$ R(1, \rho) = 1 + \frac{\rho}{1-\rho} $$

Which is just a rearrangement of the heuristic function. (Interestingly, Wolfram Alpha will simplify it to include the Gamma function and list the heuristic as an approximation. Gamma again, though Gamma function and Gamma distribution are different. The Gamma function is closely related to the factorial function.)

In the \(m=2\) case, if I’m doing my algebra right, the Erlang C function simplifies to

$$ C(2, \rho) = \frac{2\rho^2}{ (1-\rho) + (1-\rho)2\rho + 2\rho^2} $$

Which further simplifies to \(\frac{2\rho^2}{\rho+1}\), which when rewritten into stretch-factor form, becomes

$$ R(2, \rho)=1+\frac{2\rho^2}{\rho+1} \frac{1}{2(1-\rho)} $$

Which is exactly \(\frac{1}{1-\rho^2}\), the heuristic form.

At \(m=3\) and above, the heuristic is only approximate. What does the Erlang form reduce to for the first of those cases? Does it result in the missing term that will extend to 4 and beyond too? I haven’t done that yet. TODO.

Approximations to Erlang, Based on the Heuristic

Approximations to complicated equations are often really useful. You use them all the time, although you may not know it. For example, a computer uses approximations to calculate functions such as sine, square root, and logarithm. The field of numeric methods is dedicated to studying such algorithms and their errors.

In 1977, Sakasegawa discovered an approximation to the length of a queue, which is more accurate than the heuristic function, but wasn’t derived analytically. You can find the paper here. In a nutshell, his method was to begin with a well-known queueing theory formula derived by Pollaczek and Khinchine, which is valid (exact) for M/G/1 systems, that is, single-server queueing systems in some specific cases. Sakasegawa observed the error curve at various parameters and essentially did a least-squares sum of errors regression to arrive at an approximation for the queue length, which in the simplest types of queueing systems reduces to:

$$ L_q \approx \frac{ \rho^{\sqrt{2(m+1)}} }{ 1-\rho} $$

In “stretch factor” form, this becomes

$$ R(m, \rho) \approx 1+\frac{\rho^{\sqrt{2(m+1)}}}{\rho m(1-\rho)} $$

This is such an accurate approximation that it’s more than good enough for real-life applications, and I use it all the time. (It’s hard to put Erlang’s formula into a spreadsheet, because it has iterative computations.)

Given the usefulness and simplicity of approximations, perhaps an approximation to the stretch factor can be derived from the heuristic form \(1/(1-\rho^m)\). This idea appeals to me because it might lead to insights about what’s disappeared or simplified out of the exact Erlang formula in the base case.

One possible way to do this is to approximate the error function of the heuristic. I already mentioned that it might be possible to do this with the Gamma distribution’s PDF. Finding an approximation to that, and then adding or multiplying by it, might result in a usable approximation.

Another idea is a sigmoid such as the classic logistic function. In fact, if I wanted to approximate the error in the range (0,1) this isn’t too bad an approximation for \(m=16\), for example:

$$ \frac{.5}{1+e^{-10(x-1)}} $$

Finally, instead of adding a term or multiplying the heuristic by a term, perhaps it’s the \(\rho^m\) portion in the denominator that needs to be tweaked. A little analysis led me to the following conclusions.

The error at \(m>3\) could be explained by the exponent \(m\) being too large. If so, then the correct value for the exponent could be a function of \(\rho\), and an adjustment to it would need to be of the form

$$ A_{exp} = \frac{log\left( \frac{E(\rho)-1}{E(\rho)} \right)}{log(\rho)} $$

Where \(E(\rho)\) is the Erlang formula for the stretch factor. I arrived at this by solving the error function for \(\rho\). For convenience, this can be divided by \(m\) to normalize it relative to the number of servers. I’ve made a Desmos calculator illustrating the shape of this adjustment term for 1, 4, 8, and 16 servers:

One of the challenges with this is that due to the limitations of floating-point math in computers, the heuristic function appears to have no error at low utilization. I think it does, but it’s just a small value. That’s why the shape of that curve has a discontinuity at low utilization.

An interesting observation: If you zoom out and remove the limits on the range of values plotted, you get something that looks like a part of the Gamma function. Is this a coincidence? Could the Gamma function be used as an approximation to this error? Or is the missing term a Gamma function, which would result in an exact solution? TODO.

Another way to nudge the heuristic to approximate the Erlang residence time stretch factor would be to examine whether the base, \(\rho\), is too large or too small. Following a similar train of thought as before and solving the error function for the number of servers, I found that the error would need to be of the form

$$ A_{base} = \left( \frac{E(\rho)-1}{E(\rho)}\right)^{1/m} $$

Normalizing this relative to \(\rho\) by dividing, I got a function that has similar discontinuities as before, and is of the following shape. It looks like it might be possible to approximate with something like a quadratic from 0 to 1, but if you zoom out further, it looks more like… wait for it… part of the Gamma function. You can see this on Desmos.

I experimented with this in a different way, by trying to approximate \(A_{base}\) directly. I just guessed at its shape and came up with the following, which isn’t too far off for \(2<m<5\):

$$ A_{base} \approx \rho - \frac{2}{15} \sqrt{m} (\rho-1)\rho $$

You can see this shape, compared with the actual \(A_{base}\), at this Desmos. And this one shows what this looks like when included as a term in the heuristic stretch factor.

Red is Erlang, blue is my heuristic, and black dashed is Gunther’s. Don’t be fooled; mine may look better, but if you examine high utilizations you’ll see it’s much worse. Small errors in the approximation to the error function make big differences in the result. (This is true if the error is defined as a function of number of servers, too.)

Conclusions

There’s a lot of outright superstition in this blog post, but hopefully some of the intuition I’m trying to develop comes through as well. Guessing at approximations (and seeing Gamma-things everywhere, like shapes in the clouds) is probably more time-wasting than productive, but I find that by playing with things I learn a lot. There’s something still unfinished in my mind, something unsatisfactory about coming towards an exact solution from two directions, finding two different forms, and finding that one of them runs aground at a certain point, even though it’s just a simplification of the other under some circumstances. You might replace “even though” with “because” in the previous sentence, and be satisfied, but as for me I feel there’s still something to be learned here. And potentially a useful result might come out of it, even if it’s only a fast approximation like Sakasegawa’s.

Besides, I just enjoy exploring math and its shapes; this is a great form of relaxation or stress relief for me when I want to concentrate on something so as to put other things out of my mind. Hopefully there’s a kindred soul out there who finds this interesting too. If so, hello, and enjoy!

Categories: MySQL

The Response Time Stretch Factor

Xaprb, home of innotop - Sun, 2016-10-30 14:00

Computer systems, and for that matter all types of systems that receive requests and process them, have a response time that includes some time waiting in queue if the server is busy when a request arrives. The wait time increases sharply as the server gets busier. For simple systems there is a simple equation that describes this exactly, but for more complicated systems this equation is only approximate. This has rattled around in my brain for a long time, and rather than keeping my notes private I’m sharing them here (although since I’m still trying to learn this stuff I may just be putting my ignorance on full display).

I’ll skip all the derivations and go through only the basics to get to the results.

The Stretch Factor Heuristic and Exact Formula

The equation describing the curve above is derived as follows. When a request arrives at the server, it will have to wait time \(S\), the service time, to be completed. But if the server is busy, which happens with some probability, then it will be delayed some additional wait time \(W\) for the request in process, and potentially for other requests are already waiting, before it enters service. The total time is the residence time, \(R = W + S\).

The wait time \(W\) is some fraction of the total residence time, which can be at most 100%, and if this is denoted by \(\rho\) then \(W = \rho R\). Thus,

$$ R = W + S = \rho R + S $$

Which, when rearranged and divided by service time to make it a relative “stretch factor,” becomes

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

None of this is original; I’ve basically cribbed this from Neil Gunther’s book Analyzing Computer Systems Performance with Perl::PDQ. Later in that same book, Gunther shows the derivation of a related formula for the stretch factor in a multiserver queue with \(m\) servers,

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

Where \(\rho\) denotes server utilization. This heuristic approximation does arise analytically, but is not exact when there are more than 2 servers. It underestimates wait time when utilization is high, especially with large numbers of servers (say, 64). The exact solution is given by

$$ R = \frac{C(m, \rho) S}{m(1-\rho)} + S $$

Where the first term is just \(W\), and the function \(C(m,\rho)\) is the Erlang C function. If we put it all together and rearrange into “stretch factor” form, the Erlang equation for stretch factor is

$$ R(m, \rho) = 1 + \frac{ \frac{(m \rho)^m}{m!} }{ (1-\rho) \sum_{n=0}^{m-1} \frac{(m \rho)^n}{n!} + \frac{(m \rho)^m}{m!} } \frac{1}{m(1-\rho)} $$

If you’d like to see how this looks, check out this Desmos calculator.

Now here’s the part my brain has been trying to connect, almost idly in shower-thought time, for a while. If intuition led from one direction to the heuristic approximation \(R=\frac{1}{1-\rho^m}\), and an exact derivation leads to a formula that in the single- and dual-server case is the same as the heuristic, then what is the heuristic missing to extend to an exact multiserver queueing system? Can it be extended, or is it just a coincidence that it’s an exact solution for \(m=1, m=2\)?

A few trains of thought have sprung into my mind.

First, I observed that the Erlang C formula includes \(m!\), and it happens to be the case that 1! = 1, and 2! = 2. A clue, or a distraction? What if I add a term to the heuristic, multiplied by \(m/m!\) or similar, which would just reduce to the same thing for the single- and dual-server cases? What form would that term take?

Secondly, what if I work backwards from the Erlang formula and see if it reduces to a different form of the heuristic?

Thirdly, what if the heuristic’s exactness for the base cases is just a coincidence after all? In that case, perhaps a new, simpler approximation to the Erlang formula is waiting to be invented. Approximations are highly useful to me in tools such as spreadsheets and the like, or even in using intuition, which is workable with the heuristic form. Approximations are easy to think about and a lot easier to type and troubleshoot. They’re also faster to compute, should performance matter.

I’ll take each of these cases in turn.

A Missing Term

If the heuristic is exact for 1- and 2-server cases because of the coincidence that the factorial function for these values is the value itself, then what is the missing term? What shape might it have?

To gain some intuition about this, I wrote a simple Desmos calculator to show the value of the hypothetical “missing term” in the context of the heuristic’s value. In other words, the heuristic’s error function. Here’s a picture of that for several values of \(m\). Red is 1 server, green is 8, purple is 16, orange is 64.

Note that I showed utilization extending out beyond the value 1 because the shape of the function is interesting, but that value is impossible—a server can never be more than 100% utilized. What’s interesting, to me anyway, is that the error function looks kind of like the PDF of a Gamma distribution. I’ll leave that thought there.

I’m not sure what form a term including, say, \(m/m!\) would take. This is something I haven’t explored a lot yet. TODO.

Reducing Erlang

What does the Erlang formula reduce to in the \(m=1\) case? The Erlang C formula itself reduces to \(\rho\), and when decorated with the additional stuff to get it into stretch-factor form, it reduces to

$$ R(1, \rho) = 1 + \frac{\rho}{1-\rho} $$

Which is just a rearrangement of the heuristic function. (Interestingly, Wolfram Alpha will simplify it to include the Gamma function and list the heuristic as an approximation. Gamma again, though Gamma function and Gamma distribution are different. The Gamma function is closely related to the factorial function.)

In the \(m=2\) case, if I’m doing my algebra right, the Erlang C function simplifies to

$$ C(2, \rho) = \frac{2\rho^2}{ (1-\rho) + (1-\rho)2\rho + 2\rho^2} $$

Which further simplifies to \(\frac{2\rho^2}{\rho+1}\), which when rewritten into stretch-factor form, becomes

$$ R(2, \rho)=1+\frac{2\rho^2}{\rho+1} \frac{1}{2(1-\rho)} $$

Which is exactly \(\frac{1}{1-\rho^2}\), the heuristic form.

At \(m=3\) and above, the heuristic is only approximate. What does the Erlang form reduce to for the first of those cases? Does it result in the missing term that will extend to 4 and beyond too? I haven’t done that yet. TODO.

Approximations to Erlang, Based on the Heuristic

Approximations to complicated equations are often really useful. You use them all the time, although you may not know it. For example, a computer uses approximations to calculate functions such as sine, square root, and logarithm. The field of numeric methods is dedicated to studying such algorithms and their errors.

In 1977, Sakasegawa discovered an approximation to the length of a queue, which is more accurate than the heuristic function, but wasn’t derived analytically. You can find the paper here. In a nutshell, his method was to begin with a well-known queueing theory formula derived by Pollaczek and Khinchine, which is valid (exact) for M/G/1 systems, that is, single-server queueing systems in some specific cases. Sakasegawa observed the error curve at various parameters and essentially did a least-squares sum of errors regression to arrive at an approximation for the queue length, which in the simplest types of queueing systems reduces to:

$$ L_q \approx \frac{ \rho^{\sqrt{2(m+1)}} }{ 1-\rho} $$

In “stretch factor” form, this becomes

$$ R(m, \rho) \approx 1+\frac{\rho^{\sqrt{2(m+1)}}}{\rho m(1-\rho)} $$

This is such an accurate approximation that it’s more than good enough for real-life applications, and I use it all the time. (It’s hard to put Erlang’s formula into a spreadsheet, because it has iterative computations.)

Given the usefulness and simplicity of approximations, perhaps an approximation to the stretch factor can be derived from the heuristic form \(1/(1-\rho^m)\). This idea appeals to me because it might lead to insights about what’s disappeared or simplified out of the exact Erlang formula in the base case.

One possible way to do this is to approximate the error function of the heuristic. I already mentioned that it might be possible to do this with the Gamma distribution’s PDF. Finding an approximation to that, and then adding or multiplying by it, might result in a usable approximation.

Another idea is a sigmoid such as the classic logistic function. In fact, if I wanted to approximate the error in the range (0,1) this isn’t too bad an approximation for \(m=16\), for example:

$$ \frac{.5}{1+e^{-10(x-1)}} $$

Finally, instead of adding a term or multiplying the heuristic by a term, perhaps it’s the \(\rho^m\) portion in the denominator that needs to be tweaked. A little analysis led me to the following conclusions.

The error at \(m>3\) could be explained by the exponent \(m\) being too large. If so, then the correct value for the exponent could be a function of \(\rho\), and an adjustment to it would need to be of the form

$$ A_{exp} = \frac{log\left( \frac{E(\rho)-1}{E(\rho)} \right)}{log(\rho)} $$

Where \(E(\rho)\) is the Erlang formula for the stretch factor. I arrived at this by solving the error function for \(\rho\). For convenience, this can be divided by \(m\) to normalize it relative to the number of servers. I’ve made a Desmos calculator illustrating the shape of this adjustment term for 1, 4, 8, and 16 servers:

One of the challenges with this is that due to the limitations of floating-point math in computers, the heuristic function appears to have no error at low utilization. I think it does, but it’s just a small value. That’s why the shape of that curve has a discontinuity at low utilization.

An interesting observation: If you zoom out and remove the limits on the range of values plotted, you get something that looks like a part of the Gamma function. Is this a coincidence? Could the Gamma function be used as an approximation to this error? Or is the missing term a Gamma function, which would result in an exact solution? TODO.

Another way to nudge the heuristic to approximate the Erlang residence time stretch factor would be to examine whether the base, \(\rho\), is too large or too small. Following a similar train of thought as before and solving the error function for the number of servers, I found that the error would need to be of the form

$$ A_{base} = \left( \frac{E(\rho)-1}{E(\rho)}\right)^{1/m} $$

Normalizing this relative to \(\rho\) by dividing, I got a function that has similar discontinuities as before, and is of the following shape. It looks like it might be possible to approximate with something like a quadratic from 0 to 1, but if you zoom out further, it looks more like… wait for it… part of the Gamma function. You can see this on Desmos.

I experimented with this in a different way, by trying to approximate \(A_{base}\) directly. I just guessed at its shape and came up with the following, which isn’t too far off for \(2<m<5\):

$$ A_{base} \approx \rho - \frac{2}{15} \sqrt{m} (\rho-1)\rho $$

You can see this shape, compared with the actual \(A_{base}\), at this Desmos. And this one shows what this looks like when included as a term in the heuristic stretch factor.

Red is Erlang, blue is my heuristic, and black dashed is Gunther’s. Don’t be fooled; mine may look better, but if you examine high utilizations you’ll see it’s much worse. Small errors in the approximation to the error function make big differences in the result. (This is true if the error is defined as a function of number of servers, too.)

Conclusions

There’s a lot of outright superstition in this blog post, but hopefully some of the intuition I’m trying to develop comes through as well. Guessing at approximations (and seeing Gamma-things everywhere, like shapes in the clouds) is probably more time-wasting than productive, but I find that by playing with things I learn a lot. There’s something still unfinished in my mind, something unsatisfactory about coming towards an exact solution from two directions, finding two different forms, and finding that one of them runs aground at a certain point, even though it’s just a simplification of the other under some circumstances. You might replace “even though” with “because” in the previous sentence, and be satisfied, but as for me I feel there’s still something to be learned here. And potentially a useful result might come out of it, even if it’s only a fast approximation like Sakasegawa’s.

Besides, I just enjoy exploring math and its shapes; this is a great form of relaxation or stress relief for me when I want to concentrate on something so as to put other things out of my mind. Hopefully there’s a kindred soul out there who finds this interesting too. If so, hello, and enjoy!

Categories: MySQL

The Square Root Staffing Law

Xaprb, home of innotop - Sat, 2016-10-29 13:57

The square root staffing law is a rule of thumb derived from queueing theory, useful for getting an estimate of the capacity you might need to serve an increased amount of traffic.

The square root staffing law is designed to help with capacity planning in what’s called the QED regime, which tries to balance efficiency with quality of service. Capacity planning is a set of tradeoffs: for best quality of service, you must provision lots of spare capacity (headroom), but that’s wasteful. For best efficiency, you minimize idle capacity, but then quality of service becomes terrible.

The QED regime poses the following scenario: assuming that your current quality of service is acceptable, and you want to maintain it, how much must you increase capacity to serve increased demand?

The picture above illustrates this. Suppose you currently have 10 servers (the green circle) and your peak utilization is 25% (the blue circle), so you have 75% idle capacity (the green area). You predict that you’ll need to serve 3x the current peak load for, say, Black Friday. How many more servers do you need?

The answer is less than a linear increase to 30 servers (the red dashed line). It’s 21, in fact (the orange circle). This answer comes from the rule of thumb that to hold quality of service constant, you must increase the headroom by the square root of the load increase.

If you’d like to experiment interactively with this capacity planning tool, I built a Desmos calculator for you. You can drag the circles around to see how parameters such as utilization affect the amount of idle capacity you’ll need to provision. You can download an Excel worksheet from VividCortex as well.

The square root rule is an approximation, but it’s a decent one, and its bounds are well understood.

To learn more about this, please read my book The Essential Guide To Queueing Theory, which contains further reference material you might also find helpful.

You might also be interested in learning about the Universal Scalability Law, which is a great tool for capacity planning as well.

Categories: MySQL

The Square Root Staffing Law

Xaprb, home of innotop - Sat, 2016-10-29 13:57

The square root staffing law is a rule of thumb derived from queueing theory, useful for getting an estimate of the capacity you might need to serve an increased amount of traffic.

The square root staffing law is designed to help with capacity planning in what’s called the QED regime, which tries to balance efficiency with quality of service. Capacity planning is a set of tradeoffs: for best quality of service, you must provision lots of spare capacity (headroom), but that’s wasteful. For best efficiency, you minimize idle capacity, but then quality of service becomes terrible.

The QED regime poses the following scenario: assuming that your current quality of service is acceptable, and you want to maintain it, how much must you increase capacity to serve increased demand?

The picture above illustrates this. Suppose you currently have 10 servers (the green circle) and your peak utilization is 25% (the blue circle), so you have 75% idle capacity (the green area). You predict that you’ll need to serve 3x the current peak load for, say, Black Friday. How many more servers do you need?

The answer is less than a linear increase to 30 servers (the red dashed line). It’s 21, in fact (the orange circle). This answer comes from the rule of thumb that to hold quality of service constant, you must increase the headroom by the square root of the load increase.

If you’d like to experiment interactively with this capacity planning tool, I built a Desmos calculator for you. You can drag the circles around to see how parameters such as utilization affect the amount of idle capacity you’ll need to provision. You can download an Excel worksheet from VividCortex as well.

The square root rule is an approximation, but it’s a decent one, and its bounds are well understood.

To learn more about this, please read my book The Essential Guide To Queueing Theory, which contains further reference material you might also find helpful.

You might also be interested in learning about the Universal Scalability Law, which is a great tool for capacity planning as well.

Categories: MySQL

Blog Series: MySQL Configuration Management

MySQL Performance Blog - Fri, 2016-10-28 22:45

MySQL configuration management remains a hot topic, as I’ve noticed on numerous occasions during my conversations with customers.

I thought it might be a good idea to start a blog series that goes deeper in detail into some of the different options, and what modules potentially might be used for managing your MySQL database infrastructure.

Configuration management has been around since way before the beginning of my professional career. I, myself, originally began working on integrating an infrastructure with my colleagues using Puppet.

Why is configuration management important?
  • Reproducibility. It’s giving us the ability to provision any environment in an automated way, and feel sure that the new environment will contain the same configuration.
  • Fast restoration. Thanks to reproducibility, you can quickly provision machines in case of disasters. This makes sure you can focus on restoring your actual data instead of worrying about the deployment and configuration of your machines.
  • Integral part of continuous deployment. Continuous deployment is a terminology everyone loves: being able to deploy changes rapidly and automatically after automated regression testing requires a configuration management solution.
  • Compliance and security. Solutions like Puppet and Chef maintain and enforce configuration parameters on your infrastructure. This can sound bothersome at first, but it’s essential for maintaining a well-configured environment.
  • Documented environment. Although reading someone’s puppet code can potentially harm you beyond insanity, it provides you with the real truth about your infrastructure.
  • Efficiency and manageability. Configuration management can automate repetitive tasks (for example, user grants, database creation, configuration variables), as well as security updates, service restarts, etc. These can potentially bring you less work and faster rollouts.
Which players are active in this field?

The most popular open source solutions are Puppet, Chef, Ansible, and CFengine (among others). In this series, we will go deeper in the first three of them.

Let’s first start by giving you a quick, high-level introduction.

Puppet

Puppet is a language used to describe the desired state of an environment. The Puppet client reads the catalog of the expected state from the server and enforces these changes on the client. The system works based on a client/server principle.

Puppet has as default four essential components:

  • Puppet Server: A Java virtual machine offering Puppet’s core services.
  • Puppet Agent: A client library that requests configuration catalog info from the puppet-server.
  • Hiera: A key-value lookup database, which can store and modify values for specific hosts.
  • Facter: An application that keeps an inventory of the local node variables.

How can you integrate puppet in your MySQL infrastructure?

This will allow you and your team to create users, databases, install and configure MySQL

Probably my old “code from hell” module is still somewhere out there.

Chef

Chef also consists of a declarative language (like Puppet) based on Ruby which will allow you to write cookbooks for potential integrable technologies. Chef is also based on a server/client solution. The client being chef nodes, the server managing the cookbooks, catalogs and recipes.

In short, Chef consists of:

  • Chef server: Manages the multiple cookbooks and the catalog
  • Chef clients (nodes): The actual system requesting the catalog information from the chef server.
  • Workstations: This is a system that is configured to run Chef command-line tools that synchronize with a Chef-repository or the Chef server. You could also describe this as a Chef development and tooling environment.

How can you integrate Chef in your MySQL infrastructure:

Ansible

Ansible originated with something different in mind. System engineers typically chose to use their own management scripts. This can be troublesome and hard to maintain. Why wouldn’t you use something easy and automated and standardized? Ansible fills in these gaps, and simplifies management of Ansible targets.

Ansible works by connecting to your nodes (by SSH default) and pushes out Ansible modules to them. These modules represent the desired state of the node, and will be used to execute commands to attain the desired state.

This procedure is different to Puppet and Chef, which are essentially preferably client/server solutions.

Some pre-made modules for MySQL are:

Conclusion and Next Steps

Choose your poison (or magical medicine, you pick the wording), every solution has its perks.

Keep in mind that in some situations running a complicated Puppet or Chef infrastructure could be overkill. At this moment, a solution like Ansible might be a quick and easily integrable answer for you.

The next blog post will go over the Puppet Forge MySQL module, so stay tuned!

Categories: MySQL

Life on Mobile: The Last Mile on iPad

Xaprb, home of innotop - Fri, 2016-10-28 22:27

I wrote previously about how I run much of my life on iPhone/iPad, and how I switched from Android to iPhone. Why do I still have a laptop, if I can do so much on mobile devices? What holds me back?

A year ago, I thought I could probably minimize or even eliminate the use of my laptop, so I decided to give it a try. After a few weeks, however, I found that the “last mile” is a long trek on mobile, and it’s not practical for me to get rid of my laptop completely.

There are two major reasons. One, a lot of things I need to do don’t work on mobile. Secondly, when you really start using a mobile device for everything, it’s shocking how many bugs and unusable features there are.

Here are a few things I can’t really do well on mobile, or at least on an iPad or iPhone:

  • I can’t write papers in LaTeX and generate PDFs, especially with custom packages and fonts and the like. (Yes, there are online LaTeX editors, but they also have limitations.)
  • I haven’t yet been able to get a VPN to actually work, and I need one.
  • PGP/GPG/encryption is a nightmare. I have never been able to get my GPG keys set up so I can decrypt a secret message someone sends me. When I tried, I was terrified that I was unintentionally going to leak my private keys.
  • Ditto for SSH keys and anything I want to do on the terminal, which is typically a lot when I’m using my laptop.
  • I can’t really work on Microsoft Office documents. The Office apps integrate well enough with the documents themselves in most cases, but working with files stored on Google Drive or similar is just baffling. It’s clear no one at Microsoft cares about that use case. And complicated features often are unavailable on the mobile versions of the Office apps, but are pretty commonly used in my experience.
  • I can’t integrate an iPad well into a Google Calendar. There’s no iPad app for Google Calendar (though there’s an iPhone app), and the Apple Calendar app is substandard. For example, if there’s an attached video call link, you can’t see it in the Apple Calendar app.
  • I can’t really use Hangouts. The Hangouts app is beyond absurd. Trying to find and open a hangout from an iPhone or iPad is an exercise in frustration.
  • I can’t use keyboard shortcuts for productivity (in general, and with an external keyboard). Many apps do offer keyboard shortcuts, but not, for example, Google Inbox. And even when doing things like editing an email in Inbox and using the arrow keys on the official Apple iPad Pro keyboard, Inbox does weird things. Selecting text with shift-right-arrow, for example, selects text leftward instead of rightward!
  • I often can’t use mobile and/or desktop versions of important websites. A lot of websites redirect you to a mobile version with limited functionality. You can request a desktop version instead, but in many cases it doesn’t work, and in others, the desktop version doesn’t work well in mobile. (There’s no mouse, and hovering a mouse doesn’t make sense in mobile, but a lot of websites and web apps use hover-driven UI elements, so they don’t work in mobile). As a concrete example of this, the Google Image Search website doesn’t offer all the same features in mobile, such as searching for images with CC licensing.
  • Many apps lack last-mile fit-and-finish. This is true of both the official Apple apps as well as many apps from Google and others. And the integrations between apps often is broken in fairly obvious ways. For example, copy-paste doesn’t really work well between the Apple Notes app and most of the Google apps, apparently because of formatting codes when copying from Notes. I can paste into a textbox in plaintext and then copy that and paste it into an email message in Inbox, or into a document in Docs, but I can’t paste directly. As a result, I often end up awkwardly pasting things into Github’s Gist (just because it has a large textarea) in the browser, copying, and then pasting into Inbox.
  • Lots of fairly basic UI interactions break pretty often, leading to a frustrating experience. Oddly, I noticed this less often before I put my laptop into cold storage, but when I forced myself to rely on only the iPad and iPhone, suddenly I realized how much polish is lacking. For example, with the official Apple iPad Pro keyboard attached, the iPad Pro doesn’t register the first few keystrokes to enter the passcode and unlock the iPad, so it’s not like the Macbook where you just press a key and it wakes up and is ready for your password.
  • You can’t choose the app you want for specific purposes like email, calendar, and maps. The Google apps work well together and let you specify whether to use a Google app, but it’s not systemwide. There are always workarounds. But those are slightly more laborious, usually involving something like using a sharing menu.
  • The idea of “files” doesn’t really exist in the same way, and doing something like “downloading” a “file” from an email attachment, editing it, and sending the newly edited version is pretty awful. Mobile is designed around the idea that local storage isn’t the central concept, cloud sharing is. But cloud sharing doesn’t really work for a lot of things, such as, say, collaboratively editing an Excel spreadsheet. Maybe someday we’ll get there, but as of now, it’s laborious in a lot of cases, and sometimes impossible. Fundamentally, only iCloud (Apple’s own services) are natively supported, and the integration with all others such as Google Drive, Dropbox, or Microsoft’s whatever they call it, is a real pain. (iCloud sharing, by the way, is a total disaster.)
  • Lots of apps, both built-in and third-party, do the common thing very well, but any exceptional case is either not possible or is often hidden. An example of this is hidden parts of the UI. In many apps, you swipe as if you want to scroll, which reveals a hidden UI element such as a search field. But if you didn’t know that was possible already, you’d be somewhat unlikely to find it.
  • Many UI interactions are highly designed, in ways that seem impressive at first but aren’t productive when you do them constantly. For example, animating apps when you’re switching between them. Cool trick, unless you do it a lot. Then you get impatient with the delays and the visual distraction. Or, at least, I do.
  • Many apps don’t play well with split-screen mode, so you can’t use two apps side-by-side in iPad. On the iPad Pro, this is a huge waste, because it has a big screen, and now you’re reduced to switching between apps a lot.
  • Many apps are pretty substandard on iPad Pro, exhibiting various layout issues (font spacing all weird, clipped fonts…) or badly wasted space (iPhone-sized content area in the middle of a giant white screen).
  • Serious typing or other productivity work is hampered. On my iPad Pro, I have been unable to disable auto-correct, despite disabling every related setting, when an external keyboard is attached. It constantly changes my words to something else. And I understand, when you’re typing a text message to your friend with your fingers on the screen, autocorrect can help. But when I want to write someone’s name that’s spelled a bit differently, or when I don’t want to capitalize the first letter after “i.e.” or something like that, it’s really frustrating.

A lot of this is Apple-specific, but from what I’ve seen Apple is generally ahead of the pack. Mobile-only users are clearly in the minority. If you’re mobile-only, a ton of little but important stuff is just broken for you, and you won’t be able to complete the tasks you want. These could be really important—as, for example, using a government website to request an absentee ballot or file your tax return or something. You may be forced to use a “traditional” computer to do these types of things.

The other thing I’ve noticed is that Apple seems to be introducing more bugs in the last couple of versions of iOS. Everyone loves to criticize Apple for the smallest things, but I believe they’re legitimately slipping product quality recently. Siri fails to do simple things it used to be good at, and just asks inane questions in reply. AirPlay devices don’t get detected quickly anymore; there are long delays and it takes several tries. Apps don’t upgrade in the background anymore; when I open the iPad up, they start upgrading, and sometimes it’s the app I wanted to use, and I have to wait for it. The new iOS 10 lock screen is horribly buggy, and basically useless when it does work. It used to be one of my productivity secret weapons pre-version-10, and now I’ve essentially had to disable it and do things the unproductive way again. Those kinds of problems give me pause and make me wonder whether I’m being productive on mobile anymore, or should use mobile less and desktop more.

Am I complaining? No, not really, just stating what I see as the reality: mobile devices aren’t ready yet for the “real world” of all the things I must do on a daily and weekly basis. This, of course, is a far cry from how Apple positions them; they touted the iPad Pro as a laptop replacement, perfect for Microsoft PC laptop users to migrate to. I think that’d be a pretty miserable experience.

I’ll finish this blog post with a set of “pro tip” iOS time savers I’ve found to be useful for me. Hopefully they help you too!

  • To enable something like iOS 9 lock-screen behavior with regards to unlocking (to disable the confusing extra step of unlocked-but-still-looks-locked), go to Accessibility / Home Button / Rest Finger To Open.
  • Reduce all the eye candy when switching apps with Accessibility / Reduce Motion.
  • You can try to disable autocorrect and predictive type-ahead, but it doesn’t always work. Look in Keyboard settings. You can also change this per-app by long-pressing on the “emoji” key to the left of the space key (in on-screen keyboards).
  • Try to swipe/scroll everything in every app and see what you find.
  • Use Tweetbot, not the official Twitter app. Don’t install LinkedIn or Facebook.
  • Inbox and Outlook are the best GMail apps.
  • Don’t allow the App Store to use cellular data, so your apps only update when on wifi, and you’ll save massive amounts of data usage. This is under Settings / Cellular. In my case, the App Store was using gigabytes of data, probably more than 90% of my total data usage.

Pic Credit

Categories: MySQL

Life on Mobile: The Last Mile on iPad

Xaprb, home of innotop - Fri, 2016-10-28 22:27

I wrote previously about how I run much of my life on iPhone/iPad, and how I switched from Android to iPhone. Why do I still have a laptop, if I can do so much on mobile devices? What holds me back?

A year ago, I thought I could probably minimize or even eliminate the use of my laptop, so I decided to give it a try. After a few weeks, however, I found that the “last mile” is a long trek on mobile, and it’s not practical for me to get rid of my laptop completely.

There are two major reasons. One, a lot of things I need to do don’t work on mobile. Secondly, when you really start using a mobile device for everything, it’s shocking how many bugs and unusable features there are.

Here are a few things I can’t really do well on mobile, or at least on an iPad or iPhone:

  • I can’t write papers in LaTeX and generate PDFs, especially with custom packages and fonts and the like. (Yes, there are online LaTeX editors, but they also have limitations.)
  • I haven’t yet been able to get a VPN to actually work, and I need one.
  • PGP/GPG/encryption is a nightmare. I have never been able to get my GPG keys set up so I can decrypt a secret message someone sends me. When I tried, I was terrified that I was unintentionally going to leak my private keys.
  • Ditto for SSH keys and anything I want to do on the terminal, which is typically a lot when I’m using my laptop.
  • I can’t really work on Microsoft Office documents. The Office apps integrate well enough with the documents themselves in most cases, but working with files stored on Google Drive or similar is just baffling. It’s clear no one at Microsoft cares about that use case. And complicated features often are unavailable on the mobile versions of the Office apps, but are pretty commonly used in my experience.
  • I can’t integrate an iPad well into a Google Calendar. There’s no iPad app for Google Calendar (though there’s an iPhone app), and the Apple Calendar app is substandard. For example, if there’s an attached video call link, you can’t see it in the Apple Calendar app.
  • I can’t really use Hangouts. The Hangouts app is beyond absurd. Trying to find and open a hangout from an iPhone or iPad is an exercise in frustration.
  • I can’t use keyboard shortcuts for productivity (in general, and with an external keyboard). Many apps do offer keyboard shortcuts, but not, for example, Google Inbox. And even when doing things like editing an email in Inbox and using the arrow keys on the official Apple iPad Pro keyboard, Inbox does weird things. Selecting text with shift-right-arrow, for example, selects text leftward instead of rightward!
  • I often can’t use mobile and/or desktop versions of important websites. A lot of websites redirect you to a mobile version with limited functionality. You can request a desktop version instead, but in many cases it doesn’t work, and in others, the desktop version doesn’t work well in mobile. (There’s no mouse, and hovering a mouse doesn’t make sense in mobile, but a lot of websites and web apps use hover-driven UI elements, so they don’t work in mobile). As a concrete example of this, the Google Image Search website doesn’t offer all the same features in mobile, such as searching for images with CC licensing.
  • Many apps lack last-mile fit-and-finish. This is true of both the official Apple apps as well as many apps from Google and others. And the integrations between apps often is broken in fairly obvious ways. For example, copy-paste doesn’t really work well between the Apple Notes app and most of the Google apps, apparently because of formatting codes when copying from Notes. I can paste into a textbox in plaintext and then copy that and paste it into an email message in Inbox, or into a document in Docs, but I can’t paste directly. As a result, I often end up awkwardly pasting things into Github’s Gist (just because it has a large textarea) in the browser, copying, and then pasting into Inbox.
  • Lots of fairly basic UI interactions break pretty often, leading to a frustrating experience. Oddly, I noticed this less often before I put my laptop into cold storage, but when I forced myself to rely on only the iPad and iPhone, suddenly I realized how much polish is lacking. For example, with the official Apple iPad Pro keyboard attached, the iPad Pro doesn’t register the first few keystrokes to enter the passcode and unlock the iPad, so it’s not like the Macbook where you just press a key and it wakes up and is ready for your password.
  • You can’t choose the app you want for specific purposes like email, calendar, and maps. The Google apps work well together and let you specify whether to use a Google app, but it’s not systemwide. There are always workarounds. But those are slightly more laborious, usually involving something like using a sharing menu.
  • The idea of “files” doesn’t really exist in the same way, and doing something like “downloading” a “file” from an email attachment, editing it, and sending the newly edited version is pretty awful. Mobile is designed around the idea that local storage isn’t the central concept, cloud sharing is. But cloud sharing doesn’t really work for a lot of things, such as, say, collaboratively editing an Excel spreadsheet. Maybe someday we’ll get there, but as of now, it’s laborious in a lot of cases, and sometimes impossible. Fundamentally, only iCloud (Apple’s own services) are natively supported, and the integration with all others such as Google Drive, Dropbox, or Microsoft’s whatever they call it, is a real pain. (iCloud sharing, by the way, is a total disaster.)
  • Lots of apps, both built-in and third-party, do the common thing very well, but any exceptional case is either not possible or is often hidden. An example of this is hidden parts of the UI. In many apps, you swipe as if you want to scroll, which reveals a hidden UI element such as a search field. But if you didn’t know that was possible already, you’d be somewhat unlikely to find it.
  • Many UI interactions are highly designed, in ways that seem impressive at first but aren’t productive when you do them constantly. For example, animating apps when you’re switching between them. Cool trick, unless you do it a lot. Then you get impatient with the delays and the visual distraction. Or, at least, I do.
  • Many apps don’t play well with split-screen mode, so you can’t use two apps side-by-side in iPad. On the iPad Pro, this is a huge waste, because it has a big screen, and now you’re reduced to switching between apps a lot.
  • Many apps are pretty substandard on iPad Pro, exhibiting various layout issues (font spacing all weird, clipped fonts…) or badly wasted space (iPhone-sized content area in the middle of a giant white screen).
  • Serious typing or other productivity work is hampered. On my iPad Pro, I have been unable to disable auto-correct, despite disabling every related setting, when an external keyboard is attached. It constantly changes my words to something else. And I understand, when you’re typing a text message to your friend with your fingers on the screen, autocorrect can help. But when I want to write someone’s name that’s spelled a bit differently, or when I don’t want to capitalize the first letter after “i.e.” or something like that, it’s really frustrating.

A lot of this is Apple-specific, but from what I’ve seen Apple is generally ahead of the pack. Mobile-only users are clearly in the minority. If you’re mobile-only, a ton of little but important stuff is just broken for you, and you won’t be able to complete the tasks you want. These could be really important—as, for example, using a government website to request an absentee ballot or file your tax return or something. You may be forced to use a “traditional” computer to do these types of things.

The other thing I’ve noticed is that Apple seems to be introducing more bugs in the last couple of versions of iOS. Everyone loves to criticize Apple for the smallest things, but I believe they’re legitimately slipping product quality recently. Siri fails to do simple things it used to be good at, and just asks inane questions in reply. AirPlay devices don’t get detected quickly anymore; there are long delays and it takes several tries. Apps don’t upgrade in the background anymore; when I open the iPad up, they start upgrading, and sometimes it’s the app I wanted to use, and I have to wait for it. The new iOS 10 lock screen is horribly buggy, and basically useless when it does work. It used to be one of my productivity secret weapons pre-version-10, and now I’ve essentially had to disable it and do things the unproductive way again. Those kinds of problems give me pause and make me wonder whether I’m being productive on mobile anymore, or should use mobile less and desktop more.

Am I complaining? No, not really, just stating what I see as the reality: mobile devices aren’t ready yet for the “real world” of all the things I must do on a daily and weekly basis. This, of course, is a far cry from how Apple positions them; they touted the iPad Pro as a laptop replacement, perfect for Microsoft PC laptop users to migrate to. I think that’d be a pretty miserable experience.

I’ll finish this blog post with a set of “pro tip” iOS time savers I’ve found to be useful for me. Hopefully they help you too!

  • To enable something like iOS 9 lock-screen behavior with regards to unlocking (to disable the confusing extra step of unlocked-but-still-looks-locked), go to Accessibility / Home Button / Rest Finger To Open.
  • Reduce all the eye candy when switching apps with Accessibility / Reduce Motion.
  • You can try to disable autocorrect and predictive type-ahead, but it doesn’t always work. Look in Keyboard settings. You can also change this per-app by long-pressing on the “emoji” key to the left of the space key (in on-screen keyboards).
  • Try to swipe/scroll everything in every app and see what you find.
  • Use Tweetbot, not the official Twitter app. Don’t install LinkedIn or Facebook.
  • Inbox and Outlook are the best GMail apps.
  • Don’t allow the App Store to use cellular data, so your apps only update when on wifi, and you’ll save massive amounts of data usage. This is under Settings / Cellular. In my case, the App Store was using gigabytes of data, probably more than 90% of my total data usage.

Pic Credit

Categories: MySQL

New TokuDB and PerconaFT database file management feature in Percona Server 5.6.33-79.0 and Percona Server 5.7.15-9

MySQL Performance Blog - Fri, 2016-10-28 20:18

This blog post discusses a new TokuDB and PerconaFT database file management feature in two Percona Server releases.

By now you have hopefully read through Peter’s post and my two prior posts on the TokuDB/PerconaFT file set. If you have not, it is probably a good idea to run through them now before we get into the details of this new feature.

We introduced a new server option beginning in Percona Server 5.6.33-79.0 and Percona Server 5.7.15-9, called tokudb_dir_per_db, that addresses two shortcomings within the current TokuDB implementation:

  • The renaming of data files on table/index rename
  • The ability to group data files together within a directory that represents a single database.

The new option is disabled by default in 5.6.33-79.0, but will be enabled by default beginning in 5.7.15-9

New table renaming functionality

When you rename a TokuDB table via SQL, the data files on disk keep their original names. Only the mapping in the PerconaFT directory file is changed to map the new dictionary name to the original internal file names. This makes it difficult to quickly match database/table/index names to their actual files on disk, requiring you to use the INFORMATION_SCHEMA.TOKUDB_FILE_MAP to cross reference.

When tokudb_dir_per_db is enabled, this is no longer the case. When you rename a table, the mapping in the PerconaFT directory file will be updated, and the files will be renamed on disk to reflect the new table name. This was a lot more difficult to implement than we originally thought due to the non-transactional nature of the underlying file systems. We had to “invent” a transactional file system rename functionality within PerconaFT to ensure that a crash during a file system mv src dst was recoverable.

New directory layout functionality

Many users have had issues with managing the huge volume of individual files used by TokuDB and PerconaFT. We are beginning to take some steps to help improve the manageability of these files, and potentially even reduce the number of files present.

When you enable tokudb_dir_per_db, all new tables and indices are placed within their corresponding database directory within the tokudb_data_dir or system datadir. Existing table files will not be automatically relocated to their corresponding database directory.

You can easily move a table’s data files into the new scheme and proper database directory with a few steps:

mysql> SET GLOBAL tokudb_dir_per_db=true; mysql> RENAME TABLE <table> TO <tmp_table>; mysql> RENAME TABLE <tmp_table> TO <table>;

Two renames are needed because MySQL will not allow you to rename a table to itself. The first rename renames the table to the temporary name and mv‘s the tables files into the owning database directory. The second rename sets the table name back to the original name. Tables can, of course, also be renamed/moved across databases and will be placed correctly into the corresponding database directory.

You must be careful with renaming tables! If you have used any tricks to create symlinks of the database directories on different storage volumes, the mv is not a simple directory mv on the same volume, but a physical copy across volumes. This can take quite some time and prevents access to the table being moved during the copy.

NOTE: If you have tokudb_data_dir set to something other than the system datadir, TokuDB creates a directory matching the name of the database. Upon dropping of the database, this directory remains behind.

Example:

While running Percona Server 5.7.15-9 with tokudb_dir_per_db=false to illustrate the old behavior, create a table t1, show the file map, and list the data directory:

mysql> SET GLOBAL tokudb_dir_per_db=false; Query OK, 0 rows affected (0.00 sec) mysql> DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Database changed mysql> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c VARCHAR(200), KEY kab(a, b)) ENGINE=TOKUDB; Query OK, 0 rows affected (0.07 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.TOKUDB_FILE_MAP; *************************** 1. row *************************** dictionary_name: ./test/t1-key-kab internal_file_name: ./_test_t1_key_kab_de_3_1d.tokudb table_schema: test table_name: t1 table_dictionary_name: key-kab *************************** 2. row *************************** dictionary_name: ./test/t1-main internal_file_name: ./_test_t1_main_de_2_1d.tokudb table_schema: test table_name: t1 table_dictionary_name: main *************************** 3. row *************************** dictionary_name: ./test/t1-status internal_file_name: ./_test_t1_status_de_1_1d.tokudb table_schema: test table_name: t1 table_dictionary_name: status 3 rows in set (0.00 sec)

$ ls -1 data/*.tokudb data/_test_t1_key_kab_de_3_1d.tokudb data/_test_t1_main_de_2_1d.tokudb data/_test_t1_status_de_1_1d.tokudb

We see the data files for our table t1 as the three files _test_t1_*

Rename t1 to all_the_kings_horses, show the file map again, and another listing of the data directory:

mysql> RENAME TABLE t1 TO all_the_kings_horses; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.TOKUDB_FILE_MAP; *************************** 1. row *************************** dictionary_name: ./test/all_the_kings_horses-key-kab internal_file_name: ./_test_t1_key_kab_de_3_1d.tokudb table_schema: test table_name: all_the_kings_horses table_dictionary_name: key-kab *************************** 2. row *************************** dictionary_name: ./test/all_the_kings_horses-main internal_file_name: ./_test_t1_main_de_2_1d.tokudb table_schema: test table_name: all_the_kings_horses table_dictionary_name: main *************************** 3. row *************************** dictionary_name: ./test/all_the_kings_horses-status internal_file_name: ./_test_t1_status_de_1_1d.tokudb table_schema: test table_name: all_the_kings_horses table_dictionary_name: status 3 rows in set (0.00 sec)

$ ls -1 data/*.tokudb data/_test_t1_key_kab_de_3_1d.tokudb data/_test_t1_main_de_2_1d.tokudb data/_test_t1_status_de_1_1d.tokudb

The file names remained the same as the original table, but the file map has changed to reflect the new table/dictionary names.

Let us inject a little confusion by adding another index to the table:

mysql> alter table all_the_kings_horses add index kac(a,c); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM INFORMATION_SCHEMA.TOKUDB_FILE_MAP; *************************** 1. row *************************** dictionary_name: ./test/all_the_kings_horses-key-kab internal_file_name: ./_test_t1_key_kab_de_3_1d.tokudb table_schema: test table_name: all_the_kings_horses table_dictionary_name: key-kab *************************** 2. row *************************** dictionary_name: ./test/all_the_kings_horses-key-kac internal_file_name: ./_test_all_the_kings_horses_key_kac_e3_3_1d_B_0.tokudb table_schema: test table_name: all_the_kings_horses table_dictionary_name: key-kac *************************** 3. row *************************** dictionary_name: ./test/all_the_kings_horses-main internal_file_name: ./_test_t1_main_de_2_1d.tokudb table_schema: test table_name: all_the_kings_horses table_dictionary_name: main *************************** 4. row *************************** dictionary_name: ./test/all_the_kings_horses-status internal_file_name: ./_test_t1_status_de_1_1d.tokudb table_schema: test table_name: all_the_kings_horses table_dictionary_name: status 4 rows in set (0.00 sec)

$ ls -1 data/*.tokudb data/_test_all_the_kings_horses_key_kac_e3_3_1d_B_0.tokudb data/_test_t1_key_kab_de_3_1d.tokudb data/_test_t1_main_de_2_1d.tokudb data/_test_t1_status_de_1_1d.tokudb

The file for the new index kac was created with the current table name, not the original.

Now we move on to the new behavior. First make sure that tokudb_dir_per_db=true, then rename the table again, show the file map, and do another directory listing:

mysql> SET GLOBAL tokudb_dir_per_db=true; Query OK, 0 rows affected (0.00 sec) mysql> RENAME TABLE all_the_kings_horses TO all_the_kings_men; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.TOKUDB_FILE_MAP; *************************** 1. row *************************** dictionary_name: ./test/all_the_kings_men-key-kab internal_file_name: ./test/all_the_kings_men_key_kab_ea_2_1d.tokudb table_schema: test table_name: all_the_kings_men table_dictionary_name: key-kab *************************** 2. row *************************** dictionary_name: ./test/all_the_kings_men-key-kac internal_file_name: ./test/all_the_kings_men_key_kac_ea_3_1d.tokudb table_schema: test table_name: all_the_kings_men table_dictionary_name: key-kac *************************** 3. row *************************** dictionary_name: ./test/all_the_kings_men-main internal_file_name: ./test/all_the_kings_men_main_ea_4_1d.tokudb table_schema: test table_name: all_the_kings_men table_dictionary_name: main *************************** 4. row *************************** dictionary_name: ./test/all_the_kings_men-status internal_file_name: ./test/all_the_kings_men_status_ea_5_1d.tokudb table_schema: test table_name: all_the_kings_men table_dictionary_name: status 4 rows in set (0.00 sec)

$ ls -1 data/test/*.tokudb data/test/all_the_kings_men_key_kab_ea_2_1d.tokudb data/test/all_the_kings_men_key_kac_ea_3_1d.tokudb data/test/all_the_kings_men_main_ea_4_1d.tokudb data/test/all_the_kings_men_status_ea_5_1d.tokudb

The database files have now been renamed to properly match the name of the database, table and keys, and they have been moved into the data/test directory.

Now let us watch all that action with an alternate tokudb_data_dir. Rather than showing how to move files around as mentioned in the previous blog posts, we will just reset our TokuDB installation and start the server with a different tokudb_data_dir that is a sibling to the server datadir, called tokudb_data.

mysql> SET GLOBAL tokudb_dir_per_db=true; Query OK, 0 rows affected (0.00 sec) mysql> DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 1 row affected (0.00 sec) Database changed mysql> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c VARCHAR(200), KEY kab(a, b)) ENGINE=TOKUDB; Query OK, 0 rows affected (0.15 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.TOKUDB_FILE_MAP; *************************** 1. row *************************** dictionary_name: ./test/t1-key-kab internal_file_name: /ssd/toku/DB-295/percona-server-install-5.7/tokudb_data/test/t1_key_kab_d9_3_1d.tokudb table_schema: test table_name: t1 table_dictionary_name: key-kab *************************** 2. row *************************** dictionary_name: ./test/t1-main internal_file_name: /ssd/toku/DB-295/percona-server-install-5.7/tokudb_data/test/t1_main_d9_2_1d.tokudb table_schema: test table_name: t1 table_dictionary_name: main *************************** 3. row *************************** dictionary_name: ./test/t1-status internal_file_name: /ssd/toku/DB-295/percona-server-install-5.7/tokudb_data/test/t1_status_d9_1_1d.tokudb table_schema: test table_name: t1 table_dictionary_name: status 3 rows in set (0.00 sec)

$ ls -1 tokudb_data/ test __tokudb_lock_dont_delete_me_data __tokudb_lock_dont_delete_me_temp $ ls -1 tokudb_data/test/ t1_key_kab_d9_3_1d.tokudb t1_main_d9_2_1d.tokudb t1_status_d9_1_1d.tokudb

This shows us TokuDB now putting everything over in the directory we specified in our tokudb_data_dir, and is following the tokudb_dir_per_db paradigm by creating the directory called test before creating the table.

What happens when we drop that database?

mysql> drop database test; Query OK, 1 row affected (0.02 sec)

$ ls -1 tokudb_data/ test __tokudb_lock_dont_delete_me_data __tokudb_lock_dont_delete_me_temp $ ls -1 tokudb_data/test/

All of the tables files have been removed, but as mentioned above, the test still exists and needs to be removed manually.

Thanks for reading! We hope that you have found this blog series useful and look forward to hearing your experience with this new feature.

In the future we will investigate implementing the CREATE TABLE … DATA|INDEX DIRECTORY=… feature for TokuDB, which builds on top of this work.

We need to give a shout out to Vladislav Lesin, who took the lead on this issue and fought several battles in his attempt to ensure that this is fully crash safe and recoverable.

Categories: MySQL

Thoughts on MySQL 8.0 Invisible Indexes

MySQL Performance Blog - Thu, 2016-10-27 18:20

MySQL 8.0 has a new feature called “invisible indexes,” which allow you to quickly enable/disable indexes from being used by the MySQL Optimizer.

I wanted to share some of my first experiences and thoughts about this new feature.

Why is it good for us?

There are a couple of use cases. One of them is if you want to drop an index, but want to know the effect beforehand. You can make it invisible to the optimizer. It is a quick metadata change to make an index invisible. Once you are sure there is no performance degradation, you can then drop the index.

The main point is that the invisible index is unavailable for use by the optimizer, but it is still present and kept up-to-date by write operations. The optimizer won’t use it, even if we try to “FORCE INDEX”. I think we should be able to force it somehow, though. There might be scenarios where this could be useful:

  • We can create a new invisible index, but if we want to test it we have to make it visible. That means all the queries are going to be able to use it, which could have an immediate effect on the application. I don’t think this is the best approach if we just want to test it. You should always test on staging, but not everybody has the same data size or real life data on their staging servers. Forcing invisible indexes could be useful.
  • You have many indexes, but are not sure which one is not in use. You can change one index to invisible to see if there is any performance degradation. If yes, you can change it back immediately.
  • You could have a special case where only one query can use that index. In that case, an invisible index could be a great solution.
How can you create invisible indexes?

There are two options. We can create a table with an invisible index like this:

CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL, `j` int(11) DEFAULT NULL, `k` int(11) DEFAULT NULL, KEY `i_idx` (`i`), KEY `idx_1` (`i`,`j`,`k`) INVISIBLE ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Or we can use alter table and change the index to be invisible:

ALTER TABLE t1 ALTER INDEX idx_1 INVISIBLE;

Using invisible indexes

If we want to remove an index now, we can change it to invisible. But what about queries that use “FORCE/USE INDEX”? Are they are going to throw an error? If you force an index that does not exist, you get an error. You don’t get an error with invisible indexes. The optimizer doesn’t use it, but it knows that it exists.

mysql> show create table t1 G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL, `j` int(11) DEFAULT NULL, `k` int(11) DEFAULT NULL, KEY `i_idx` (`i`), KEY `idx_1` (`i`,`j`,`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> explain select * from t1 force index(idx_1) where i=1 and j=4; +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_1 | idx_1 | 10 | const,const | 2 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> alter table t1 alter index idx_1 invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 force index(idx_1) where i=1 and j=4; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 6.25 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select * from t1 where i=1 and j=4; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ref | i_idx | i_idx | 5 | const | 2 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

As you can see, if we use “FORCE INDEX” with an invisible index MySQL performs a full table scan because (from mysql.com):

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.

MySQL won’t throw any errors because the index exists, but it is not visible. Even if there is another usable index, it is going to perform a full table scan. On a large table, that could cause serious performance issues. Even if MySQL doesn’t throw any errors during the query execution, it should log a warning in the error log.

Conclusion

I think invisible indexes are a great new feature that could be useful for many customers. We should to be able to use an invisible index if necessary, and be able to log queries that are trying to use invisible indexes.

You can read more about invisible indexes in this blog post, and in the MySQL Documentation.

Categories: MySQL

Percona Poll: What Database Technologies Are You Using?

MySQL Performance Blog - Wed, 2016-10-26 19:53

Take Percona’s poll on what database technologies you use in your environment.

Different databases get designed for different scenarios. Using one database technology for every situation doesn’t make sense, and can lead to non-optimal solutions for common issues. Big data and IoT applications, high availability, secure backups, security, cloud vs. on-premises deployment: each have a set of requirements that might need a special technology. Relational, document-based, key-value, graphical, column family – there are many options for many problems. More and more, database environments combine more than one solution to address the various needs of an enterprise or application (known as polyglot persistence).

Please take a few seconds and answer the following poll on database technologies. Which are you using? Help the community learn what technologies critical database environments employ. Please select from one to six technologies as they apply to your environment.

If you’re using other solutions or have specific issues, feel free to comment below. We’ll post a follow-up blog with the results!

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

 

Categories: MySQL

Percona Live Open Source Database Conference 2017: Submit Papers ASAP!

MySQL Performance Blog - Wed, 2016-10-26 16:50

The Percona Live Open Source Database Conference 2017 call for papers is closing soon! Get in your submissions ASAP!

After this past sold-out Percona Live Europe in Amsterdam, we’ve decided that expansion definitely works! The diverse list of topics at the recent conference convinced us that variety is both the spice of life. It’s the key ingredient to a successful event.

For Percona Live 2017, we’re not just looking for MySQL and MongoDB topics, but also other open source databases. Submit your topics as soon as you can, the period closes on November 13, 2016!

We’re also planning to take this conference back to its roots. This means not focusing 100% on operations, but including a mix of talks that also benefit developers. For this year’s submissions, we are looking for both operation and developer topics, and this is reflected in the options when you submit talk ideas (Operations and Developers). We are still looking for business case studies for your database deployments, and there is also a Wildcard category. If you’ve got something interesting, please let us know by submitting!

Percona Live 2017 will also once again feature the MySQL and MongoDB 101 tracks this year.

The Percona Live 2017 Call for Papers is open until November 13, 2016. Do you have a MySQL, MongoDB, PostgreSQL or open source database use case to share, a skill to teach, or a big idea to discuss? We invite you to submit your speaking proposal for either breakout or tutorial sessions. This conference provides an opportunity to network with peers and technology professionals. It brings together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience.

Submit your proposal here.

Percona Live Open Source Database Conference 2017: Santa Clara, CA

The Percona Live Open Source Database Conference 2017 is the premier event for the diverse and active open source database community, as well as organizations that develop and use open source database software.

The conference will feature one day of tutorials and three days of keynote talks and breakout sessions related to open source databases and software. Learn about the hottest topics, building and maintaining high-performing deployments and what top industry leaders have to say.

The Percona Live Open Source Database Conference 2017 is April 24th – 27th, at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

Register for Percona Live 2017 now! Super Saver registration lasts until Nov 13. Percona Live is a very popular conference: this year’s Percona Live Europe sold out, and we’re looking to do the same for Percona Live 2017. Don’t miss your chance to get your ticket at its most affordable price. Click here to register.

Percona Live 2017 sponsorship opportunities are available now. Click here to find out how to sponsor.

Categories: MySQL

Monitoring OS metrics for Amazon RDS with Grafana

MySQL Performance Blog - Tue, 2016-10-25 21:35

This article describes how to visualize and monitor OS metrics for Amazon RDS instances using Grafana. We use CloudWatch as a data source, and Grafana to query CloudWatch API in real-time to get data. The setup instructions include just four quick steps (assuming you already use Grafana).

Setup

1. Create an IAM user on the AWS panel, and attach the managed policy “CloudWatchReadOnlyAccess”.

2. Put the credentials in the file “~grafana/.aws/credentials”:

[default] aws_access_key_id = youraccesskeyid aws_secret_access_key = yoursecretaccesskey

3. Add CloudWatch as the data source to Grafana, with the name “CloudWatch” (the pre-defined graphs rely on this name) and choose any region. Other fields can be left blank.

4. Import the dashboard JSON file from our collection of MySQL dashboards, or enter dashboard id “702” in the “Grafana.net Dashboard” field of the importing form in Grafana.

Finally, choose the AWS region, select the DB Instance,  and see the graphs. I have created my db.t2.micro instance on us-west-2 region, and named it “blackbox” to show these screenshots:

Here are the disk performance graphs (I am using a magnetic drive, and we shouldn’t expect many iops):

Available RAM, storage space and network traffic:

The dashboard uses 60s resolution and shows an average over each datapoint. An exception is the “CPU Credit Usage” graph, which has a five min. average and interval length. All data is fetched in real-time and not stored anywhere.

This dashboard can be used with any Amazon RDS DB engine, including MySQL, Aurora, etc.

Cost

Amazon provides one million CloudWatch API requests each month at no additional charge. Past this, it costs $0.01 per 1,000 requests. The pre-defined dashboard performs 15 requests on each refresh and an extra two on initial loading.

More monitoring for RDS

“Amazon RDS OS Metrics” dashboard will also be a part of the next release of Percona Monitoring and Management (PMM). If you are already using it, you can use the same instructions as in this blog post, but on step two you have to create a local credentials file (say /root/aws_credentials) and share it with pmm-server container by adding “-v /root/aws_credentials:/usr/share/grafana/.aws/credentials -e HOME=/usr/share/grafana” with the create command.

To enable MySQL metrics and query analytics for Amazon RDS using PMM platform, follow this guide.

Example of query analytics on the same instance:

MySQL metrics using the Prometheus data source (persistent storage):

Categories: MySQL

TokuDB and PerconaFT Data Files: Database File Management Part 2 of 2

MySQL Performance Blog - Mon, 2016-10-24 19:46

This is the second installment of the blog series on TokuDB and PerconaFT data files. You can find my previous post here. In this post we will discuss some common file maintenance operations and how to safely execute these operations.

Moving TokuDB data (table/index) files to another location outside of the default MySQL datadir

TokuDB uses the location specified by the tokudb_data_dir variable for all of its data files. If you don’t explicitly set the tokudb_data_dir variable, TokuDB uses the location specified by the servers datadir for these files.

The the __tokudb_lock_dont_delete_me_data file, located in the same directory as the TokuDB data files, protects TokuDB data files from concurrent process access.

TokuDB data files can be moved to other locations, and symlinks left behind in their place. If those symlinks refer to files on other physical data volumes, the tokudb_fs_reserve_percent monitor will not traverse the symlink and monitor the real location for adequate space in the file system.

To safely move your TokuDB data files:

  1. Shut down the server cleanly.
  2. Change the tokudb_data_dir in your my.cnf to the location where you wish to store your TokuDB data files.
  3. Create your new target directory.
  4. Move your *.tokudb files and your __tokudb_lock_dont_delete_me_data from the current location to the new location.
  5. Restart your server.
Moving the TokuDB temporary files to another location outside of the default MySQL datadir

TokuDB uses the location specified by the tokudb_tmp_dir variable for all of its temporary files. If you don’t explicitly set the tokudb_tmp_dir variable, TokuDB uses the location specified by the tokudb_data_dir. If you don’t explicitly set the tokudb_data_dir variable, TokuDB uses the location specified by the servers datadir for these files.

The __tokudb_lock_dont_delete_me_temp file, located in the same directory as the TokuDB temporary files, protects the TokuDB temporary files from concurrent process access.

If you locate your TokuDB temporary files on a physical volume that is different from where your TokuDB data files or recovery log files are located, the tokudb_fs_reserve_percent monitor will not monitor their location for adequate space in the file system.

To safely move your TokuDB temporary files:

  1. Shut the server down cleanly. A clean shutdown will ensure that there are no temporary files that need to be relocated.
  2. Change the tokudb_tmp_dir in your my.cnf to the location where you wish to store your new TokuDB temporary files.
  3. Create your new target directory.
  4. Move your __tokudb_lock_dont_delete_me_temp file from the current location to the new location.
  5. Restart your server.
Moving TokuDB recovery log files to another location outside of the default MySQL datadir

TokuDB uses the location specified by the tokudb_log_dir variable for all of its recovery log files. If the tokudb_log_dir variable is not explicitly set, TokuDB uses the location specified by the servers datadir for these files.

The __tokudb_lock_dont_delete_me_logs file, located in the same directory as the TokuDB recovery log files, protects TokuDB recovery log files from concurrent process access.

TokuDB recovery log files can be moved to another location and a symlinks left behind in place of the tokudb_log_dir. If that symlink refers to a directory on another physical data volume, the tokudb_fs_reserve_percent monitor will not traverse the symlink and monitor the real location for adequate space in the file system.

To safely move your TokuDB recovery log files:

  1. Shut the server down cleanly.
  2. Change the tokudb_log_dir in your my.cnf to the location where you wish to store your TokuDB recovery log files.
  3. Create your new target directory.
  4. Move your log*.tokulog* files and your __tokudb_lock_dont_delete_me_logs file from the current location to the new location.
  5. Restart your server.

I hope this blog series is helpful. Another post discussing a long-awaited new feature to help you manage and organize your TokuDB data files is coming soon.

Categories: MySQL

Announcing MyRocks in Percona Server for MySQL

MySQL Performance Blog - Mon, 2016-10-24 17:31

Announcing MyRocks in Percona Server for MySQL.

RocksDB has been taking the world by the storm!

RocksDB counts Facebook, LinkedIn, Pinterest, Airbnb and Netflix among its users. It is the storage engine for many open source database technologies, including CockroachDB, Apache Flink, TiKV and Dgraph. It is also integrated with MySQL and MongoDB as the MyRocks and MongoRocks storage engines, respectively.

Percona was an early RocksDB supporter, as well as an early adopter. MongoRocks was included with Percona Server for MongoDB since its first GA version.   

RocksDB has proven itself to be a very stable, high-performance technology. The enthusiastic,  high-energy engineering team behind it cares not just about making it work for Facebook (which can be the case with some technologies built mainly for internal use), but also about satisfying the needs of the open source community at large. So it shouldn’t be a big surprise that RocksDB is getting such traction.

We have been watching RocksDB’s MySQL implementation, MyRocks, very closely. It’s been steadily getting better performance and adding more and more features – making it feasible for a broad range of general MySQL workloads (not just Facebook use cases).

What was missing, however, were large scale production deployments of MyRocks. There is a big difference between how a technology does in tests versus how it does “in the wild.” Facebook announced last month that they completed a large-scale migration from InnoDB to MyRocks, and now use MyRocks in production.

As a result, Percona decided it was time to bring MyRocks to Percona Server. Including MyRocks with Percona Server will help make it easily accessible to more MySQL users. I announced the addition of MyRocks to Percona Server during my keynote speech at this year’s Percona Live Europe.

The MySQL community’s reaction was overwhelmingly positive. They can’t wait to explore MyRocks, and see if it is right for their workload. Percona engineering teams are working as fast as possible to release the Beta version of Percona Server with MyRocks (with a GA soon to follow), but if you can’t wait that long you can check out experimental MyRocks Docker images here.

Reading this, many of you are likely wondering: when? In our experience, integrating a storage engine into database software is not a trivial task, and progress is often tricky. Completing 95% of the integration is easy enough (sufficient for most workloads). However, the last 5% of the integration – dealing with outliers and corner cases – can be quite difficult. You often discover more issues just when you think it’s done.

We know you have high expectations for Percona Server, and need it to handle the most demanding environments. With this in mind, we’re not going rush a Percona Server with MyRocks release. We will provide the experimental builds of MyRocks in Percona Server in Q1 2017, and we encourage you to start testing and experimenting so we can quickly release a solid GA version.  

Percona’s purpose is to champion the best solutions for the open source community, and we think including MyRocks as an option for Percona Server for MySQL is a step in that direction.

Categories: MySQL

Life on Mobile: The iPhone Apps And Services I Use

Xaprb, home of innotop - Sun, 2016-10-23 01:46

In my last post I talked about the transition from Android to iOS and some of the experiences I had during that journey. Once I switched, I found a rich ecosystem of iPhone and iPad apps I use daily. Some of them are central to my workflow and professional life, and I use them across all my devices, including my laptop/desktop systems. In this post I’ll talk about those apps and how they enable me to run the bulk of my life (and my company) on a phone screen.

I’ll separate these apps into categories and explain how they work together for me.

Productivity, Research and Information-Gathering

The key apps I use to gather and organize information and research are Notes, Reminders, Feedly, Pocket, and Tweetbot. Notes and Reminders are Apple’s built-in apps. They are basic but, in my opinion, more useful than similar apps I’ve tried such as OneNote or Evernote.

I use Reminders for, well, reminders. It’s essentially categorized lists of things that can be marked complete, and which can be configured to alert at a time or when leaving or arriving at a location. So I can tell Siri “remind me to change my keyboard batteries when I get to work” and it’ll notify me when I arrive. I also share one list, Grocery, with my wife. I can say “Add milk to the Grocery list” and it’ll be there. If either of us buys milk we can mark it complete and that gets synced.

I use Notes for simple textual notes with paragraphs, bullet lists, and images. I outline articles, keep key facts, lists of reference material, and many more. I have a primary note called Tasks, which is stuff that’s in the short-term holding bin. It’ll get moved on to other notes later if I don’t do it right away. Then I have topical notes with information I’ve captured, websites (I don’t use browser bookmarks), drafts of articles, etc. Right now I’m working from a note called Blog Posts, where I’ve captured the key points in this post. I’m writing in another note that’s just about this article. The Blog Posts note has dozens of topics I’d like to write about someday. Other notes include research on a smart watch I’m considering, company culture, queueing theory, marketing resources and tasks, an upcoming webinar, and so on. I have about a hundred notes.

Notes help me get information out of my head so I can give myself permission to forget about it. I used to do this by carrying a pen and a scrap of paper in my pocket. I’d scribble down anything that came to mind and then put it from my thoughts. Now I just note it in the app.

I am a Google user, and Google Inbox potentially offers ways to capture many similar types of information, but for some reason I don’t find it as helpful as the Apple apps, which feel cleaner and less awkward to me. It’s just a feeling, not a fact.

I subscribe to a lot of RSS feeds, to keep up with what’s going on in the industry. I used to use Google Reader, and when it was canned I searched for a replacement and chose Feedly over similar offerings. Others I tried, such as Flipboard, seemed to veer away from RSS subscription functionality into a feed of pleasurable information in a magazine-like format that was intended to find stuff you didn’t know you wanted to read. That’s not what I want. I use RSS so I can be sure I catch news from people and companies I want, and I don’t get unwanted content.

Feedly is great at helping me scan through the feeds from sites I’ve decided to follow, and it integrates with Pocket for saving articles for later. I can long-press an article and it’s saved to Pocket. I don’t usually read in Feedly, I just browse and save for later.

Pocket is also one that I chose from among a handful, including Readability, Instapaper, and Evernote again. I’ve found it to be a great way to read and archive things I want to use as reference. And of course, it’s much better for reading than the websites themselves, which often have ads and distracting layouts or annoying things like expanding and shrinking header bars.

I’m a premium customer of both Feedly and Pocket. I also use Tweetbot to discover information, and long-pressing on a Tweet brings up a dialog to either save to Pocket or add to a Note. Depending on the Tweet, I’ll do one or the other. Tweetbot is worlds better than the official Twitter client. I’ll write a blog post about it alone someday. Its features give me control over my timeline and what I want to see. The official Twitter app is just a distraction and entertainment service, but with Tweetbot, Twitter becomes a vital part of how I interact with and stay in touch with key people, industries, and companies.

Apps For Work

My main work-related apps are Google Inbox, Google Drive, Google Sheets, Google Docs, and Google Presentations. I also have Microsoft Office apps installed, but they’re painful to use and I don’t like them. And there’s the Apple office-document apps: Keynote, etc. I use them sometimes, but mostly just use Google apps. Google Drive is the killer app here; things really work well together with Google Drive in the Apple ecosystem. Much better than iCloud or Dropbox. Google Maps is also much better than Apple Maps.

I used to use GMail on Android, but found that any of the alternatives are better on iOS: Outlook, the built-in Mail, but most especially Inbox.

I use Google Calendar to run my life. If it’s free on my calendar, you can book me then. My calendar tells me where to go and what to prepare for. The Google Calendar app for iPhone is great. It isn’t built natively for iPad (why???) so I use the built-in Apple Calendar app there instead. It’s good enough to work well, but tends to sync a little bit more slowly.

The sore points in Google apps for work are Hangouts and Google+. They feel like abandonware.

Some other apps I use for work:

  • FullContact pulls together my contacts from many places and keeps them synced up. It’s a big win. I’m a paying customer.
  • I occasionally use Adobe Fill & Sign to sign a PDF, but it’s awkward, so I try not to.
  • Duet Display is amazing. It lets me plug my iPad or iPhone into my laptop and use it as an external monitor. This is great for when I’m on the go.
  • I use GoToMeeting sometimes. I use it more on my desktop/laptop.
  • 1Password is indispensable for me. I sync its files over Dropbox, and use it on all of my mobile and desktop devices. It’s so, so good. I do not allow my devices themselves to remember any of my passwords or auto fill things in browsers or system keychains.
  • We use Asana at work, but I don’t use it much. I haven’t opened the Asana app in a while.
  • We use Salesforce at work too. The Salesforce app is pretty bad. It’s only slightly better than LinkedIn.
News

I use a few news-related apps. The main one is New York Times. I’m a premium subscriber there. I came to the NYT through their amazing NYTNow app, which they discontinued. The success of that app seems to have informed the main NYT app, which offers much the same experience in kind of a subset of the app.

I also, less frequently, use Al Jazeera (AJ English). I appreciate the non-US-centric worldview I find there. Every company’s citizenry tells itself they’re the best in the world, and America is no different. Hearing how we’re viewed from elsewhere is important to me. I’ve tried BBC and Reuters and other services, but Al Jazeera somehow hits a sweet spot for me.

In my car, I listen to NPR all day every day, but the NPR apps are what happens when someone tries to app-ify radio without understanding what an app is or why people use it. I honestly tried the NPR and NPR One apps and both times just couldn’t find words to describe the disconnect. I support my local NPR station when they fundraise, and that’s good enough for me.

Apple has a News app, which used to be great. It used to let me specify what news sources I wanted to get stories from, approve or disapprove of stories, and it learned what I wanted. (All substance, no sports, no celebrities, etc.) It was really amazing. I raved about it on Twitter. Then a few months ago they replaced it with a “we’ll show you popular content, without offering you much control” app. It’s the same experience I remember from Flipboard and so on. I don’t use it anymore.

Stories and Entertainment

I’m a paid Spotify subscriber, though due to the fact that I have a large music library that’s pretty esoteric and isn’t on any of the major music services, Apple Music’s ability to sync my library is a must-have. I use Apple Music 95% of the time these days. I wrote previously about the encoding quality issues I had with Google Play Music. I think I remember having much the same dismal experience with Amazon Music. There are other services too, such as Pandora, but none of them offers the combination of streaming, “radio,” and my own library as far as I know.

Twitter counts as an entertainment service sometimes. I use Tweetbot, as mentioned. I also use Netflix and occasionally the Amazon Movies app. These work a lot better on iOS than in a browser on a laptop. They use a lot less energy and generate much less heat. Sometimes I also use the IMDB app.

I listen to a few podcasts using the official Podcasts app: This American Life, Radiolab, and so on. It works well and I enjoy using it.

I also use iBooks sometimes to listen to audiobooks, most of which are from Audible.com. I’ve installed, but never used, the LibriVox app; and I have various apps for religious texts, such as the Life.Church Bible app.

Games

I’m not much of a gamer, but I’ve found a few games I really have enjoyed on the iPad. The first is Monument Valley, which has to be played to be understood. It’s a gorgeous, minimalistic game of exploring Escher-like architecture to solve puzzles.

Lara Craft GO is somewhat in a similar vein, although it has a different feel (Lara Craft can die, and she has to do things like kill snakes and lizards to reach destinations; Monument Valley is nonviolent.)

Prune is another minimalistic, beautiful game. Trees sprout out of the ground and you prune the branches to help them grow tall and straight. It’s a puzzle-solving and strategy game in some ways, but it’s really more about the feeling of the graphics than anything else.

The Room is amazing. I won’t spoil it for you. There are three editions of it now. Each is better than the last. (Start with the first.)

You can find YouTube videos of all of these to get a better sense of what they’re like.

Photos and Movies

I don’t use the built-in iMovie app. When I record a movie with the Camera app, I almost always upload it with YouTube right afterwards and then delete it. I keep a library of unlisted movies of my family and so on in YouTube. I find this is the easiest way to share movies with people, by far. The sharing features in the Apple Photos app are just frustrating.

For photo editing, usually the Apple Photos app is enough—I usually do little beyond simple cropping and color correction. There’s a Photoshop app, but it’s all but useless. I tried Google SnapSeed, but that’s not really an improvement over the built-in app either. Eventually I purchased Pixelmator, which is quite good and lets me do things I previously only could do on the desktop Photoshop app.

I use the Google Photos app, with auto-backup enabled, to get photos off my devices and into my Google account, so I can delete them from the device. If I don’t do this, I run out of storage space on my phone. I don’t find the iCloud Photo Library to be very good. It doesn’t solve the space problems and it has a lot of trouble syncing in my experience. But Google Photos is actually pretty brilliant.

VLC is able to play internet-hosted streams and weird video formats that crop up occasionally.

Programming

It’s actually possible to do a limited amount of coding on an iPad. I use the Prompt app for an SSH client, and iZip Pro for very occasional uncompressing jobs.

I’ve found Working Copy Enterprise to be great for blogging. This blog is Markdown files hosted on GitHub, served by Netlify. All I have to do is commit and push a new Markdown file to GitHub, and Netlify gets pinged to pull and build the new version of the site. It’s secure, fast, and easy. Working Copy is a complete Git + text-editor app. I use it to clone Git repos from GitHub, create and edit files (including images), commit, and push. When I’m done writing this blog draft in Notes, it’ll be less than a minute for me to push it live. A lot of that is due to Working Copy. It’s really well done.

I tried Octopage and CodeHub for static site blogging with GitHub, but they are too limited to work well for me.

I haven’t found a good way to join a VPN. I have the OpenVPN Connect app, but have never gotten it to work. I can get it to connect, but I can’t access the VPN network.

Other

The Amazon app is really good on iOS. I prefer it over using the website in a desktop browser. At times I’ve also installed and used DealNews and eBay. The Apple Store app was actually a pretty terrible experience. I recall it took a gigabyte of storage on my phone (which it also had to first download). I deleted it instantly.

Buffer is great for scheduling social media sharing. I’m a paying customer. I use it for things like my inspirational tweets, which I wrote about previously. Its sharing extension is good too, but I rarely use it that way.

1Blocker kills a lot of the bloat from many websites. And reminds me, when I accidentally go to Forbes or HBR, that I don’t want to be there in the first place.

I use Headspace every single day. I use Yoga Studio several times a week.

Despite the fact that I wrote an absolutely epic blog post about my decades-long fascination with the art and science of tuning instruments by ear, I’ve found PanoTuner to be really helpful.

Airline apps are great for checking in, getting a boarding pass, and getting through security and onto the plane without fussing with paper and waiting in lines.

Conclusion

From airline apps to “git commit” I’ve found that I can run the bulk of my digital life on iPhones and iPads. Most of my common tasks—email, calendaring, notes, and the like, are not only possible, but better on mobile. By choosing apps and services that work seamlessly on mobile, a lot of my life fits in the pocket of my jeans, and I can be productive from anywhere, anytime.

But it’s not all ponies and rainbows. Next time I’ll talk about the little edge cases that don’t quite work, the things that keep me from getting rid of my laptop.

Photo Credit

Categories: MySQL

Life on Mobile: The iPhone Apps And Services I Use

Xaprb, home of innotop - Sun, 2016-10-23 01:46

In my last post I talked about the transition from Android to iOS and some of the experiences I had during that journey. Once I switched, I found a rich ecosystem of iPhone and iPad apps I use daily. Some of them are central to my workflow and professional life, and I use them across all my devices, including my laptop/desktop systems. In this post I’ll talk about those apps and how they enable me to run the bulk of my life (and my company) on a phone screen.

I’ll separate these apps into categories and explain how they work together for me.

Productivity, Research and Information-Gathering

The key apps I use to gather and organize information and research are Notes, Reminders, Feedly, Pocket, and Tweetbot. Notes and Reminders are Apple’s built-in apps. They are basic but, in my opinion, more useful than similar apps I’ve tried such as OneNote or Evernote.

I use Reminders for, well, reminders. It’s essentially categorized lists of things that can be marked complete, and which can be configured to alert at a time or when leaving or arriving at a location. So I can tell Siri “remind me to change my keyboard batteries when I get to work” and it’ll notify me when I arrive. I also share one list, Grocery, with my wife. I can say “Add milk to the Grocery list” and it’ll be there. If either of us buys milk we can mark it complete and that gets synced.

I use Notes for simple textual notes with paragraphs, bullet lists, and images. I outline articles, keep key facts, lists of reference material, and many more. I have a primary note called Tasks, which is stuff that’s in the short-term holding bin. It’ll get moved on to other notes later if I don’t do it right away. Then I have topical notes with information I’ve captured, websites (I don’t use browser bookmarks), drafts of articles, etc. Right now I’m working from a note called Blog Posts, where I’ve captured the key points in this post. I’m writing in another note that’s just about this article. The Blog Posts note has dozens of topics I’d like to write about someday. Other notes include research on a smart watch I’m considering, company culture, queueing theory, marketing resources and tasks, an upcoming webinar, and so on. I have about a hundred notes.

Notes help me get information out of my head so I can give myself permission to forget about it. I used to do this by carrying a pen and a scrap of paper in my pocket. I’d scribble down anything that came to mind and then put it from my thoughts. Now I just note it in the app.

I am a Google user, and Google Inbox potentially offers ways to capture many similar types of information, but for some reason I don’t find it as helpful as the Apple apps, which feel cleaner and less awkward to me. It’s just a feeling, not a fact.

I subscribe to a lot of RSS feeds, to keep up with what’s going on in the industry. I used to use Google Reader, and when it was canned I searched for a replacement and chose Feedly over similar offerings. Others I tried, such as Flipboard, seemed to veer away from RSS subscription functionality into a feed of pleasurable information in a magazine-like format that was intended to find stuff you didn’t know you wanted to read. That’s not what I want. I use RSS so I can be sure I catch news from people and companies I want, and I don’t get unwanted content.

Feedly is great at helping me scan through the feeds from sites I’ve decided to follow, and it integrates with Pocket for saving articles for later. I can long-press an article and it’s saved to Pocket. I don’t usually read in Feedly, I just browse and save for later.

Pocket is also one that I chose from among a handful, including Readability, Instapaper, and Evernote again. I’ve found it to be a great way to read and archive things I want to use as reference. And of course, it’s much better for reading than the websites themselves, which often have ads and distracting layouts or annoying things like expanding and shrinking header bars.

I’m a premium customer of both Feedly and Pocket. I also use Tweetbot to discover information, and long-pressing on a Tweet brings up a dialog to either save to Pocket or add to a Note. Depending on the Tweet, I’ll do one or the other. Tweetbot is worlds better than the official Twitter client. I’ll write a blog post about it alone someday. Its features give me control over my timeline and what I want to see. The official Twitter app is just a distraction and entertainment service, but with Tweetbot, Twitter becomes a vital part of how I interact with and stay in touch with key people, industries, and companies.

Apps For Work

My main work-related apps are Google Inbox, Google Drive, Google Sheets, Google Docs, and Google Presentations. I also have Microsoft Office apps installed, but they’re painful to use and I don’t like them. And there’s the Apple office-document apps: Keynote, etc. I use them sometimes, but mostly just use Google apps. Google Drive is the killer app here; things really work well together with Google Drive in the Apple ecosystem. Much better than iCloud or Dropbox. Google Maps is also much better than Apple Maps.

I used to use GMail on Android, but found that any of the alternatives are better on iOS: Outlook, the built-in Mail, but most especially Inbox.

I use Google Calendar to run my life. If it’s free on my calendar, you can book me then. My calendar tells me where to go and what to prepare for. The Google Calendar app for iPhone is great. It isn’t built natively for iPad (why???) so I use the built-in Apple Calendar app there instead. It’s good enough to work well, but tends to sync a little bit more slowly.

The sore points in Google apps for work are Hangouts and Google+. They feel like abandonware.

Some other apps I use for work:

  • FullContact pulls together my contacts from many places and keeps them synced up. It’s a big win. I’m a paying customer.
  • I occasionally use Adobe Fill & Sign to sign a PDF, but it’s awkward, so I try not to.
  • Duet Display is amazing. It lets me plug my iPad or iPhone into my laptop and use it as an external monitor. This is great for when I’m on the go.
  • I use GoToMeeting sometimes. I use it more on my desktop/laptop.
  • 1Password is indispensable for me. I sync its files over Dropbox, and use it on all of my mobile and desktop devices. It’s so, so good. I do not allow my devices themselves to remember any of my passwords or auto fill things in browsers or system keychains.
  • We use Asana at work, but I don’t use it much. I haven’t opened the Asana app in a while.
  • We use Salesforce at work too. The Salesforce app is pretty bad. It’s only slightly better than LinkedIn.
News

I use a few news-related apps. The main one is New York Times. I’m a premium subscriber there. I came to the NYT through their amazing NYTNow app, which they discontinued. The success of that app seems to have informed the main NYT app, which offers much the same experience in kind of a subset of the app.

I also, less frequently, use Al Jazeera (AJ English). I appreciate the non-US-centric worldview I find there. Every company’s citizenry tells itself they’re the best in the world, and America is no different. Hearing how we’re viewed from elsewhere is important to me. I’ve tried BBC and Reuters and other services, but Al Jazeera somehow hits a sweet spot for me.

In my car, I listen to NPR all day every day, but the NPR apps are what happens when someone tries to app-ify radio without understanding what an app is or why people use it. I honestly tried the NPR and NPR One apps and both times just couldn’t find words to describe the disconnect. I support my local NPR station when they fundraise, and that’s good enough for me.

Apple has a News app, which used to be great. It used to let me specify what news sources I wanted to get stories from, approve or disapprove of stories, and it learned what I wanted. (All substance, no sports, no celebrities, etc.) It was really amazing. I raved about it on Twitter. Then a few months ago they replaced it with a “we’ll show you popular content, without offering you much control” app. It’s the same experience I remember from Flipboard and so on. I don’t use it anymore.

Stories and Entertainment

I’m a paid Spotify subscriber, though due to the fact that I have a large music library that’s pretty esoteric and isn’t on any of the major music services, Apple Music’s ability to sync my library is a must-have. I use Apple Music 95% of the time these days. I wrote previously about the encoding quality issues I had with Google Play Music. I think I remember having much the same dismal experience with Amazon Music. There are other services too, such as Pandora, but none of them offers the combination of streaming, “radio,” and my own library as far as I know.

Twitter counts as an entertainment service sometimes. I use Tweetbot, as mentioned. I also use Netflix and occasionally the Amazon Movies app. These work a lot better on iOS than in a browser on a laptop. They use a lot less energy and generate much less heat. Sometimes I also use the IMDB app.

I listen to a few podcasts using the official Podcasts app: This American Life, Radiolab, and so on. It works well and I enjoy using it.

I also use iBooks sometimes to listen to audiobooks, most of which are from Audible.com. I’ve installed, but never used, the LibriVox app; and I have various apps for religious texts, such as the Life.Church Bible app.

Games

I’m not much of a gamer, but I’ve found a few games I really have enjoyed on the iPad. The first is Monument Valley, which has to be played to be understood. It’s a gorgeous, minimalistic game of exploring Escher-like architecture to solve puzzles.

Lara Craft GO is somewhat in a similar vein, although it has a different feel (Lara Craft can die, and she has to do things like kill snakes and lizards to reach destinations; Monument Valley is nonviolent.)

Prune is another minimalistic, beautiful game. Trees sprout out of the ground and you prune the branches to help them grow tall and straight. It’s a puzzle-solving and strategy game in some ways, but it’s really more about the feeling of the graphics than anything else.

The Room is amazing. I won’t spoil it for you. There are three editions of it now. Each is better than the last. (Start with the first.)

You can find YouTube videos of all of these to get a better sense of what they’re like.

Photos and Movies

I don’t use the built-in iMovie app. When I record a movie with the Camera app, I almost always upload it with YouTube right afterwards and then delete it. I keep a library of unlisted movies of my family and so on in YouTube. I find this is the easiest way to share movies with people, by far. The sharing features in the Apple Photos app are just frustrating.

For photo editing, usually the Apple Photos app is enough—I usually do little beyond simple cropping and color correction. There’s a Photoshop app, but it’s all but useless. I tried Google SnapSeed, but that’s not really an improvement over the built-in app either. Eventually I purchased Pixelmator, which is quite good and lets me do things I previously only could do on the desktop Photoshop app.

I use the Google Photos app, with auto-backup enabled, to get photos off my devices and into my Google account, so I can delete them from the device. If I don’t do this, I run out of storage space on my phone. I don’t find the iCloud Photo Library to be very good. It doesn’t solve the space problems and it has a lot of trouble syncing in my experience. But Google Photos is actually pretty brilliant.

VLC is able to play internet-hosted streams and weird video formats that crop up occasionally.

Programming

It’s actually possible to do a limited amount of coding on an iPad. I use the Prompt app for an SSH client, and iZip Pro for very occasional uncompressing jobs.

I’ve found Working Copy Enterprise to be great for blogging. This blog is Markdown files hosted on GitHub, served by Netlify. All I have to do is commit and push a new Markdown file to GitHub, and Netlify gets pinged to pull and build the new version of the site. It’s secure, fast, and easy. Working Copy is a complete Git + text-editor app. I use it to clone Git repos from GitHub, create and edit files (including images), commit, and push. When I’m done writing this blog draft in Notes, it’ll be less than a minute for me to push it live. A lot of that is due to Working Copy. It’s really well done.

I tried Octopage and CodeHub for static site blogging with GitHub, but they are too limited to work well for me.

I haven’t found a good way to join a VPN. I have the OpenVPN Connect app, but have never gotten it to work. I can get it to connect, but I can’t access the VPN network.

Other

The Amazon app is really good on iOS. I prefer it over using the website in a desktop browser. At times I’ve also installed and used DealNews and eBay. The Apple Store app was actually a pretty terrible experience. I recall it took a gigabyte of storage on my phone (which it also had to first download). I deleted it instantly.

Buffer is great for scheduling social media sharing. I’m a paying customer. I use it for things like my inspirational tweets, which I wrote about previously. Its sharing extension is good too, but I rarely use it that way.

1Blocker kills a lot of the bloat from many websites. And reminds me, when I accidentally go to Forbes or HBR, that I don’t want to be there in the first place.

I use Headspace every single day. I use Yoga Studio several times a week.

Despite the fact that I wrote an absolutely epic blog post about my decades-long fascination with the art and science of tuning instruments by ear, I’ve found PanoTuner to be really helpful.

Airline apps are great for checking in, getting a boarding pass, and getting through security and onto the plane without fussing with paper and waiting in lines.

Conclusion

From airline apps to “git commit” I’ve found that I can run the bulk of my digital life on iPhones and iPads. Most of my common tasks—email, calendaring, notes, and the like, are not only possible, but better on mobile. By choosing apps and services that work seamlessly on mobile, a lot of my life fits in the pocket of my jeans, and I can be productive from anywhere, anytime.

But it’s not all ponies and rainbows. Next time I’ll talk about the little edge cases that don’t quite work, the things that keep me from getting rid of my laptop.

Photo Credit

Categories: MySQL
Syndicate content