4 ms·

At any stage, did you speak to a DBA? Those are some really weird choices you made along the way.

innagadadavida 9y ago
You new here? Reinventing the wheel and blogging about it has glory. Where is the glory in writing some SQL that just works? Moreover it makes the code base very readable and maintainable, making your job redundant.
- markpapadakis 9y ago
  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).
hendzen 9y ago
"We would DELETE from those tables every few hours because we were only interested in the last 2 weeks worth of rows, so the dataset size was constrained/bounded, so slow downs weren't a result of tables getting larger." Yeah, this workload is hard on any LSM store, unless compaction is carefully tuned so that the tombstones are cleaned up often enough. Most people would recommend using partitioning...
- markpapadakis 9y ago
  Yes, we 'd insert new rows every 30 minutes or so. Also, we did try partitioning one of the 3 tables (which made sense) into multiple tables -- it didn't improve the situation at all. Again, there are probably tunables and practices specific to MyRocks that we just didn't consider. We just didn't want to pursue this any further.
  - Shelnutt2 9y ago
    I believe hendzen was referring to using partition on a table in mysql. Mariadb has an example walkthrough on this: https://mariadb.com/kb/en/library/partition-maintenance/#partition-maintenance-for-the-time-series-case https://mariadb.com/kb/en/library/partition-maintenance/#par... . The only change that I'd recommend considering, is we use partition by list, instead of range, and keep a weeks worth of daily future partitions. This makes it so you don't have to reorganize the table. On the other hand it means if you get future data it can fail and if your partition jobs have issues for a week you can fail to load data all together. Using partitions for time series data help also due to partition pruning, https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning... .
    - markpapadakis 9y ago
      Thank you -- we didn't use table partitioning because we didn't have good results in the past, though we should probably have tried it, and maybe it'd have worked well. We just kind of gave up on mySQL use for that specific problem after failing (again, maybe its entirely because of our inability to use mySQL "properly") to get good results and moved on to something else.

You new here? Reinventing the wheel and blogging about it has glory. Where is the glory in writing some SQL that just works? Moreover it makes the code base very readable and maintainable, making your job redundant.

markpapadakis 9y ago
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).

hendzen 9y ago

"We would DELETE from those tables every few hours because we were only interested in the last 2 weeks worth of rows, so the dataset size was constrained/bounded, so slow downs weren't a result of tables getting larger." Yeah, this workload is hard on any LSM store, unless compaction is carefully tuned so that the tombstones are cleaned up often enough. Most people would recommend using partitioning...

markpapadakis 9y ago
Yes, we 'd insert new rows every 30 minutes or so. Also, we did try partitioning one of the 3 tables (which made sense) into multiple tables -- it didn't improve the situation at all. Again, there are probably tunables and practices specific to MyRocks that we just didn't consider. We just didn't want to pursue this any further.
- Shelnutt2 9y ago
  I believe hendzen was referring to using partition on a table in mysql. Mariadb has an example walkthrough on this: https://mariadb.com/kb/en/library/partition-maintenance/#partition-maintenance-for-the-time-series-case https://mariadb.com/kb/en/library/partition-maintenance/#par... . The only change that I'd recommend considering, is we use partition by list, instead of range, and keep a weeks worth of daily future partitions. This makes it so you don't have to reorganize the table. On the other hand it means if you get future data it can fail and if your partition jobs have issues for a week you can fail to load data all together. Using partitions for time series data help also due to partition pruning, https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning... .
  - markpapadakis 9y ago
    Thank you -- we didn't use table partitioning because we didn't have good results in the past, though we should probably have tried it, and maybe it'd have worked well. We just kind of gave up on mySQL use for that specific problem after failing (again, maybe its entirely because of our inability to use mySQL "properly") to get good results and moved on to something else.