Thank you Chris for looking at my issue in such detail.
Yes, the parallel feature rocks.
Regards,
Thomas Güttler
Am 19.08.2016 um 22:40 schrieb Chris Mair:
> On 19/08/16 10:57, Thomas Güttler wrote:
>
>
>>>> What do you think?
>>>
>>> I store most of my logs in flat textfiles syslog style, and use grep for adhoc querying.
>>>
>>> 200K rows/day, thats 1.4 million/week, 6 million/month, pretty soon you're talking big tables.
>>>
>>> in fact thats several rows/second on a 24/7 basis
>>
>> There is no need to store them more then 6 weeks in my current use case.
>
>
> Hi,
>
> to me this kind of data looks like something Postgres can handle with ease.
>
> We're talking about 8.4M rows here.
>
> Coincidentally, I was trying out the new parallel query feature in the
> 9.6 beta just now and decided to use your numbers as a test case :)
>
> I can create 8.4M records having a timestamp and a random ~ 250 character string
> in ~ 31 seconds:
>
> pg96=# select now() + (sec / 200000.0 * 86400.0 || ' seconds')::interval as ts,
> pg96-# repeat(random()::text, 15) as msg
> pg96-# into t1
> pg96-# from generate_series(1, 6 * 7 * 200000) as sec;
> SELECT 8400000
> Time: 30858.274 ms
>
> Table size is 2.4 GB.
>
> This gives about 6 weeks. A query to scan the whole thing on the narrow column
> takes ~ 400 msec, like this:
>
> pg96=# select min(ts), max(ts) from t1;
> min | max
> -------------------------------+-------------------------------
> 2016-08-19 20:17:24.921333+00 | 2016-09-30 20:17:24.489333+00
> (1 row)
>
> Time: 409.468 ms
>
> Even running an unanchored regular expression (!) on the wider column is doable:
>
> pg96=# select count(*) from t1 where msg ~ '12345';
> count
> -------
> 955
> (1 row)
>
> Time: 3146.838 ms
>
> If you have some filter, not everything needs to be regexped and this gets pretty fast:
>
> pg96=# select count(*) from t1 where ts between '2016-08-25' and '2016-08-26' and msg ~ '12345';
> count
> -------
> 24
> (1 row)
>
> Time: 391.577 ms
>
> All this is without indices. Your data is more structured than my test, so undoubtly you will
> get some gain from indices...
>
> Here is something more analytical - basically same as the count(*) above:
>
> pg96=# select ts::date, count(*) from t1 where msg ~ '12345' group by ts::date order by ts::date;
> ts | count
> ------------+-------
> 2016-08-19 | 26
> 2016-08-20 | 28
> [...]
> 2016-09-28 | 21
> 2016-09-29 | 33
> (42 rows)
>
> Time: 3157.010 ms
>
> Note, however, that I'm using 9.6 beta with the parallel query feature: the sequential scans with the regexp is
> run in parallel on 6 workers... this gives me a speed-up of a factor 4-5 (machine has 8 logical CPUs) and
> the whole table fits in cache. For a use case as this, the parallel query feature in 9.6 is so good it's almost
> like cheating ;)
>
> Bye,
> Chris.
>
>
>
>
>
--
Thomas Guettler http://www.thomas-guettler.de/
