4 ms·

> "We realized that the original daily table structure wasn’t ideal. If we needed to query something over several days or months, it was terrible — lots of UNIO

> "We realized that the original daily table structure wasn’t ideal. If we needed to query something over several days or months, it was terrible — lots of UNION ALLs or loops to crawl through days or even months at a time was slow." Seems to me rather storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it. Anybody can explain why SQL database makes sense for large time series data? Why not just dump it into linear files?

tutfbhuf 6y ago
> Why not just dump it into linear files? I guess because they want to query them, but in this case I would prefer the ELK stack (Elasticsearch + Logstash + Kibana).
- oneplane 6y ago
  But even then, normal files work great with (all non-Microsoft) software that is actually intended to work with this type of data at this scale. You could do it on one machine with Pandas DataFrames or on multiple machines with Spark DataFrames. You could keep warm logs in standard log files and put everything else in Parquet. You could use a parallel database. Heck, you could use any of the cloud offerings to do it. And you could use ElasticSearch. ElasticSearch would be fine for this.
  - Semaphor 6y ago
    > But even then, normal files work great with (all non-Microsoft) software Even Microsoft has a tool [0] that allows you to query IIS logfiles with SQL [0]: https://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx https://www.microsoft.com/technet/scriptcenter/tools/logpars...
  - Shog9 6y ago
    Curious what use-cases you're envisioning here. Having worked with the system described in the past, it's quite flexible: data can be easily pulled out into more specialized systems for aggregation or trend analysis, while the raw logs remain quickly accessible over long enough periods of time to allow for digging into everything from support cases involving a single person to monitoring distributed attacks. Access can be controlled and monitored with reasonable granularity, and training new folks to use it is as easy as teaching some basic Select queries. Not gonna suggest it's the most efficient system (if nothing else, SQLServer wastes a TON of disk space), but it minimizes complexity while deftly avoiding the choice between throwing away information when it's still needed and keeping everything in flat files which are too unwieldy to be used.
    - oneplane 6y ago
      It's not that the system itself isn't functional at all, but think about cost, flexibility (i.e. the migration as posted) and support. If you have to move that much data at once because your RDBMS doesn't work in any other way you're going to be in trouble, even if you get the 50k costing SSDs. All of the things SQL server does can be done with not-SQL-server things, but you suddenly gain the capability to take shards offline and do multi-version migration, multi-host migration, multi-storage-backend migration. You can query in SQL, but also GraphQL. You can use Lucene search, and you can use ES-specific queries. You can have multiple write hosts, you can have tiered storage on application-level, OS-level and SAN-level and they can actually work together. Again, it's not that SQL server doesn't do anything, it's just that it's probably not the best plan for time series access logs.
      - Shog9 6y ago
        I'm sure there are better options. OTOH... I was at SO for about 9 years, and the system described existed for all of it - predating GraphQL, predating even ElasticSearch. There's stuff built on top of it that was never envisioned when it was created, and stuff that wouldn't have been possible without it. At this point, moving to something else would likely be a far more costly investment just in terms of requirements analysis than the migration described in the blog post. Would it pay off? Maybe! But for a critical bit of infrastructure, that's something you put an awful lot of careful thought and research into before you venture to do anything... And meanwhile, that infrastructure has to be kept running.
        oneplane 6y ago
        One important facet that isn't much technology related at all is the knowledge pool you can work with. Every time a generic component is implemented non-standard you essentially end up with unusable knowledge for both existing people leaving for another job and new people coming in with the common knowledge. While most companies that are old enough to predate the iPhone usually have a lot of in-house technology that predates conventions and generic implementations, there is plenty of movement towards commonalty instead of keeping that special snowflake stuff in; at least at the places where I have/had influence. When you freeze an in-house tool in time you essentially rob your own people of progress. The solution isn't rip-and-replace, but a process of continuous improvement is good for everyone.
