4 ms·

I suspected that folks would think this is about rolling your own thing as opposed to relying on existing solutions, and I thought I was clear what I said was n

I suspected that folks would think this is about rolling your own thing as opposed to relying on existing solutions, and I thought I was clear what I said was not about that — I just thought it would be somewhat valuable to someone how MyRocks compared to InnoDB and MyISAM for our use case. That’s just one datapoint. Doesn’t mean others will have a similar experience(in terms of performance and scalability) with us. Obviously. YMMV.

dbattaglia 9y ago
I think you need to just expect and brace for a certain amount of rage on HN if you say that you did something outside the norm. It’s unfortunate that people with literally 1 or 2 sentences of context can be so judgmental and completely miss the point.
- Twirrim 9y ago
  Note: in my original comment I rather deliberately did not call them the wrong choices, just weird. Hence asking if they'd engaged a DBA. They look like the kinds of choices that someone would make if they were just trying random things.
Twirrim 9y ago
You could build your stack out as a full LAMP setup, all on a single box. As things get a little busy you could spin up a second server, running all the software, adding master<->master replication between the databases and split the load between them. Then you could add a third, or even a fourth, creating a nice database replication loop between them (1->2->3->4->1) to ensure all db instances remain in sync. It would work. You could probably get a nice shiny blog post out of it about how easy it is to horizontally scale a web application. After all, you didn't even need to deal with all that hassle of splitting up writes and reads off to master and slave servers as appropriate. "We easily scaled our application horizontally, you could too!" Eventually you're going to run in to all sorts of hell with trying to keep the databases in sync. As your write load increases, the fleet won't be able to keep up, and everything on it will start fighting for IO. Approaching horizontal replication like that is such a horrible idea. Alternative take, sadly from personal experience: At one job, I got asked to help out a customer who just couldn't get the performance they needed out of their database. They wanted me to help migrate them to a larger server, which I did. Not long afterwards they came back asking for help tuning their database, because it was still slow as molasses. Turns out it was a site trying to be like a yellow pages. Their central bottleneck came from a single field in a single table that detailed the businesses. That field was "categories", a nice FULL TEXT field. Every category had a four letter short code. They wanted to have businesses exist in multiple categories, and so what they'd do was have an alphabetically sorted list of short codes, semicolon separated, e.g. "DOCT;DENT;MEDI" (for a business that offered Doctor and Dentist services). When you went to look at the medical table, the query would do something roughly of the form "SELECT * FROM businesses WHERE categories LIKE '%MEDI;%';". This would have been about 2009. There was no way to index off that field, every query would have to do a full table scan of that column to find out every relevant business. It worked, and when just a small handful of people were using the site at the same time, everything was A-OK as the server was really powerful and brute force was viable. Add any more users and the thing would fall to its knees. It wasn't that MySQL was the problem, it wasn't, it was that they were using it wrong. Switching engines wouldn't have fixed the fundamental flaw in the schema. I even showed them how they could solve all of their problems with a relatively simple schema change, but they wouldn't change things. They did spend a lot of time ranting about how it was MySQL's problem and "How can Google manage it but MySQL can't", when it really, absolutely and truly wasn't MySQL's fault. From the company perspective, everything was mostly great. They were paying for database servers far bigger than they needed, and paying for my time to read their rants, and have my advice ignored. You can lead a horse to the water, but you can't make it drink, I guess? Just because you can use a tool one way, doesn't mean it's the right way to use it. From the perspective of your average jack-of-all-trades sysadmin, who has had to dabble in DBA work from time to time, here's what seemed really strange to me: InnoDB -> MyISAM. That was a really, really strange choice. When you mention that at the outset as a change made, that's the kind of choice that rings alarm bells in my head and makes me think "They really don't know what they're doing". MyISAM is an older technology than InnoDB and has a large number of drawbacks to it. A few key ones: * MyISAM writes lock up the entire table, vs InnoDB row level locking. The whole table becomes read only until the write is finished. You can only have one write or update happening at a time, forcing you in to effective serialisation for all writes. That introduces a nasty scaling limitation. * MyISAM doesn't support transactions. It doesn't barf when it sees transaction related instructions, it just ignores them. * MyISAM's crash recovery is next to negligible. InnoDB has transaction logs around and the like that help it to recover from a crash gracefully without data loss, along with a host of better approaches to data storage on disk. * Development on it virtually ceased a long time ago. Lots of effort around query optimisations for modern architecture, multi-processing etc. etc. have gone in to InnoDB etc. and not in to MyISAM. The only advantage MyISAM had over InnoDB for a while was lack of FULL TEXT column support in InnoDB, but that was added in version 5.6, which went RC about 5 years ago or so. I can't imagine a single person who knows anything about MySQL considering that change. You also indicated that the engines start out fine, but performance dropped over time, and that you were deleting data older than a certain length of time. That makes me think a few things: 1) Your tables were getting badly fragmented. By constantly deleting data older than a certain age, you were forcing reads and writes to be all over the place. The impact is worse if you're not using partitioned tables. Which leads into.. 2) Not using MySQL native table partitioning. This introduces some major advantages with queries, allowing more parallelisation of various actions underneath it. It also has the advantage of limiting the scope of any operation, particularly index updates (as I understand it, index updates on a partitioned table only end up modifying the index for a single partition, rather than having to modify an index for the entire table).
- markpapadakis 9y ago
  Re: myISAM qualities: 1. Writes are infrequent; every 30 minutes or so we insert/update rows. Requests rate is very flow. This is for a specific report -- and said report was infrequently requested. Only updated by a single producer/process. All that means that locking wasn't a concern for us. 2. We didn't need transactions - if the producer would fail while it was executing the REPLACE statements, we 'd start it over and it wouldn't be a problem (idempotency) 3. We didn't care for crash recovery either -- if aything would go wrong, we 'd rebuild those tables (we only cared for 2 weeks or so worth of rows, rebuilding them wouldn't take long). I think you ignore that, despite MyISAM's deficiencies, it's really fast if you don't care for the aforementioned properties/warranties provided by more modern engines. And it was -- for our dataset, it was almost twice as fast as InnoDB. We have been running mySQL in production since release 3.x; we moved to it from mSQL. It may not mean much, but we know it mySQL well, at least some of our folks do. As I said in another reply, we didn't use native table partitioning because we didn't get the expected benefits in a different use-case/dataset, but we certainly should have considered it. Thank you for the suggestions though :)

