3 ms·

Cloud stuff is never going to make sense, local machine it is. I do see what you're saying and here it seems like a good fit for a massive machine. No argument

Cloud stuff is never going to make sense, local machine it is. I do see what you're saying and here it seems like a good fit for a massive machine. No argument here. NB. "Trawling through 70 GB of web server log files" unless you partition your search you're going to be doing a lot of IO due to having only 64-GB of RAM. Chucking in extra mem to keep it all ram-resident will beat cores any day.

jiggawatts 4y ago
You would be... surprised. The query engine in Microsoft SQL Server is absurdly advanced, and simply beats the pants off just about everything else for complex, general-case queries. There are other engines that are better tuned for special cases, but if you've got "stuff" that's within the capacity of a single box, it's really hard to beat MSSQL. Let's expand on my example above: Clustered ColumnStore.[1] It's basically PowerBI or Vertica embedded within the traditional MSSQL engine. This extension enables the ability to have a per-column (vertical) on-disk storage format instead of the traditional row-store format most databases have. You can have it as a secondary acceleration index, or as a clustered (primary) index. In the latter case, it'll compress your raw table data. For my log files it was 33-to-1 compression (3% of the original file size). Second, even if the data doesn't fit into memory, it'll utilise the columnar storage to simply ignore (skip) columns it doesn't need for the query. I was computing performance statistics, so it was just reading in some numbers instead of the text. A further 100-to-1 optimisation can be gained here. It then parallelises this across all cores/hyperthreads, so I get 100% utilisation on 16 cores. All of this I/O is fully asynchronous with a huge queue depth. Expect to see 200K IOPS on a laptop, easily. Once in memory, it uses Intel AVX SIMD instructions to chew through the data 8 rows per clock tick per core. With 8 cores going at once, this is 64 rows per clock, or up to 234 billion rows processed per second. What's neat about it is that it's fully general: Columnstore can be mixed -- in a single query -- with in-memory tables, on-disk tables, traditional row-store, and external tables streamed in over the network. The engine will just... "figure this out". It'll build indexes on the fly if it has to, ideally in memory, but failing that... it'll stream disk-to-disk as required. I've seen "sort spills" reach 600K IOPS on my laptop. If I have to scan through years of logs and be able to join it against additional tables for "enriching" it, then it's hard to beat. Sure, there's a scale where the are dedicated log analytics databases, but they have their quirks, limitations, and costs. I take your incredulity that others can fully utilise their computers as a sign that general knowledge of just what is possible is poor. People have pointed out that some simple shell scripts can outperform Hadoop clusters up to a surprising scale. Similarly, knowledge of the capabilities of traditional database engines like MSSQL is oddly lacking, even amongst developers. Not to mention more esoteric ways of making your computer do your bidding, such as Mathematica, Julia, GPU codes, or whatever. Your computer is a power tool for your squishy, manual-labour meat brain! A lever for the mind. Learn to utilise it better and you'll be better at thinking. [1] https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/in...
- zasdffaa 4y ago
  I've not used columnstore in MSSQL but in fact I likely know more about the innards of MSSQL than you. WRT memory, all I mant was that if your working set doesn't fit in RAM, you're screwed regardless. Nothing will save you, so check that first. I don't know what your 70GB means in this context - please check. > It then parallelises this across all cores/hyperthreads, so I get 100% utilisation on 16 cores. that suggests your working set is indeed smaller than ram. Good. However be aware that the parallelise feature of mssql is much better that it was but is still moderately crap. Disable it and see if you get a 16X slowdown. My guess is you won't, which means when enabled you won't be getting a 16X speedup (also hyperthreading can actually slow things down - disbale it and see what happens). Edit: I'd not be surprised if you were only getting 4X to 5X speedup (I may be wrong but I do have experience in this). > With 8 cores going at once, this is 64 rows per clock, or up to 234 billion rows processed per second. (giggle) Oh, I don't think so! > I've seen "sort spills" reach 600K IOPS on my laptop. Then you're hitting IO and yer cores are useless. It spills to disk because it's overflowed ram - you may have a problem. > Learn to utilise it better and you'll be better at thinking. ... agreed, but the reverse is true also. edit 2: Depending on what you're looking for, and if you do it often, check if MSSQL's free-text indexing can index it, if so you may get a far bigger speedup from that than anything else. Maybe. Depending.
  - jiggawatts 4y ago
    I do know a thing or two about MS SQL optimisation also. 8) So keep in mind that the SSD I'm referring to can read at 7 GB/s. Not seven gigabits... 7 gigabytes per second. I've benchmarked some not-so-old servers (the entire box, not a VM!) as capable of only 35 GB/s streaming reads from main memory! So these days "disk" is more like "5x slower memory". It used to be 100-1000x slower. I told one customer to get a 16x PCIe gen 4 adapter card with 4 NVMe M.2 ports on it. This would -- in theory -- be able to pump out 28 GB/s of reads, equivalent to a 200 Gbps NIC. Total storage would be 32 TB with CoTS 8TB drives. That's a mind-blowing amount of IO in a single box! The hardware has the capability, it's the software and inability to utilise that is the bottleneck now..
    - 4y ago