ThePadawan 6y ago
Because you've already paid the licensing cost for the SQL database and have salaried DBAs taking care of it. And building an alternative solution (including HA, backups etc.) would cost more than $0.
- PaywallBuster 6y ago
  > I fully expected the process to take months, and it did — after hitting delays in getting new servers the entire project took about 11 months. This doesn't sound like it cost $0
  - Klinky 6y ago
    At the time of the decision was made(4.5~ years ago) it probably was $0. Now it's worth the cost to migrate because the value of keeping that data has been proven worthwhile by the cheapest initial solution.
  - AlfeG 6y ago
    But the real cost is not 11 month. Db Dev were not 100% working in this project, just monitor from time to time that everything is ok.
abhishekjha 6y ago
>storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it I get this question asked a lot of times in the interview. Why is it a bad idea to store logs in a SQL database? It has the ACID properties baked in and it is pretty reliable. What is the better alternative? I guess I have never worked on anything that would require keeping track of 40TB of data because of which the drawbacks aren't really obvious to me.
- posix_me_less 6y ago
  Because everything gets substantially slower and more difficult, see 11 months of busy work motivated by queries being slow, described in TFA. SQL is great for querying data, but you don't need to store the whole history in a single database. ACID is a red herring. These are traffic logs, not some precious global state that has to handle ACID transactions. The better alternative is to dump the data into many big files sorted by time. When you need to do some SQL kungfu on a part of the data, store that part into SQL database.
  - hogFeast 6y ago
    Then use partitioning (which the OP appears to be doing)? If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? With SQL, your data is presumably going to be non-contiguous so you are going to get slower seeks on disk...but (I believe, although I am not 100% familiar) this can happen with noSQL if you add an index (which are often BTree...so the same as SQL under the hood) and you have things like SSTables/compaction potentially slowing performance too (although maybe not an issue in this application...I don't know). If you are doing some kind of full table scan though, there is no weird magic in noSQL that allows you to perform this faster afaik. You are reading from disk, that is your limit. The point, afaik, with noSQL for logging is that writes are faster so the throughput is larger...which can be required for this use case but probably isn't relevant for everyone.
    - pbronez 6y ago
      > If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? The real architectural benefit we got from noSQL (remember - "Not Only SQL") was dynamic horizontal scalability. Once you separate Compute and Storage then you can make choices based on your specific workload. > You are reading from disk, that is your limit. Separating Compute and Storage works around this. Instead of putting all your logs in a monolithic SQL database, stream them into object storage. When you want to run a query, you can complete it arbitrarily fast by spinning up more compute nodes. Now you're using Massively Parallel Processing and that disk limit stops mattering as much. Lots of systems do this... AWS EMR, Snowflake, Presto, etc etc So you get a much simpler storage system and faster queries. You may appear to pay more for storage in this approach, but that's mostly a cloud vs on prem accounting question.
BrentOzar 6y ago
> Anybody can explain why SQL database makes sense for large time series data? Let's say, just to say, that you have effectively free licensing for Microsoft SQL Server (because you already own the licenses in question), and you have staff who know that platform well. Sometimes it's easier to use the tools you already have rather than go acquire a new platform to achieve a goal. As with anytime you choose a persistence layer, you're accumulating technical debt the instant you make the choice. Over time, if better persistence layers become available, you have to make a judgment call about when it makes sense to change the code, versus when it makes sense to do maintenance work on the persistence layer.
- slg 6y ago
  >and you have staff who know that platform well. People often ignore how important this is, especially for a small team like Stack Overflow appears to have. A new platform requires training and/or a lack of productivity as employees learn the new system. It requires developer time to convert over any work designed to use the old system. It makes hiring more difficult because now you will either want someone with the knowledge of both your systems which greatly shrinks down your pool of potential hires or new employees will need to learn a new system on the job which increases the ramp time up for any hires. Maybe this doesn't fit the textbook definition of technical debt, but it is still a real and ongoing cost that comes with making a decision to switch tools.
  - gbrayut 6y ago
    Case in point, one of my favorite system architecture/design blog posts: Providence: Failure Is Always An Option https://jasonpunyon.com/blog/2015/02/12/providence-failure-is-always-an-option/ https://jasonpunyon.com/blog/2015/02/12/providence-failure-i... Which has one of my favorite quotes about scope creep: Kevin and I have essentially become allergic to big projects. We attempt to practice “What can get done by Friday?” driven development. Keeping things small precludes a whole class of errors like “We need a new datastore”, ‘cause that ain’t gettin’ done by Friday. It’s hard to sink a week on something you weren’t supposed to be working on when all you have is a week.
- 6y ago

tutfbhuf 6y ago

> Why not just dump it into linear files? I guess because they want to query them, but in this case I would prefer the ELK stack (Elasticsearch + Logstash + Kibana).

oneplane 6y ago
But even then, normal files work great with (all non-Microsoft) software that is actually intended to work with this type of data at this scale. You could do it on one machine with Pandas DataFrames or on multiple machines with Spark DataFrames. You could keep warm logs in standard log files and put everything else in Parquet. You could use a parallel database. Heck, you could use any of the cloud offerings to do it. And you could use ElasticSearch. ElasticSearch would be fine for this.
- Semaphor 6y ago
  > But even then, normal files work great with (all non-Microsoft) software Even Microsoft has a tool [0] that allows you to query IIS logfiles with SQL [0]: https://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx https://www.microsoft.com/technet/scriptcenter/tools/logpars...
- Shog9 6y ago
  Curious what use-cases you're envisioning here. Having worked with the system described in the past, it's quite flexible: data can be easily pulled out into more specialized systems for aggregation or trend analysis, while the raw logs remain quickly accessible over long enough periods of time to allow for digging into everything from support cases involving a single person to monitoring distributed attacks. Access can be controlled and monitored with reasonable granularity, and training new folks to use it is as easy as teaching some basic Select queries. Not gonna suggest it's the most efficient system (if nothing else, SQLServer wastes a TON of disk space), but it minimizes complexity while deftly avoiding the choice between throwing away information when it's still needed and keeping everything in flat files which are too unwieldy to be used.
  - oneplane 6y ago
    It's not that the system itself isn't functional at all, but think about cost, flexibility (i.e. the migration as posted) and support. If you have to move that much data at once because your RDBMS doesn't work in any other way you're going to be in trouble, even if you get the 50k costing SSDs. All of the things SQL server does can be done with not-SQL-server things, but you suddenly gain the capability to take shards offline and do multi-version migration, multi-host migration, multi-storage-backend migration. You can query in SQL, but also GraphQL. You can use Lucene search, and you can use ES-specific queries. You can have multiple write hosts, you can have tiered storage on application-level, OS-level and SAN-level and they can actually work together. Again, it's not that SQL server doesn't do anything, it's just that it's probably not the best plan for time series access logs.
    - Shog9 6y ago
      I'm sure there are better options. OTOH... I was at SO for about 9 years, and the system described existed for all of it - predating GraphQL, predating even ElasticSearch. There's stuff built on top of it that was never envisioned when it was created, and stuff that wouldn't have been possible without it. At this point, moving to something else would likely be a far more costly investment just in terms of requirements analysis than the migration described in the blog post. Would it pay off? Maybe! But for a critical bit of infrastructure, that's something you put an awful lot of careful thought and research into before you venture to do anything... And meanwhile, that infrastructure has to be kept running.
      - oneplane 6y ago
        One important facet that isn't much technology related at all is the knowledge pool you can work with. Every time a generic component is implemented non-standard you essentially end up with unusable knowledge for both existing people leaving for another job and new people coming in with the common knowledge. While most companies that are old enough to predate the iPhone usually have a lot of in-house technology that predates conventions and generic implementations, there is plenty of movement towards commonalty instead of keeping that special snowflake stuff in; at least at the places where I have/had influence. When you freeze an in-house tool in time you essentially rob your own people of progress. The solution isn't rip-and-replace, but a process of continuous improvement is good for everyone.

ThePadawan 6y ago

Because you've already paid the licensing cost for the SQL database and have salaried DBAs taking care of it. And building an alternative solution (including HA, backups etc.) would cost more than $0.

PaywallBuster 6y ago
> I fully expected the process to take months, and it did — after hitting delays in getting new servers the entire project took about 11 months. This doesn't sound like it cost $0
- Klinky 6y ago
  At the time of the decision was made(4.5~ years ago) it probably was $0. Now it's worth the cost to migrate because the value of keeping that data has been proven worthwhile by the cheapest initial solution.
- AlfeG 6y ago
  But the real cost is not 11 month. Db Dev were not 100% working in this project, just monitor from time to time that everything is ok.

abhishekjha 6y ago

>storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it I get this question asked a lot of times in the interview. Why is it a bad idea to store logs in a SQL database? It has the ACID properties baked in and it is pretty reliable. What is the better alternative? I guess I have never worked on anything that would require keeping track of 40TB of data because of which the drawbacks aren't really obvious to me.

posix_me_less 6y ago
Because everything gets substantially slower and more difficult, see 11 months of busy work motivated by queries being slow, described in TFA. SQL is great for querying data, but you don't need to store the whole history in a single database. ACID is a red herring. These are traffic logs, not some precious global state that has to handle ACID transactions. The better alternative is to dump the data into many big files sorted by time. When you need to do some SQL kungfu on a part of the data, store that part into SQL database.
- hogFeast 6y ago
  Then use partitioning (which the OP appears to be doing)? If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? With SQL, your data is presumably going to be non-contiguous so you are going to get slower seeks on disk...but (I believe, although I am not 100% familiar) this can happen with noSQL if you add an index (which are often BTree...so the same as SQL under the hood) and you have things like SSTables/compaction potentially slowing performance too (although maybe not an issue in this application...I don't know). If you are doing some kind of full table scan though, there is no weird magic in noSQL that allows you to perform this faster afaik. You are reading from disk, that is your limit. The point, afaik, with noSQL for logging is that writes are faster so the throughput is larger...which can be required for this use case but probably isn't relevant for everyone.
  - pbronez 6y ago
    > If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? The real architectural benefit we got from noSQL (remember - "Not Only SQL") was dynamic horizontal scalability. Once you separate Compute and Storage then you can make choices based on your specific workload. > You are reading from disk, that is your limit. Separating Compute and Storage works around this. Instead of putting all your logs in a monolithic SQL database, stream them into object storage. When you want to run a query, you can complete it arbitrarily fast by spinning up more compute nodes. Now you're using Massively Parallel Processing and that disk limit stops mattering as much. Lots of systems do this... AWS EMR, Snowflake, Presto, etc etc So you get a much simpler storage system and faster queries. You may appear to pay more for storage in this approach, but that's mostly a cloud vs on prem accounting question.

BrentOzar 6y ago

> Anybody can explain why SQL database makes sense for large time series data? Let's say, just to say, that you have effectively free licensing for Microsoft SQL Server (because you already own the licenses in question), and you have staff who know that platform well. Sometimes it's easier to use the tools you already have rather than go acquire a new platform to achieve a goal. As with anytime you choose a persistence layer, you're accumulating technical debt the instant you make the choice. Over time, if better persistence layers become available, you have to make a judgment call about when it makes sense to change the code, versus when it makes sense to do maintenance work on the persistence layer.

slg 6y ago
>and you have staff who know that platform well. People often ignore how important this is, especially for a small team like Stack Overflow appears to have. A new platform requires training and/or a lack of productivity as employees learn the new system. It requires developer time to convert over any work designed to use the old system. It makes hiring more difficult because now you will either want someone with the knowledge of both your systems which greatly shrinks down your pool of potential hires or new employees will need to learn a new system on the job which increases the ramp time up for any hires. Maybe this doesn't fit the textbook definition of technical debt, but it is still a real and ongoing cost that comes with making a decision to switch tools.
- gbrayut 6y ago
  Case in point, one of my favorite system architecture/design blog posts: Providence: Failure Is Always An Option https://jasonpunyon.com/blog/2015/02/12/providence-failure-is-always-an-option/ https://jasonpunyon.com/blog/2015/02/12/providence-failure-i... Which has one of my favorite quotes about scope creep: Kevin and I have essentially become allergic to big projects. We attempt to practice “What can get done by Friday?” driven development. Keeping things small precludes a whole class of errors like “We need a new datastore”, ‘cause that ain’t gettin’ done by Friday. It’s hard to sink a week on something you weren’t supposed to be working on when all you have is a week.
6y ago