Skip to content

Category: MySQL

Good Riddance to the Query Cache

MySQL 8.0 will be retiring support for the Query Cache.

The MySQL Query cache is a result cache: The MySQL server will record all result sets that are small enough to keep in the cache, and a hash of the query that produced it. If a query meets certain requirements, and the hash of the same query string is ever seen again, the query will not be actually parsed and executed, but the same result set will be replayed.

There are mechanisms in place that prevent uncacheable queries from being cached in the first place, and that prune outdated data from the query cache.

The query cache exists in the first place, because it was easier to create than to teach every PHP CMS developer in the world about sessions. So instead of retrieving the current background color of the current theme over and over from the database, the query cache recognizes the current theme color query again and just replays “green” over and over.

But that was then.

Leave a Comment

Protecting MySQL Network Traffic

Percona Live Talk by Daniël van Eeden: Protecting MySQL Network Traffic.

Warning: It is somewhat more complicated than this:

Slideshare

Check out the performance slide (#22), too.

Tl;Dr: You want a MySQL compiled against OpenSSL, because SSL Tickets and AES-NI support. YaSSL sucks, hard. With Tickets and hardware symmetric encryption, TLS support in MySQL is actually no longer slow.

Tl;DR 2: MariaDB is actually pretty well positioned here.

Leave a Comment

Optane/Xpoint and paradigm shift

So Optane is here. It’s a bit-addressable non-volatile storage with a higher density than DRAM. It’s not as good as initially promised, yet, but it’s a first iteration.

It is basically very slow RAM (or very fast flash), which is bit-adressable. So you are not, like with flash, erasing 64 KB sized pages, but you are doing things to individual bits and bytes.

It’s also faster than flash (but slower than DRAM), about 10x faster than old Commodore 64 memory.

And it’s persistent, so if you power off your machine, contents are not gone.

And it is very dense, denser even than the memory you currently use, because no transistors, so less space necessary per bit.

This is going to change a lot of things, but not right now. We need to rethink our approach to persistence.

4 Comments

Galera vs. Group Replication

Percona: Galera ./. Group Replication

A blog post over at Percona discusses better replication for MySQL and compares Galera and MySQL Group Replication.

Galera builds their own initial state transfer mechanism and their own transaction distribution mechanism, independently of MySQL replication (write set replication wsrep). wsrep is synchronous – on commit, the write set is shipped, applied and acknowledged (or not).

MySQL Group Replication strives to achive the same thing, but uses their own, “MySQL native” set of technologies to do this.

Leave a Comment

Some basics about distributed databases

This is a replay of a much older blog post, which was available in German language in the old blog. It’s from 2012, and neither GTID nor Galera cluster or Group Replication existed back then.

Wonka> The http://www.toppoint.de probably will never have meaningful load, but I would like to know how one would make this highly available. Some kind of Redundant Array of Inexpensive Databases.

Lalufu> MySQL with replication? Or DRBD?

Isotopp> With DRBD. Not with replication.

1 Comment

MySQL and encrypted connections

2006 slides by Rasmus Lerdorf

Since 5.0, MySQL does allow natively encrypted connections to the database, and supposedly also does support client certs for user authentication. Supposedly, because I never tried.

MySQL as a database performs well with transient connections as they are prevalent in two-tier deployments (mod_php, mod_perl, mod_python to database), in which a database connection is made upon web request, and the connection is torn down at the end of the request. This model does not scale so well with encryption in the mix, as on connection a full TLS/SSL exchange must be made.

2 Comments