dhd415

I'm happy to see that MySQL is progressing, but I think that enumerating its adherence to the latest SQL standards overlooks some of its other serious flaws from the perspective of operations and reliability. Below are two of the worst operational situations I've encountered with MySQL. I've run several other databases in high-volume production environments and never encountered those kinds of problems. That MySQL's developers would have made these choices that result in those kinds of problems is truly mind-boggling to me.

* If you have pre-5.6.4 date/time columns in your table and perform any kind of ALTER TABLE statement on that table (even one that doesn't involve the date/time columns), it will TAKE A TABLE LOCK AND REWRITE YOUR ENTIRE TABLE to upgrade to the new date/time types. In other words, your carefully-crafted online DDL statement will become fully offline and blocking for the entirety of the operation. To add insult to injury, the full table upgrade was UNAVOIDABLE until 5.6.24 when an option (still defaulted to off!) was added to decline the automatic upgrade of date/time columns. If you couldn't upgrade to 5.6.24, you had two choices with any table with pre-5.6.4 types: make no DDL changes of any kind to it or accept downtime while the full table rewrite was performed. To be as fair as possible, this is documented in the MySQL docs, but it is mind-blowing to me that any database team would release this behavior into production. In other words, in what world is the upgrade of date/time types to add a bit more fractional precision so important that all online DDL operations on that table will be silently, automatically, and unavoidably converted to offline operations in order to perform the upgrade? To me, this is indicative of the same mindset that released MySQL for so many years with the unsafe and silent downgrading of data as the default mode of operation.

* Dropping a table takes a global lock that prevents the execution of ANY QUERY until the underlying files for the table are removed from the filesystem. Under many circumstances, this would go unnoticed, but I experienced a 7-minute production outage when I dropped a 700GB table that was no longer used. Apparently, this delay is due to the time it takes the underlying Linux filesystems to delete the large table file. This was an RDS instance, so I had no visibility into the filesystem used and it was probably exacerbated by the EBS backing for the RDS instance, but still, what database takes out a GLOBAL LOCK TO DROP A TABLE? After the incident, I googled for and found this description of the problem (https://www.percona.com/blog/2009/06/16/slow-drop-table/) which isn't well-documented. It's almost as if you have to anticipate every possible way in which MySQL could screw you and then google for it if you want to avoid production downtime.

Their basic approach for handling time series data -- partition the inserts by time range -- is one that works for pretty much any relational database. If you can treat the data as immutable which is often the case with time series data, it gets even better. The fact that they have an out-of-the-box solution for it is nice, but I've implemented my own for an application in which a portion of the workload was time series data and a separate portion was not. It's all about mechanical sympathy and understanding the performance characteristics of your chosen datastore.

Yeah, apart from contractors who are between gigs, I can't imagine very many people who can work for a week as part of the hiring process. And even if many applicants could, I think it takes longer, sometimes quite a bit longer, to accurately assess the degree to which a developer will contribute to a engineering organization.

I'm not sure what you mean by "multi-process DB", but there are plenty of workloads in which SQLite under-performs non-MVCC databases. It is simply not designed for highly concurrent access patterns. In a previous job, I had to replace the persistence layer for a medium-load (100-500 events/sec) event tracking system from SQLite to SQL Server which is, by default, non-MVCC. The latter was considerably more complicated and considerably faster and more scalable than the former.

I say this not because SQLite is not good. It's _great_ for what it intends to do which is, as the linked page says, offer an excellent alternative to fopen(). It doesn't really offer an alternative to a full DBMS, though. There may be some gray area between a "heavy fopen()" workload and a "light DBMS" workload where there might be a legitimate case to be made for SQLite over a DBMS, but standard OLTP or OLAP workloads with any degree of concurrency are almost uncertainly outside of SQLite's intended use case.

There was a recent conversation on a similar topic with input from Richard Hipp, SQLite's author, here: https://news.ycombinator.com/item?id=15303662

I think skill differentials in programming is a contributing factor to the bimodal distribution. I think there's also another factor which is the degree to which one is an active "curator" of one's own career. I've known some devs who were quite talented but were perfectly content to stay at low-paying jobs where the value they generated for their companies for developing CRUD or other low-multiplier apps was moderate. Others who were much more deliberate about developing skills in areas of software where much higher multiples were possible and choosing jobs strategically to build their careers made MUCH more money than the former despite not necessarily being more technically skilled. These latter developers are much more likely to be the ones working at FAANGs (or whatever) for $200k+.

>>Random distribution seems like it would be better for write performance - it means that inserting multiple new values and rebuilding the index can happen in parallel rather than having to coordinate the auto increments. It also naturally has the right properties for sharding as and when you get to the point of needing that.<<

I'm not sure what you mean by "inserting new values and rebuilding the index can happen in parallel." In most storage engines using clustered indexes, writes that append to a b-tree are faster than writes that insert somewhere in the middle of a b-tree so there really isn't any rebuilding of the index required in the former case.

>>On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.<<

That depends entirely on the nature of your workload. There are plenty of common workloads such as sensor data or log entries for which the common read patterns are very much correlated with the order in which the rows were written.

The sequencing of those UUIDs resets every time Windows restarts. Given the frequency of OS and SQL Server updates that require reboots, that wasn't good enough for any of my workloads.

Making blanket generalizations in the title of an article is a terrible idea. Auto-increment integer IDs work for different use cases than UUIDs. This article does a decent job of describing the scenarios where UUIDs work well, but its section on when UUIDs are less optimal is a bit lacking. In particular, UUIDs are generally a bad choice of key for clustered or index-organized tables because their random insertion order results in fragmentation in the primary index. PostgreSQL doesn't have clustered indexes and perhaps that's why that drawback wasn't mentioned.

Given the existing architecture of Aurora, the independent scaling of CPU and storage seems pretty straightforward. What is much harder to scale up and (especially) down is a warmed-up buffer pool which is critical for consistent query performance. I wonder if that is what they mean in the article when they say that scaling happens on a "pool of 'warm' instances". If so, I'd be very interested in more details on how that works.

It would certainly be nice to disable just that one section. I took the GGP's comment to be about disabling the entire Google Now/Feed app.

FWIW, you can disable Google Now on your phone. On the more recent versions of Android, you long-press an empty spot on your home screen, select "Settings", and then locate and disable the "Your feed" item.

The article says, "The [new instance class] db.r4.16xlarge will give you additional write performance." Does anyone know why this is the case? I thought one of the distinguishing characteristics of Aurora was that the read and write performance of its proprietary storage layer was independent of both the instance class of your database server and the size of your database.

An article from Ars Technica [1] on this topic makes the following point:

"Unlike most Azure compute resources, which are typically shared between customers, the Cray supercomputers will be dedicated resources. This suggests that Microsoft won't be offering a way of timesharing or temporarily borrowing a supercomputer. Rather, it's a way for existing supercomputer users to colocate their systems with Azure to get the lowest latency, highest bandwidth connection to Azure's computing capabilities rather than having to have them on premises."

Somewhat interestingly, this sounds like a bit like hybrid cloud except that it's hosted entirely in Azure datacenters rather than partially on-premises.

[1] https://arstechnica.com/gadgets/2017/10/cray-supercomputers-...

A missing small feature here or there is kind of a lame reason to not take a large and well-respected product seriously. That said, bcp and PowerShell (piping the output of Invoke-Sqlcmd through Export-CSV) are among the more common ways that can be done with SQL Server.

That may be the case for other products, but in this case, they acquired the "Sybase SQL Server" product and simply renamed it "Microsoft SQL Server".

It's proprietary and therefore not an option for everyone, but SQL Server's "Filestream" feature [1] was designed for the "large blob of unstructured data" use case (small blobs of unstructured data generally work fine in regular tables). It stores large blob data efficiently in individual files directly on disk that are still managed by the database engine and written/read in the same transaction as table data. It's well-integrated with their other standard features such as backups, HA clusters, etc. It's a pretty impressive feature.

[1] https://technet.microsoft.com/en-us/library/bb933993(v=sql.1...

I'd consider HNES's highlighting of new comments to be nearly essential to reading HN. I don't understand how others can follow long discussions without any indication of what comments have been posted since a previous viewing or refresh. And it doesn't seem to me like it would be a difficult feature to add.

I use the "Chrome Store Foxified" extension which lets you install some Chrome plugins including HNES.

I don't see how pgsql could perform such optimizations as eliminating a sorting step when ordering results by column(s) in a clustered index if there is no guarantee that new or updated data will also be ordered by the clustered index. I agree that both models have their pros and cons. While I am a big fan of pgsql, I do also appreciate those databases that offer both heaps and clustered indexes that I can mix and match in my schema as best suits my workload.

That kind of clustering is a point-in-time operation. Subsequent inserts and updates aren't stored in clustered order. When the ordering is guaranteed, the query planner can be more aggressive in optimizing certain kinds of queries. A real clustered index could also be used as an implicit covering index as grzm mentions.

It's disappointing that pgsql has no index-organized tables aka clustered indexes. They are quite advantageous for a variety of workloads.

I have but I'd say that SQLite is not intended for use in that scenario. It's an in-process library for persisting data to disk in a generally relational format with a SQL interface. It starts to degrade under highly concurrent read and write workloads that you can experience in client-server applications. At that point, a typical RDBMS with more robust concurrency support starts to be a better choice. I experienced that when using SQLite and we eventually moved the application to a full RDBMS which was more complex but also performed and scaled much better.

Note that this is very much in line with their recommendations from the docs (http://sqlite.org/whentouse.html):

>>SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases. SQLite competes with fopen().<<

It's worth noting that when considered as competition to fopen(), SQLite is quite good.

I'd happily pay for an alternative to GV, too, especially since Google has shown little interest in GV and has a tendency to shut down services in which they've lost interest (RIP, Google Reader). Better support for MMS would be nice, too.

I largely agree with the above and I would say that 1000 reqs/sec is probably a decent threshold for considering when async IO is going to matter for performance. That said, the details of your particular workload may benefit from async IO at significantly lower levels. As an example, one message routing application on which I worked with a typical workload of ~100 reqs/sec increased its performance by about 5x when switched from a blocking thread per request model to async IO. The application typically maintained a larger number (500-1000) of open but usually idle network connections. With that particular workload and on that particular platform, the overhead of thread context switching became a significant factor at much fewer than 1000 reqs/sec. One hint that this was the case was relatively high percentage (30%+) of CPU time spent in kernel mode. Switching to async IO dropped kernel time to about 5% on this particular application.

Yes, although everything that I've read suggests that Motorola's patent portfolio was light on anything related to smartphones so it provides little in the way of a litigation deterrent for competitors of Google or Android OEMs.

Some speculate that the Motorola acquisition was for their patent portfolio rather than their product line. I don't know how much credence to give that theory. An argument in favor of it is how quickly the Motorola phone business (minus the patents) was sold off to Motorola and an argument against it is that Motorola's former patents don't seem to be especially valuable to Google.

Describing the production operation of MySQL as a minefield is an excellent analogy. I maintained a list of the mines I stepped on in the last job I had where MySQL was used and here are the two I encountered with the biggest blast radius:

* If you have pre-5.6.4 date/time columns in your table and perform any kind of ALTER TABLE statement on that table (even one that doesn't involve the date/time columns), it will TAKE A TABLE LOCK AND REWRITE YOUR ENTIRE TABLE to upgrade to the new date/time types. In other words, your carefully-crafted online DDL statement will become fully offline and blocking for the entirety of the operation. To add insult to injury, the full table upgrade was UNAVOIDABLE until 5.6.24 when an option (still defaulted to off!) was added to decline the automatic upgrade of date/time columns. If you couldn't upgrade to 5.6.24, you had two choices with any table with pre-5.6.4 types: make no DDL changes of any kind to it or accept downtime while the full table rewrite was performed. To be as fair as possible, this is documented in the MySQL docs, but it is mind-blowing to me that any database team would release this behavior into production. In other words, in what world is the upgrade of date/time types to add a bit more fractional precision so important that all online DDL operations on that table will be silently, automatically, and unavoidably converted to offline operations in order to perform the upgrade? To me, this is indicative of the same mindset that released MySQL for so many years with the unsafe and silent downgrading of data as the default mode of operation.

* Dropping a table takes a global lock that prevents the execution of ANY QUERY until the underlying files for the table are removed from the filesystem. Under many circumstances, this would go unnoticed, but I experienced a 7-minute production outage when I dropped a 700GB table that was no longer used. Apparently, this delay is due to the time it takes the underlying Linux filesystems to delete the large table file. This was an RDS instance, so I had no visibility into the filesystem used and it was probably exacerbated by the EBS backing for the RDS instance, but still, what database takes out a GLOBAL LOCK TO DROP A TABLE? After the incident, I googled for and found this description of the problem (https://www.percona.com/blog/2009/06/16/slow-drop-table/) which isn't well-documented. It's almost as if you have to anticipate every possible way in which MySQL could screw you and then google for it if you want to avoid production downtime.

There are others, too, but to this day, those two still raise my blood pressure when I think about them. In addition to MySQL, I've pushed SQL Server and PostgreSQL pretty hard in production environments and never encountered gotchas like that. Unlike MySQL, those teams appear to understand the priorities of people who run production databases and they make it very clear when there are big and potentially disrupting changes and they don't make those changes obligatory, automatic, and silent as MySQL did.

From the reports I've seen, the big vendors (HP, Dell, Lenovo, etc.) ship about 3x more servers than workstations. And that probably understates the degree to which Xeons end up in servers rather than workstations since HP, Dell, and Lenovo account for a significant percentage of the workstation market whereas there are a lot more vendors and custom-built hardware in the server/datacenter markets.

One of the commenters on the original article (https://medium.com/@tarthedark/did-a-custom-bench-between-ht...) changed a few lines of the C# to use some different ASP.NET facilities which more than doubled the reqs/sec and dropped the std dev of latency by 2/3. That provides some evidence that it wasn't really an apples-to-apples comparison.

Beyond that, a synthetic benchmark such as this one in which no real work is being done to service an HTTP request is unlikely to translate into real-world performance since the "real work" factors such as disk IO or database access are likely to dominate the HTTP request/response code paths. IMO, one of the places where C# shines is its thorough support for asynchronous I/O (of all kinds -- not just network) so that these kinds of services that do "real work" scale up efficiently on multi-core systems. I am also impressed with how fast Golang is, but you really have to compare the systems with real workloads over time periods sufficient to fully stress GC on their production platforms to see which will be faster.

Same here. I've found that all the other registry and policy methods eventually stopped working for me whereas disabling the update service has worked reliably for 9+ months.