Skip to content

Category: MySQL

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

Post like it is 2015

Following a great idea from their friends at GitLab, Soup.io loses all postings since 2015 because of malfunctioning backups. They write:

We had a big database crash, and the backups we had were corrupted.
The only working backup was from 2015.

Also, TIL soup.io still exists. Meanwhile, Gitlab posted a blameless postmortem. You can read it online, and they write:

Improving Recovery Procedures

[…]
9. Automated testing of recovering PostgreSQL database backups (#1102)
[…]

Does your database backup successfully restore? Are you sure? Are you testing this?

Remember these words of wisdom:

Nobody wants backup.
Everybody wants restore.
— Martin Seeger

1 Comment

How can MySQL replicate? Let me count the ways

Percona Blog: MySQL Replication Overview

The Percona Blog has an Overview Of Different MySQL Replication Solutions. On top of the regular traditional Async Replication shown above, they also cover SemiSync, Group Replication, and Galera Cluster. Statement and Row Based Replication are contrasted.

Finally, “some misconceptions about replication” are being addressed, with “Replication is not a cluster”, “Replication is a HA solution”, “Replication replaces backups” and “Replication replaces Load Balancing”.

Leave a Comment

PHP 7: mysql extension deprecated

In mysql() nach PHP 7 retten, Charly Kühnast explains how you can get the deprecated and disabled mysql extension back in PHP 7. You shouldn’t.

There are many reasons for this. One of them being that none of the newer features in MySQL can be used with the old mysql extensions. There is an overview in the PHP documentation that explains exactly what you are missing.

One of the things that you are missing is support for prepared statements. Prepared statements are a mechanism in which you write SQL statements with placeholders for variables, and then later bind values to the placeholders using a “bind” call or as part of the “execute” call which is actually running the statement. In any case, the variables are being escaped properly automatically, making SQL injection a lot harder.

This is not just a problem limited to PHP – a search for bind and execute other sources can be very instructive. For example, the sources of Opennebula or in older versions of Owncloud (up to and including version 7) are rich treasure troves of potential exploits.

So currently the situation is as follows:

There are three extensions at the PHP level, one of which is deprecated and disabled in PHP 7:

  • The old mysql extension is no longer available by default, and for good reasons. Do not use it, do not attempt to use code that uses it.
  • The mysqli extension has been around for very many years, and offers a procedural and an object oriented interface, and makes “newer” MySQL features available, including prepared statements.
  • The PDO_mysql extension has been around for many years, too, and offers an object oriented, and portable across databases interface. It also allows access to all “newer” MySQL features.

The wire protocol of all of these extensions is implemented by a C-level library, against which the extension can be linked. A manual page explains the choices.

  • Traditionally that has been the Oracle/MySQL C-API (“libmysqlclient”, “Connector/C”), which comes with the database server. It is available on the GPL, which is a license different from the PHP license of the rest of the PHP proper, and it has it’s own memory management, which is different from the PHP native memory management.
  • Since PHP 5.3, there is mysqlnd (the “native driver”, ND). It re-implements the MySQL wire protocol, and is available under the same license as PHP itself. It also uses the same memory management that PHP uses, which makes it faster (no copying) and more efficient (no duplication of values). It is the default on a normal PHP build these days.

What you should be using: These days, your code should not be using the mysql extension. So you will be using mysqli or PDO_mysql, depending on your needs, with the underlying implementation of the native driver doing the heavy lifting.

Do not attempt to port mysql-Extension based code to PHP 7 without refactoring it for prepared statements, please.

 

Leave a Comment