Recent Posts by Mark Lewin

A really convenient feature in MySQL 8.0 is the ability to persist the values of global variables across server restarts, without writing them into an options file. This was developed primarily for the benefit of Cloud installations of MySQL, but is very handy for a DBA in on-premise installations too. To use this feature you need to have the SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges. To demonstrate, we'll increase the value of the max_connections system...

Have you ever tried to improve the performance of a query by adding a new index to a table with a large number of rows, waited for ages while the index is built, only to discover that it didn't really help as much as you'd like? Or perhaps you have had an index that you have a hunch isn't doing much to help your query performance and decided to drop it, only to have a bunch of users complain that their queries are stalling? If so, then you will appreciate a nice new feature in...

For many programming languages, it's pretty easy to figure out which Connector to use. Java has Connector/J, if you're using C# or ASP you'll opt for Connector/.NET: the clue is in the name. For PHP however, things aren't quite so straightforward. If you want to write a PHP application that communicates with a MySQL database, you have a choice of PHP extensions that you can use: mysql, mysqli, and PDO_MySQL. We can simplify that list right away by discounting the mysql extension...

We've been busy in the MySQL Curriculum team and are pleased to announce the recent update of the MySQL Performance Tuning course. This course has been substantially revised to take advantage of the performance enhancements available in MySQL 5.7, including new Performance Schema instrumentation and improvements in InnoDB and the Query Optimizer. Even more so than in previous versions of the course, we try to filter out what's "nice to know" and focus on the really...

Like any mature technology that is constantly evolving, MySQL has amassed a jargon of its own. Some of this terminology occasionally causes confusion, especially when two terms are similar, but actually refer to two completely different things. This is particularly the case for the two storage engines InnoDB and NDB (which sound very alike when spoken aloud), and the two "Cluster" technologies: InnoDB Cluster and MySQL Cluster. Let's see if we can clear this confusion up. InnoDB ...

Replication lag occurs when the slaves (or secondaries) cannot keep up with the updates occuring on the master (or primary). Unapplied changes accumulate in the slaves' relay logs and the version of the database on the slaves becomes increasingly different from that of the master. To work out what's causing the lag, you must determine which replication thread is getting backed up. Replication relies on three threads per master/slave connection: one is created on the master and...

The two status variables Queries and Questions can sometimes cause confusion. On the face of it, both appear to count the number of times a query is executed on the server. Each variable has both session and global scope, meaning that you can get statistics for both the current connection and for all connections. You can reset the session variables by executing FLUSH STATUS, but resetting the global variables requires a server restart. All good so far. However, the way in which...

While it was always technically possible to store JSON in MySQL database columns, it was never a particularly attractive option. JSON data is just text, so any string type of sufficient length will take it quite happily. However, getting your JSON into the database using this method is one thing, but getting it back out again in any useful format had to be the responsibility of your application. All that changed in MySQL 5.7.8 with the introduction of a native JSON data type...

I used to work for a company specializing in Geographical Information Systems, and I still get excited about using computers to interact with geospatial data. Once a field that interested only geography geeks, GIS has now become mainstream, with just about every mobile app offering some kind of location awareness. Whereas many such applications use some kind of third-party API to work with spatial data, with MySQL you can do this right in the database itself. MySQL provides a...