dbattaglia 9y ago

I think you need to just expect and brace for a certain amount of rage on HN if you say that you did something outside the norm. It’s unfortunate that people with literally 1 or 2 sentences of context can be so judgmental and completely miss the point.

Twirrim 9y ago
Note: in my original comment I rather deliberately did not call them the wrong choices, just weird. Hence asking if they'd engaged a DBA. They look like the kinds of choices that someone would make if they were just trying random things.

Twirrim 9y ago

You could build your stack out as a full LAMP setup, all on a single box. As things get a little busy you could spin up a second server, running all the software, adding master<->master replication between the databases and split the load between them. Then you could add a third, or even a fourth, creating a nice database replication loop between them (1->2->3->4->1) to ensure all db instances remain in sync. It would work. You could probably get a nice shiny blog post out of it about how easy it is to horizontally scale a web application. After all, you didn't even need to deal with all that hassle of splitting up writes and reads off to master and slave servers as appropriate. "We easily scaled our application horizontally, you could too!" Eventually you're going to run in to all sorts of hell with trying to keep the databases in sync. As your write load increases, the fleet won't be able to keep up, and everything on it will start fighting for IO. Approaching horizontal replication like that is such a horrible idea. Alternative take, sadly from personal experience: At one job, I got asked to help out a customer who just couldn't get the performance they needed out of their database. They wanted me to help migrate them to a larger server, which I did. Not long afterwards they came back asking for help tuning their database, because it was still slow as molasses. Turns out it was a site trying to be like a yellow pages. Their central bottleneck came from a single field in a single table that detailed the businesses. That field was "categories", a nice FULL TEXT field. Every category had a four letter short code. They wanted to have businesses exist in multiple categories, and so what they'd do was have an alphabetically sorted list of short codes, semicolon separated, e.g. "DOCT;DENT;MEDI" (for a business that offered Doctor and Dentist services). When you went to look at the medical table, the query would do something roughly of the form "SELECT * FROM businesses WHERE categories LIKE '%MEDI;%';". This would have been about 2009. There was no way to index off that field, every query would have to do a full table scan of that column to find out every relevant business. It worked, and when just a small handful of people were using the site at the same time, everything was A-OK as the server was really powerful and brute force was viable. Add any more users and the thing would fall to its knees. It wasn't that MySQL was the problem, it wasn't, it was that they were using it wrong. Switching engines wouldn't have fixed the fundamental flaw in the schema. I even showed them how they could solve all of their problems with a relatively simple schema change, but they wouldn't change things. They did spend a lot of time ranting about how it was MySQL's problem and "How can Google manage it but MySQL can't", when it really, absolutely and truly wasn't MySQL's fault. From the company perspective, everything was mostly great. They were paying for database servers far bigger than they needed, and paying for my time to read their rants, and have my advice ignored. You can lead a horse to the water, but you can't make it drink, I guess? Just because you can use a tool one way, doesn't mean it's the right way to use it. From the perspective of your average jack-of-all-trades sysadmin, who has had to dabble in DBA work from time to time, here's what seemed really strange to me: InnoDB -> MyISAM. That was a really, really strange choice. When you mention that at the outset as a change made, that's the kind of choice that rings alarm bells in my head and makes me think "They really don't know what they're doing". MyISAM is an older technology than InnoDB and has a large number of drawbacks to it. A few key ones: * MyISAM writes lock up the entire table, vs InnoDB row level locking. The whole table becomes read only until the write is finished. You can only have one write or update happening at a time, forcing you in to effective serialisation for all writes. That introduces a nasty scaling limitation. * MyISAM doesn't support transactions. It doesn't barf when it sees transaction related instructions, it just ignores them. * MyISAM's crash recovery is next to negligible. InnoDB has transaction logs around and the like that help it to recover from a crash gracefully without data loss, along with a host of better approaches to data storage on disk. * Development on it virtually ceased a long time ago. Lots of effort around query optimisations for modern architecture, multi-processing etc. etc. have gone in to InnoDB etc. and not in to MyISAM. The only advantage MyISAM had over InnoDB for a while was lack of FULL TEXT column support in InnoDB, but that was added in version 5.6, which went RC about 5 years ago or so. I can't imagine a single person who knows anything about MySQL considering that change. You also indicated that the engines start out fine, but performance dropped over time, and that you were deleting data older than a certain length of time. That makes me think a few things: 1) Your tables were getting badly fragmented. By constantly deleting data older than a certain age, you were forcing reads and writes to be all over the place. The impact is worse if you're not using partitioned tables. Which leads into.. 2) Not using MySQL native table partitioning. This introduces some major advantages with queries, allowing more parallelisation of various actions underneath it. It also has the advantage of limiting the scope of any operation, particularly index updates (as I understand it, index updates on a partitioned table only end up modifying the index for a single partition, rather than having to modify an index for the entire table).

markpapadakis 9y ago
Re: myISAM qualities: 1. Writes are infrequent; every 30 minutes or so we insert/update rows. Requests rate is very flow. This is for a specific report -- and said report was infrequently requested. Only updated by a single producer/process. All that means that locking wasn't a concern for us. 2. We didn't need transactions - if the producer would fail while it was executing the REPLACE statements, we 'd start it over and it wouldn't be a problem (idempotency) 3. We didn't care for crash recovery either -- if aything would go wrong, we 'd rebuild those tables (we only cared for 2 weeks or so worth of rows, rebuilding them wouldn't take long). I think you ignore that, despite MyISAM's deficiencies, it's really fast if you don't care for the aforementioned properties/warranties provided by more modern engines. And it was -- for our dataset, it was almost twice as fast as InnoDB. We have been running mySQL in production since release 3.x; we moved to it from mSQL. It may not mean much, but we know it mySQL well, at least some of our folks do. As I said in another reply, we didn't use native table partitioning because we didn't get the expected benefits in a different use-case/dataset, but we certainly should have considered it. Thank you for the suggestions though :)