MySQL Server Team Blog

[yeah, not a frequent poster, apologies for the markdown markup]

Thanks for sharing this, it's been fun to play with. I've been poking around a bit, looks like `skip locked` was added back in v9.5.

For those who are interested in how it works, here are a few links that might be useful:

- [What is SKIP LOCKED for in PostgreSQL 9.5?][2ndquadrant]

- [Postgres 9.5 feature highlight - SKIP LOCKED for row-level locking][otacoo]

- [The Skip Locked feature in Postgres 9.5][pgcasts]

The 2ndquadrant post points out that Oracle and SQL Server have similar functionality. Mysql didn't at the time, but [seems to have added it in 8.0.1][mysql]

[2ndquadrant]: https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/

[mysql]: https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/

[otacoo]: https://web.archive.org/web/20160626090321/http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-skip-locked-row-level/

[pgcasts]: https://www.pgcasts.com/episodes/the-skip-locked-feature-in-postgres-9-5/

I understand this is or will be a part of WebScaleSQL [1]. But WebScaleSQL does not provide binaries. You have to build it yourself.

An alternative is the JSON Labs Release of MySQL 5.7.7 [2]. The differences are explained in the slides, at the end.

Another great alternative is using PostgreSQL that supports JSON columns stored in a binary format since version 9.4 [3].

That said, I really like the approach of Facebook with DocStore, because of its use of "document paths", instead of functions.

[1] http://webscalesql.org/

[2] http://mysqlserverteam.com/json-labs-release-overview/

[3] http://www.postgresql.org/docs/9.4/static/datatype-json.html

The table is about dependency checking for those constructs.

E.g. SELECT countries.code, countries.name, count(*) FROM countries JOIN countries_languages as co_lang on co_lang.country_code = countries.code GROUP BY countries.code

This isn't supported by Postgres, because countries.name is not in the group by clause, on the other hand it does make sense, because code will be unique in the countries table, so there will always be only one name for a given code.

(see https://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/ which is also linked from the original article)

From my reading of the article, a lot of the performance improvements come getting rid of the mutex in front of their hash based metadata cache by using a lock free hashing algo. These changes originally came from Oracle's MySQL 5.7 work[1]. Looks like MariaDB merged the lock-free hashing stuff into their version, and applied it to the table definition hash which also suffered from lock contention.

These are changes that all architectures can benefit from.

[1]: http://mysqlserverteam.com/removing-scalability-bottlenecks-in-the-metadata-locking-and-thr_lock-subsystems-in-mysql-5-7/

While it's clear that the author of the linked article isn't familiar with MySQL administration, it's worth noting that the most recent versions of MySQL finally support instant column adds: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

That won't save us from needing extra tooling like pt-online-schema-change or gh-ost for other types of schema changes in high availability environments, but it's a step in the right direction.

I did quite some work with MySQL+srid, mostly with OpenStreetMap data, but also other, but I'm no expert there. Maybe your specific SRID wasn't supported? (While a huge number is supported)

Norvald has a few slide sets, which might be good addition to docs:
https://www.slideshare.net/mobile/NorvaldRyeng
There are also blogs on https://mysqlserverteam.com/category/gis/

Without details about your experiments it's hard to find the issue ...