3 ms·
As said in another comment, I just ran this: select day,count() as c from conn group by day order by c desc limit 1 and that resulted in 1 rows in set. E
by justinsaccount 9y ago
As said in another comment, I just ran this:
select day,count() as c from conn group by day order by c desc limit 1
and that resulted in
1 rows in set. Elapsed: 16.412 sec. Processed 1.43 billion rows, 2.87 GB (87.33 million rows/s., 174.65 MB/s.)
Can ElasticSearch do that?
- jdc0589 9y ago> Can ElasticSearch do that? yes. the required infrastructure will look different though, and obviously elasticsearch doesn't speak sql (not without some crazy 3rd party plugin anyway)
- justinsaccount 9y agoHow? How does one report on 1.43 billion events stored in ES in 16 seconds? And can it do this on a vm with 4G of ram?
- jdc0589 9y agoyou didn't specify anything about RAM/resources...
- justinsaccount 9y agoI did above: > I've been testing https://clickhouse.yandex/ https://clickhouse.yandex/. I threw it on a single VM with 4G of ram and imported billions of flow records into it. queries rip through data at tens of millions of records a second.
- chupasaurus 9y agoDid unrelevant and crazy testing on 4GB DigitalOcean VM with a part of a latest hosts dataset from Rapid7's Project Sonar. Data is pairs of ip,certificate thumbprint. On ~1500000 of entries in ES (~300mb with indices, much wow) a sort for ip occurancies is made on the speed of light within 3.5 seconds if data isn't cached and 300-350 if it is.
- Aeolun 9y ago1.5 million entries isn't exactly equal to 1.5 billion. Extrapolating the query would take almost an hour on ES.
- chupasaurus 9y agoThing is that the ES index which could fit in memory still performs slowly for full db query.