jiggawatts 4y ago

You would be... surprised. The query engine in Microsoft SQL Server is absurdly advanced, and simply beats the pants off just about everything else for complex, general-case queries. There are other engines that are better tuned for special cases, but if you've got "stuff" that's within the capacity of a single box, it's really hard to beat MSSQL. Let's expand on my example above: Clustered ColumnStore.[1] It's basically PowerBI or Vertica embedded within the traditional MSSQL engine. This extension enables the ability to have a per-column (vertical) on-disk storage format instead of the traditional row-store format most databases have. You can have it as a secondary acceleration index, or as a clustered (primary) index. In the latter case, it'll compress your raw table data. For my log files it was 33-to-1 compression (3% of the original file size). Second, even if the data doesn't fit into memory, it'll utilise the columnar storage to simply ignore (skip) columns it doesn't need for the query. I was computing performance statistics, so it was just reading in some numbers instead of the text. A further 100-to-1 optimisation can be gained here. It then parallelises this across all cores/hyperthreads, so I get 100% utilisation on 16 cores. All of this I/O is fully asynchronous with a huge queue depth. Expect to see 200K IOPS on a laptop, easily. Once in memory, it uses Intel AVX SIMD instructions to chew through the data 8 rows per clock tick per core. With 8 cores going at once, this is 64 rows per clock, or up to 234 billion rows processed per second. What's neat about it is that it's fully general: Columnstore can be mixed -- in a single query -- with in-memory tables, on-disk tables, traditional row-store, and external tables streamed in over the network. The engine will just... "figure this out". It'll build indexes on the fly if it has to, ideally in memory, but failing that... it'll stream disk-to-disk as required. I've seen "sort spills" reach 600K IOPS on my laptop. If I have to scan through years of logs and be able to join it against additional tables for "enriching" it, then it's hard to beat. Sure, there's a scale where the are dedicated log analytics databases, but they have their quirks, limitations, and costs. I take your incredulity that others can fully utilise their computers as a sign that general knowledge of just what is possible is poor. People have pointed out that some simple shell scripts can outperform Hadoop clusters up to a surprising scale. Similarly, knowledge of the capabilities of traditional database engines like MSSQL is oddly lacking, even amongst developers. Not to mention more esoteric ways of making your computer do your bidding, such as Mathematica, Julia, GPU codes, or whatever. Your computer is a power tool for your squishy, manual-labour meat brain! A lever for the mind. Learn to utilise it better and you'll be better at thinking. [1] https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/in...

zasdffaa 4y ago
I've not used columnstore in MSSQL but in fact I likely know more about the innards of MSSQL than you. WRT memory, all I mant was that if your working set doesn't fit in RAM, you're screwed regardless. Nothing will save you, so check that first. I don't know what your 70GB means in this context - please check. > It then parallelises this across all cores/hyperthreads, so I get 100% utilisation on 16 cores. that suggests your working set is indeed smaller than ram. Good. However be aware that the parallelise feature of mssql is much better that it was but is still moderately crap. Disable it and see if you get a 16X slowdown. My guess is you won't, which means when enabled you won't be getting a 16X speedup (also hyperthreading can actually slow things down - disbale it and see what happens). Edit: I'd not be surprised if you were only getting 4X to 5X speedup (I may be wrong but I do have experience in this). > With 8 cores going at once, this is 64 rows per clock, or up to 234 billion rows processed per second. (giggle) Oh, I don't think so! > I've seen "sort spills" reach 600K IOPS on my laptop. Then you're hitting IO and yer cores are useless. It spills to disk because it's overflowed ram - you may have a problem. > Learn to utilise it better and you'll be better at thinking. ... agreed, but the reverse is true also. edit 2: Depending on what you're looking for, and if you do it often, check if MSSQL's free-text indexing can index it, if so you may get a far bigger speedup from that than anything else. Maybe. Depending.
- jiggawatts 4y ago
  I do know a thing or two about MS SQL optimisation also. 8) So keep in mind that the SSD I'm referring to can read at 7 GB/s. Not seven gigabits... 7 gigabytes per second. I've benchmarked some not-so-old servers (the entire box, not a VM!) as capable of only 35 GB/s streaming reads from main memory! So these days "disk" is more like "5x slower memory". It used to be 100-1000x slower. I told one customer to get a 16x PCIe gen 4 adapter card with 4 NVMe M.2 ports on it. This would -- in theory -- be able to pump out 28 GB/s of reads, equivalent to a 200 Gbps NIC. Total storage would be 32 TB with CoTS 8TB drives. That's a mind-blowing amount of IO in a single box! The hardware has the capability, it's the software and inability to utilise that is the bottleneck now..
  - 4y ago