3 ms·

>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

>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.
jennyyang 6y ago
ACID properties are not useful here. Unless every single piece of data is required, which I highly doubt, then ensuring that every single log is written correctly doesn't matter. These are traffic logs, so if the server or application is down, how do these logs get written? I'm assuming that they don't. Have these databases never had outages as well, because if they did then the traffic would be lost as well. I would wager a guess that there are probably lots of gaps in their data that they just don't know about or don't care about. So given that not every single log is required, then you can step back from ACID requirements and find a solution that is easier to maintain. Taking 11 months to migrate a database is not reasonable for most projects.

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.

jennyyang 6y ago

ACID properties are not useful here. Unless every single piece of data is required, which I highly doubt, then ensuring that every single log is written correctly doesn't matter. These are traffic logs, so if the server or application is down, how do these logs get written? I'm assuming that they don't. Have these databases never had outages as well, because if they did then the traffic would be lost as well. I would wager a guess that there are probably lots of gaps in their data that they just don't know about or don't care about. So given that not every single log is required, then you can step back from ACID requirements and find a solution that is easier to maintain. Taking 11 months to migrate a database is not reasonable for most projects.