Re: Queries containing ORDER BY and LIMIT started to work slowly

[Rick Otten]

On Tue, Aug 29, 2023 at 3:57 PM Rondat Flyag  wrote:

> I took the dump just to store it on another storage (external HDD). I
> didn't do anything with it.
>
> 29.08.2023, 21:42, "Jeff Janes" :
>
>
>
> On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag 
> wrote:
>
> I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`.
> Everything was fine several days ago even with standard Postgresql
> settings. I dumped a database with the compression option (maximum
> compression level -Z 9) in order to have a smaller size (`pg_dump
> --compress=9 database_name > database_name.sql`). After that I got a lot of
> problems.
>
>
> You describe taking a dump of the database, but don't describe doing
> anything with it.  Did you replace your system with one restored from that
> dump?  If so, did vacuum and analyze afterwards?
>
> Cheers,
>
> Jeff
>
>
Since this is a very old system and backups are fairly I/O intensive, it is
possible you have a disk going bad?  Sometimes after doing a bunch of I/O
on an old disk, it will accelerate its decline.  You could be about to lose
it altogether.

Re: Huge Tables

[Rick Otten]

300M rows isn't "huge", but it is starting to get to be real data.

Some notes/very general rules of thumb since you asked a very general
question:
1.  Consider updating the statistics on the table from the default sample
of 100 rows to something larger - especially if you have a wide variety of
data. (either set on a per-table basis or set globally on your database
with the `default_statistics_target` parameter.
2.  Consider the `create statistics` command to see if there any other
additional hints you can give the planner to help figure out if columns are
related.
3.  If you partition:
a.  Your queries could be _slower_ if they don't include the partition
criteria.  So partition on something you are likely to almost always want
to filter on anyhow.  That way you can take advantage of "partition
pruning".
b.  One of the main advantages of partitioning is to be able to archive
old data easily - either by moving it to other tables, dropping it, or
doing other things with it.  Think about whether you ever intend to roll
out old data and figure out ways partitions might make that easier.
4.  Consider tweaking `max_parallel_workers` to enable more concurrency if
you are running a lot of big queries on your larger table.
a.  There are a number of other `*parallel*` parameters you can study
and tune as well.
5.  Consider bumping `work_mem` if you are running queries that are doing a
lot of sorting and other intermediary work on the larger data sets.
6.  For a table with only 300M rows, btree is going to be fine for most use
cases.  If you have a monotonically increasing/decreasing column you may be
able to use a BRIN index on it to save a little space and make for slightly
more efficient query.
7.  You may want to tweak the vacuum parameters to be able to use a little
more memory and more parallel processing.  Since autovacuums are triggered
by a percentage of change in the table, you may want to lower the
percentage of rows that trigger the vacuums.

You'll need to get a lot more specific about the issues you are running
into for us to be able to provide more specific recommendations


On Sat, Mar 11, 2023 at 6:48 AM André Rodrigues  wrote:

> Hello Guys
>
> Regarding a particular performance + architecture situation with postgres
> 12, I have a table with 300 millions rows and then I ask you, which basic
> approach like *parameters in postgres.conf*, suitable index type ,
> partitions type,  would you suggest me knowing that we have Queries  using
> bind with range id  ( primary Key )  +  1 or 2 columns ?
>
>
> Best regards
> Andre
>
>
>

max_wal_senders

[Rick Otten]

I've been thinking about the max_wal_senders parameter lately and wondering
if there is any harm in setting it too high.  I'm wondering if I should try
to shave a few senders off, perhaps to match my logical replicas + 1,
instead of just leaving it at the default of 10.  Or vice-versa, can
clients use more than one sender if they are available?  Would increasing
it result in lower latency?  The documentation is a little vague.

The documentation mentions an orphaned connection slot that may take a
while to time out.  How can I tell if I have any of those?  I was looking
for a `pg_wal_slots` table similar to the `pg_replication_slots` table, but
don't see anything obvious in the catalog.

Fwd: Database Stalls

[Rick Otten]

On Mon, Jan 30, 2023 at 4:32 PM Mok  wrote:

> Hi,
>
> Unfortunately there is no pg_stat_activity data available as we are
> unaware of the issue until it has already happened.
>
> The version we are on is 12.11.
>
> I don't think it is due to locks as there are none in the logs. Vacuums
> are logged also and none occur before or after this event. Checkpoint
> timeout is set to 1 hour and these events do not coincide with checkpoints.
>
> Gurmokh
>
>>
>>
Have you eliminated network issues?  I have seen what looks like a database
stalling to end up actually being the network packets taking a side trip to
halfway around the world for a while.  Or DNS lookups suddenly taking a
really long time.

The next most likely thing is disk i/o.  Do you have huge corresponding
disk i/o spikes or does it drop completely to zero (which is also bad -
especially if you are on a SAN and you can't get any packets out on that
network).  You'll have to look at your disks via OS tools to see.

Do you have any hardware faults?  Errors on a hardware bus?  Overheating?
I used to have a system that would freeze up entirely due to a problem with
a serial port that we had a console attached to - it was sending a low
level interrupt.  Sometimes it would recover mysteriously if someone hit
the carriage return a couple times.  Ie, is it _really_ the database that
is locking up, or is it your hardware?

Re: LIKE CLAUSE on VIEWS

[Rick Otten]

>
>
> On Sun, 22 Jan 2023 at 13:34, aditya desai  wrote:
>
>> Hi,
>> Is there any way to improve performance of LIKE clause on VIEWS.
>>
>> select * From request_vw where upper(status) like '%CAPTURED%' - 28
>> seconds.
>>
>> select * from  request_vw where status='CAPTURED'
>>
>> Application team is reluctant to change queries from the Application side
>> to = instead of LIKE.
>>
>> Also as this is VIEW TRIGRAM nor normal indexes don't get used.
>>
>>
>> Regards,
>> Aditya.
>>
>
You could try using the `text_pattern_ops` operator class on your index on
the `status` column:
https://www.postgresql.org/docs/current/indexes-opclass.html

creating hash indexes

[Rick Otten]

I inherited a database with several single-digit billion row tables.  Those
tables have a varchar(36) column populated with uuids (all connected to
each other via FKs) each currently supported by a btree index.

After the recent conversations about hash indexes I thought I'd do some
comparisons to see if using a hash index could help and perhaps
depriortize my burning desire to change the data type.  We never look up
uuids with inequalities after all.  Indeed, in my test environments the
hash index was half the size of the btree index, and the select performance
was slightly faster than btree lookups. varchar(36) with hash index was
roughly comparable to using a uuid data type (btree or hash index).

I was pretty excited until I tried to create the index on a table with the
data (instead of creating it ahead of time and then loading up the test
data).

Working in PG 14.5, on a tiny 9M row table, in an idle database, I found:
- creating the btree index on the varchar(36) column to consistently take 7
*seconds*
- creating the hash index on the varchar(36) to consistently take 1 *hour*

I was surprised at how dramatically slower it was.   I tried this on both
partitioned and non-partitioned tables (with the same data set) and in both
cases the timings came out similar.

I also tried creating a hash index on a varchar(100) column, also with 9M
rows. I gave up after it did not complete after several hours.  (it wasn't
locked, just slow)

While I was experimenting with the different index types, I did some insert
tests.  After putting the hash index on the column, the inserts were
significantly slower.  The btree index was *6-7x *slower than no index, and
the hash index was *100x* slower than no index.

Assuming I can live with the slower inserts, is there any parameter in
particular I can tweak that would make the time it takes to create the hash
index closer to the btree index creation time?  In particular if I wanted
to try this on a several billion row table in a busy database?

---

FWIW, from my tests on my laptop, on a 250M row table last weekend, after
100K selects:

MEAN (ms)  | btree|  hash
-  | ---  |  
varchar| 28.14916 | 27.03769
uuid   | 27.04855 | 27.64424

and the sizes

SIZE| btree| hash
| -| 
varchar | 12 GB| 6212 MB
uuid| 6595 MB  | 6212 MB

-  As long as the index fits in memory, varchar btree isn't really that
much slower in postgresql 14 (the way it was a few years ago), so we'll
probably just live with that for the forseeable future given the complexity
of changing things at the moment.

--
Rick

Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance

[Rick Otten]

I was able to reproduce a similar issue with using `= ANY(VALUES)`
> instead of `= ANY(ARRAY)`:
>
> 1. fast query using =ANY(ARRAY): https://explain.depesz.com/s/dwP8
> 2. slow query using =ANY(ARRAY(expr)): https://explain.depesz.com/s/3hGb
> 3. slow query using =ANY(VALUES): https://explain.depesz.com/s/cYrn
>
>
 I have found the "ANY" operator to be slow in general.  It is almost
always faster to use the "<@" operator:
```
-- more intuitive:
select
  count(*)
from
  testarray
where
  'test' = ANY (myarray)
;

-- faster:
select
  count(*)
from
  testarray
where
  ARRAY['test'::varchar] <@ myarray
;
```
It is just one of those things, like replacing "OR" with "UNION ALL"
whenever possible too, that just make queries faster in PostgreSQL without
a ton of effort or fuss.

Re: Identify root-cause for intermittent spikes

[Rick Otten]

I like to use pgbadger to collect data on what is happening in RDS
instances.  You have to turn up a bunch of logging in RDS:

1. Turn on connection logging, duration logging, lock_waits, and anything
else that you are interested in studying.

2. Then grab all of your postgresql logs from AWS.  I wrote this little
bash script to pull all of the logs for a current day.  It will work if you
have your aws credentials configured correctly and can run aws-cli commands.
```
#!/bin/env bash

## Return all of the postgresql log files saved by RDS since midnight.
## Save them in your current directory.
## This is so we can use cli tools like "grep"
## It is also really handy for feeding into pgbadger for deeper analysis.

# aws requires the timestamp to be in milliseconds.
# unfortunately date will provide either seconds or nano seconds, so we
have to do math.
midnight_timestamp=$(date -d $(date -I) '+%s')
midnight_timestamp_milliseconds=$(echo "${midnight_timestamp} * 1000" | bc)

logfiles=$(aws rds describe-db-log-files \
  --profile default \
  --db-instance-identifier "*some_rds_instance_name*" \
  --output json  \
  --file-last-written ${midnight_timestamp_milliseconds} | jq
-r ".DescribeDBLogFiles[].LogFileName")

for logfile in $(echo ${logfiles})
do
# remove the leading "error/" so we can use the name to save it.
logfile_save=$(echo "${logfile}" | awk -F\/ '{print $NF}')

tput bold; echo "${logfile}"; tput sgr0
aws rds download-db-log-file-portion \
--profile admin \
--db-instance-identifier prod-notify-me-1 \
--log-file-name ${logfile} \
--output text \
--no-paginate > ${logfile_save}
done
```
3. Then run pgbadger:
``` ~/src/pgbadger/pgbadger -f rds  postgresql*
```
4. Open the `out.html` in your browser, and poke around.   There is a ton
of stuff you can find in all the drop down menus about what was happening
in your database over the time window you collected the logs for.  The html
is generated as a standalone file by a perl script of all things.  It is
pretty impressive.



On Tue, Oct 11, 2022 at 7:07 AM Sengottaiyan T  wrote:

> Hi All,
>>
>> I'm looking for suggestions:
>>
>> Environment: AWS PostgreSQL RDS instance - Version 14.3
>> Operations support gets intermittent alerts from the monitoring tool
>> through AWS cloud watch metrics on Disk Queue Depth, CPU burst-credit & CPU
>> Utilization.
>> I would like to understand what is causing the spike - is the number of
>> logon's increased, (or) number of transactions per second increased, (or)
>> SQL execution picked wrong plan and the long running (I/O, CPU or memory
>> intensive) SQL is increasing load on server (cause and effect scenario)
>> etc.,
>>
>> Due to the reactive nature of the issues, we rely on the metrics gathered
>> in the AWS cloud watch monitoring (for the underlying OS stats),
>> Performance Insights (for the DB performance) and correlate SQL queries
>> with pg_Stat_Statements view. But the data in the view is an aggregated
>> stats. And, I'm looking to see the deltas compared to normal runs.
>> How should I approach and get to the root-cause?
>>
>> AppDynamics is already configured for the RDS instance. Are there any
>> open source monitoring tools available which would help to capture and
>> visualize the deltas?
>>
>> Thanks,
>> Senko
>>
>

Re: to_jsonb performance on array aggregated correlated subqueries

[Rick Otten]

On Fri, Aug 12, 2022 at 3:02 PM Rick Otten  wrote:

>
>
> On Fri, Aug 12, 2022 at 2:50 PM Nico Heller  wrote:
>
>> Good day,
>>
>> consider the following query:
>>
>> WITH aggregation(
>>  SELECT
>> a.*,
>>(SELECT array_agg(b.*) FROM b WHERE b.a_id = a.id) as "bs",
>>(SELECT array_agg(c.*) FROM c WHERE c.a_id = a.id) as "cs",
>>(SELECT array_agg(d.*) FROM d WHERE d.a_id = a.id) as "ds",
>>(SELECT array_agg(e.*) FROM d WHERE e.a_id = a.id) as "es"
>>  FROM a WHERE a.id IN (> )
>> SELECT to_jsonb(aggregation.*) as "value" FROM aggregation;
>>
>>
> - You do have an index on `b.a_id` and `c.a_id`, etc... ?  You didn't
> say...
>
> - Are you sure it is the `to_jsonb` that is making this query slow?
>
> - Since you are serializing this for easy machine readable consumption
> outside of the database, does it make a difference if you use `to_json`
> instead?
>
>
To follow up here a little.  I ran some quick tests on my database and
found that `to_json` is consistently, slightly, faster than `to_jsonb` when
you are just serializing the result set for consumption.   I feed in some
arrays of 1,000,000 elements for testing.  While both json serializers are
slower than just sending back the result set, it wasn't significant on my
machine with simple object types.  (3% slower).

Are any of your objects in "b.*", etc, complex data structures or deeper
arrays, or gis shapes, or strange data types that might be hard to
serialize?  I'm wondering if there is something hidden in those ".*" row
sets that are particularly problematic and compute intensive to process.

Re: Postgresql 14 partitioning advice

[Rick Otten]

On Mon, Aug 1, 2022 at 10:16 AM Rick Otten  wrote:

>
>> The other problem I ran into, which I'm still building a test case for
>> and I fear might be a bug if I can easily reproduce it,
>> is if I did the original select in a CTE, and then did a sort outside of
>> the CTE, even though the CTE found 0 rows, the database
>> still spent a _ton_ of time sorting those 0 rows:
>> ```
>>->  Sort  (cost=70.03..72.53 rows=1000 width=112) (actual
>> time=84848.452..84848.453 rows=0 loops=1)
>> ```
>> Once I can reproduce this on test data I'll be able to pin down more
>> closely what is happening and tell if I'm just reading
>> the explain plan wrong or if something is broken.  It was getting mixed
>> up with the lack of pruning/index usage problem.
>>
>> I'll report back again next week.  Anyway it is looking to me like it
>> doesn't really matter (within reason) from a performance
>> perspective how many partitions we use for our data set and query
>> patterns.  We should be able to pick the most convenient
>> from an archiving and data management perspective instead.
>>
>>
> This behavior is definitely consistent.  0 rows end up slower than when I
> find some rows in my CTE:
> ```
>->  Sort  (cost=109.44..113.19 rows=1500 width=112) (actual
> time=87110.841..87110.842 rows=0 loops=1)
>->  Sort  (cost=109.44..113.19 rows=1500 width=112) (actual
> time=25367.867..25367.930 rows=840 loops=1)
> ```
> The only thing I changed in the query was the date range.  It is actually
> the CTE scan step inside the Sort block that is slower when no rows are
> returned than when rows are returned.  It also only happens when all the
> partitions are sequence scanned instead of being partition pruned.
>
> I'm still writing up a test case that can demo this without using
> proprietary data.
>

After a bunch of experiments I can explain this now.  :-)

I had a `limit` clause in my test CTE.  When sequence scanning a bunch of
partitions, if the limit is reached, the subsequent partitions are marked
with `never executed` and not scanned.  On the other hand, when no rows are
found, all of the partitions are scanned.

Therefore, with many millions of rows in the partitions, and being forced
to sequence scan because I put the `at time zone` clause in the `where`,
the case when rows are found is always noticeably faster than the case when
rows aren't found as long as at least one partition hasn't been scanned yet
when the limit is hit.

I'm now satisfied this is a good thing, and will move on to other
problems.  Thanks for hearing me out.  I was scratching my head for a while
over that one.

Re: Postgresql 14 partitioning advice

[Rick Otten]

>
>
> The other problem I ran into, which I'm still building a test case for and
> I fear might be a bug if I can easily reproduce it,
> is if I did the original select in a CTE, and then did a sort outside of
> the CTE, even though the CTE found 0 rows, the database
> still spent a _ton_ of time sorting those 0 rows:
> ```
>->  Sort  (cost=70.03..72.53 rows=1000 width=112) (actual
> time=84848.452..84848.453 rows=0 loops=1)
> ```
> Once I can reproduce this on test data I'll be able to pin down more
> closely what is happening and tell if I'm just reading
> the explain plan wrong or if something is broken.  It was getting mixed up
> with the lack of pruning/index usage problem.
>
> I'll report back again next week.  Anyway it is looking to me like it
> doesn't really matter (within reason) from a performance
> perspective how many partitions we use for our data set and query
> patterns.  We should be able to pick the most convenient
> from an archiving and data management perspective instead.
>
>
This behavior is definitely consistent.  0 rows end up slower than when I
find some rows in my CTE:
```
   ->  Sort  (cost=109.44..113.19 rows=1500 width=112) (actual
time=87110.841..87110.842 rows=0 loops=1)
   ->  Sort  (cost=109.44..113.19 rows=1500 width=112) (actual
time=25367.867..25367.930 rows=840 loops=1)
```
The only thing I changed in the query was the date range.  It is actually
the CTE scan step inside the Sort block that is slower when no rows are
returned than when rows are returned.  It also only happens when all the
partitions are sequence scanned instead of being partition pruned.

I'm still writing up a test case that can demo this without using
proprietary data.

Re: Postgresql 14 partitioning advice

[Rick Otten]

On Wed, Jul 27, 2022 at 8:55 AM Rick Otten  wrote:

> I'm spinning up a new Postgresql 14 database where I'll have to store a
> couple years worth of time series data at the rate of single-digit millions
> of rows per day.  Since this has to run in AWS Aurora, I can't use
> TimescaleDB.
>

I thought I'd report back some of my findings from testing this week:

I took the same real world two week data set and created identical tables
except that I partitioned one by month, one by week, one by day, and one by
hour.  I partitioned a little bit into the past and a little bit into the
future. I did this on a PG 14.2 RDS instance.  This gave me tables with:
3 partitions, 13 partitions, 90 partitions and 2136 partitions, but
otherwise the same data.

Insert times were equivalent.

Then I crafted a query that was one of the main use cases for the data and
ran it a bunch of times.

I noticed a significant degradation in performance as the number of
partitions increased.  The jump from 13 to 90, in particular, was very
steep.  It didn't matter what I set work_mem or other tunables to.  I dug
deeper...

Surprising to me was if you partition on a `timestamp with timezone`
column, call it "ts":
If your where clause looks like
```
where ts at time zone 'UTC' > '2022-07-01 00:00'::timestamp
```
you will NOT get partition pruning and it will sequence scan.
However if you change it to (with an appropriately adjusted right hand side
if necessary):
```
where ts > '2022-07-01 00:00'::timestamp
```
It will do partition pruning and will index scan.

When I made that change the query performance was equivalent regardless of
which number of partitions I had in play.
I did a quick test and this happens on a regular timestamp index on a
regular table as well.

The other problem I ran into, which I'm still building a test case for and
I fear might be a bug if I can easily reproduce it,
is if I did the original select in a CTE, and then did a sort outside of
the CTE, even though the CTE found 0 rows, the database
still spent a _ton_ of time sorting those 0 rows:
```
   ->  Sort  (cost=70.03..72.53 rows=1000 width=112) (actual
time=84848.452..84848.453 rows=0 loops=1)
```
Once I can reproduce this on test data I'll be able to pin down more
closely what is happening and tell if I'm just reading
the explain plan wrong or if something is broken.  It was getting mixed up
with the lack of pruning/index usage problem.

I'll report back again next week.  Anyway it is looking to me like it
doesn't really matter (within reason) from a performance
perspective how many partitions we use for our data set and query
patterns.  We should be able to pick the most convenient
from an archiving and data management perspective instead.

Postgresql 14 partitioning advice

[Rick Otten]

I'm spinning up a new Postgresql 14 database where I'll have to store a
couple years worth of time series data at the rate of single-digit millions
of rows per day.  Since this has to run in AWS Aurora, I can't use
TimescaleDB.

I've been soliciting advice for best practices for building this.

One person I talked to said "try not to have more than 100 partitions",
even with the latest postgresql you'll end up with a lot of lock contention
if you go over 100 partitions.  This person also recommended manually
kicking off vacuums on a regular schedule rather than trusting autovacuum
to work reliably on the partitioned tables.

I've got several keys, besides the obvious time-key that I could partition
on.   I could do a multi-key partitioning scheme.  Since the data is
inbound at a relatively steady rate, if I partition on time, I can adjust
the partitions to be reasonably similarly sized.  What is a good partition
size?

Are there any tunables I should experiment with in particular for a
database with only 2 or 3 tables in it but many partitions each with
millions of rows?

Since the data most frequently queried would be recent data (say the past
month or so) would it make sense to build an archiving strategy that rolled
up older partitions into larger ones?  ie, do daily partitions for the
first four weeks, then come up with a process that rolled them up into
monthly partitions for the next few months, then maybe quarterly partitions
for the data older than a year?  (I'm thinking about ways to keep the
partition count low - if that advice is justified.)

Or, should I just have a single 7 Trillion row table with a BRIN index on
the timestamp and not mess with partitions at all?

Re: data consolidation: logical replication design considerations

[Rick Otten]

On Sat, Jul 16, 2022 at 12:07 PM Rory Campbell-Lange <
r...@campbell-lange.net> wrote:

> I'd be grateful for some comments on the advisability of using a large
> number of concurrent logical replication publications/subscriptions.
> Below I've set out the current environment and a suggested design.
> Apologies for the length of this email.
>

Another possibility is to use SymmetricDS for this.  [
https://symmetricds.org ]  SymmetricDS was originally developed to keep
databases on thousands of Point-of-Sale databases (in cash registers) in
sync with pricing and inventory data for large international retailers.

There are lots of other use cases, but even 10-12 years ago it was scalable
to the extent you are describing you need here.

The main drawback is that it is trigger based, so there is some slight
latency introduced for insert/update/delete actions on the tables on the
appropriate master, but it usually isn't significant.

Re: postgresql13-llvm jit-13.5-1PGDG.rhel8.x86_64

[Rick Otten]

On Mon, Nov 15, 2021 at 8:57 AM Mladen Gogala 
wrote:

>
> On 11/15/21 00:04, Pavel Stehule wrote:
>
>
> Hi
>
>
>> There is a CentOS8-stream version which solves the problem but I cannot
>> use that in the office. I will probably have to wait for another month
>> before OL8 has everything that I need in its repositories. Now, the
>> question is what kind of an impact will running without llvm-jit have?
>> According to the links below, llvm-jit effects are quite spectacular:
>>
>> https://llvm.org/devmtg/2016-09/slides/Melnik-PostgreSQLLLVM.pdf
>>
>> https://www.pgcon.org/2017/schedule/events/1092.en.html
>
>
> When JIT was used on very large query with a lot of CASE expr, then JIT
> has a positive effect about 50%. On usual large queries, the effect of JIT
> was about 20%. Unfortunately, JIT is sensitive to estimation, and the JIT
> sometimes increases seconds to queries, although without JIT this query is
> executed in ms. When you use a query that can be well calculated in
> parallel, then positive effect of JIT is less.
>
> Regards
>
> Pavel
>
>
> Thanks Pavel, you answered my question. I'll wait with the upgrade.
>
>
>
FWIW, there was a lively discussion on the postgresql subreddit over the
weekend on JIT:
https://www.reddit.com/r/PostgreSQL/comments/qtsif5/cascade_of_doom_jit_and_how_a_postgres_update_led/

(lively for that subreddit anyway)

Re: Logical Replication speed-up initial data

[Rick Otten]

On Thu, Aug 5, 2021 at 12:57 AM Nikhil Shetty 
wrote:

> Hi,
>
> Thank you for the suggestion.
>
> We tried by dropping indexes and it worked faster compared to what we saw
> earlier. We wanted to know if anybody has done any other changes that helps
> speed-up initial data load without dropping indexes.
>

It would be kind of cool if the database could just "know" that it was an
initial load and automatically suppress FK checks and index updates until
the load is done.  Once complete it would go back and concurrently rebuild
the indexes and validate the FK's.   Then you wouldn't have to manually
drop all of your indexes and add them back and hope you got them all, and
got them right.

Re: Very slow Query compared to Oracle / SQL - Server

[Rick Otten]

On Thu, May 6, 2021 at 10:38 AM Semen Yefimenko 
wrote:

> Hi there,
>
> I've recently been involved in migrating our old system to SQL Server and
> then PostgreSQL. Everything has been working fine so far but now after
> executing our tests on Postgres, we saw a very slow running query on a
> large table in our database.
> I have tried asking on other platforms but no one has been able to give me
> a satisfying answer.
> ...
>
> SELECT column1,..., column54  where ((entrytype = 4000 or entrytype = 4001
> or entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;
>
>
>
I know several people have suggested using `IN` to replace the or
statements, that would be my first go-to also.  Another approach I have
found helpful is to keep in mind whenever you have an  `OR`  in a where
clause it can be replaced with a `UNION ALL`.  Usually the `UNION ALL` is
faster.

I recommend avoiding `OR` in where clauses as much as possible.  -
Sometimes it can't be helped, especially if you need an exclusive or, but
most of the time there is another way that is usually better.

Another thought is "archivestatus" really a boolean or does it have
multiple states?  If it is actually a boolean, then can you change the
column data type?

Re: 15x slower PreparedStatement vs raw query

[Rick Otten]

On Tue, May 4, 2021 at 6:05 AM Alex  wrote:

> Shouldn't this process be automatic based on some heuristics?
>
> Saving 10ms planning but costing 14s execution is catastrophic.
>
> For example, using some statistics to limit planner time to some percent
> of of previous executions.
> This way, if query is fast, planning is fast, but if query is slow, more
> planning can save huge execution time.
> This is a better general usage option and should be enabled by default,
> and users who want fast planning should set the variable to use the generic
> plan.
>
>
>
"fast" and "slow" are relative things.  There are many queries that I would
be overjoyed with if they completed in 5 _minutes_.  And others where they
have to complete within 100ms or something is really wrong.  We don't
really know what the execution time is until the query actually executes.
Planning is a guess for the best approach.

Another factor is whether the data is in cache or out on disk.  Sometimes
you don't really know until you try to go get it.  That can significantly
change query performance and plans - especially if some of the tables in a
query with a lot of joins are in cache and some aren't and maybe some have
to be swapped out to pick up others.

If you are running the same dozen queries with different but similarly
scoped parameters over and over, one would hope that the system would
slowly tune itself to be highly optimized for those dozen queries.  That is
a pretty narrow use case for a powerful general purpose relational database
though.

Re: Odd (slow) plan choice with min/max

[Rick Otten]

On Tue, Mar 23, 2021 at 2:52 AM Paul McGarry  wrote:

>
>
> On Tue, 23 Mar 2021 at 16:13, Justin Pryzby  wrote:
>
>> On Tue, Mar 23, 2021 at 03:00:38PM +1100, Paul McGarry wrote:
>> > I have a query where Postgresql (11.9 at the moment) is making an odd
>> plan
>> > choice, choosing to use index scans which require filtering out
>> millions of
>> > rows, rather than "just" doing an aggregate over the rows the where
>> clause
>> > targets which is much faster.
>> > AFAICT it isn't a statistics problem, at least increasing the stats
>> target
>> > and analyzing the table doesn't seem to fix the problem.
>>
>> >  explain analyze select min(risk_id),max(risk_id) from risk where
>> > time>='2020-01-20 15:00:07+00' and time < '2020-01-21 15:00:08+00';
>>
>> I'm guessing the time and ID columns are highly correlated...
>>
>> So the planner thinks it can get the smallest ID by scanning the ID
>> index, but
>> then ends up rejecting the first 161e6 rows for which the time is too
>> low, and
>> fails the >= condition.
>>
>> And thinks it can get the greatest ID by backward scanning the ID idx,
>> but ends
>> up rejecting/filtering the first 41e6 rows, for which the time is too
>> high,
>> failing the < condition.
>>
>
> Yes, the columns are highly correlated, but that alone doesn't seem like
> it should be sufficient criteria to choose this plan.
> Ie the selection criteria (1 day of data about a year ago) has a year+
> worth of data after it and probably a decade of data before it, so anything
> walking a correlated index from top or bottom is going to have to walk past
> a lot of data before it gets to data that fits the criteria.
>


I assume you have a statistic on the correlated columns, ie `create
statistic` ?

If you can't use partitions on your date column, can you use partial
indexes instead?   Or a functional index with min() over day and max() over
day?

Re: Potential performance issues

[Rick Otten]

On Mon, Mar 1, 2021 at 8:44 AM Bob Jolliffe  wrote:

> Was just about to reply similarly.  Mind you it perhaps does raise the
> question : are the default postgresql settings perhaps too
> conservative or too static.  For example, in the absence of other
> explicit configuration, might it make more sense for many use cases
> for postgres to assess the physical memory available and make some
> half-sensible allocations based on that?  I know there are downsides
> to assuming that postgresql has free reign to all that it sees, but
> there are clearly also some downsides in assuming it has next to
> nothing.  This could also be more correctly part of a package
> installation procedure, but just floating the idea ... some kind of
> auto-tuning vs ultra-conservative defaults.
>
>
When you spin up an Aurora or RDS instance in AWS, their default parameter
group values are mostly set by formulas which derive values based on the
instance size.  Of course they can assume free reign of the entire system,
but the values they choose are still somewhat interesting.

For example, they set `maintenance_work_mem` like this:
"GREATEST({DBInstanceClassMemory/63963136*1024},65536)"

It doesn't completely remove the need for a human to optimize the parameter
group based on your use case, but it does seem to give you a better novice
starting point to work from.  And there are definitely some formulas that I
disagree with in the general case.  However it is something that is
adaptable for those times when you bump up the server size, but don't want
to have to revisit and update every parameter to support the change.

I've been thinking a lot about running PG in containers for dev
environments lately, and trying to tune to get reasonable dev performance
out of a container without crushing the other services and containers on
the laptop.  Most developers that I've worked with over the past few years
only have exposure to running PG in a container.  They've simply never run
it on a server or even barebones on their laptop.  I think any modern
approach to a default set of tuning parameters would probably also need to
be "container aware", which is for all practical purposes the new default
"minimal configuration" on multi-purpose systems.

Re: Best way to delete big amount of records from big table

[Rick Otten]

On Fri, Mar 27, 2020 at 10:14 AM Ekaterina Amez 
wrote:

>
> it's there a better way to do this. I'm testing on version 9.2 BUT
> production server is 8.4 (legacy application, supposed to be in at least
> 9.2 but recently discovered it was 8.4, planning upgrade but not now).
> Config parameters are default ones.
>

PostgreSQL 8.4 came out in 2009 and hit EOL in 2014.   PostgreSQL 9.2 hit
EOL in 2017.
https://en.wikipedia.org/wiki/PostgreSQL#Release_history

Re: Slow "not in array" operation

[Rick Otten]

On Wed, Nov 13, 2019 at 5:47 AM Morris de Oryx 
wrote:

> Disclaimer: Out over my skis again.
>
> From what you say here, and over on SO, it sounds like you've got two
> problems:
>
> * Matching on *huge *numbers of records because of common tags.
>
> * A dynamic collection of tags as they're customer driven/configured.
>
> An "ideal" solution might look like a bit-index for each tag+tuple, but
> Postgres does not have such a structure. The closest I've seen are Bloom
> filter based indexes. That's likely not going to work here as you don't
> know the collection of tags at any one time. If, however, you create your
> own frequency count estimates for tags, you may well find that there are a
> small number of common tags, and a large number of rare tags. That would be
> good to find out. If you do have some super common (non selective) tags,
> then perhaps a Bloom index based on that collection could be effective. Or
> expression indexes on the very common tags. In your SaaS setup, you might
> need counts/indexes tied to some kind of customer/tenancy distinction ID,
> understood. But, for simplicity, I'm just saying a single set of frequency
> counts, etc.
>
> Here's a recent article on Bloom filter based indexes in Postgres that
> looks decent:
> https://www.percona.com/blog/2019/06/14/bloom-indexes-in-postgresql/
>

One other question might be whether you are always querying for a specific
tag or small set of tags, or if your queries are for relatively random
tags.  ie, if you are always looking for the same 2 or 3 tags, then maybe
you could use a functional index or trigger-populate a new column on
insert/update that indicates whether those tags are present.

It is possible that you want a Graph model for this data instead of a
Relational model.  ie, if you are finding a bunch of users with common
features, you may find traversing a graph (such as Neo4j - or if you _have_
to stay with a PG backend, something like Cayley.io) to be much more
efficient and flexible.

Re: Would SSD improve Index Only Scan performance by a lot?

[Rick Otten]

On Tue, Oct 8, 2019 at 7:37 PM Arya F  wrote:

> As my table has gotten bigger, it takes longer to get a single row back
> when querying a row by its btree index.
>
> Right now the database is running on a traditional HDD. SSDs have a much
> faster seek time than traditional HDDs.
>
> Would switching to an SSD improve "Index Only Scan" time greatly? by at
> least 3-4 times?
>

*If* your query is disk I/O bound, SSD can help a lot.

If your data is already in memory, or file system cache, and your query is
bound by CPU or bloated/corrupted indexes, or some query inefficiency, then
faster disks really won't do anything.

Depending on the data type and size of the data you may be able to help
your query performance by choosing an index type other than the
out-of-the-box btree as well (such as a hash or brin index) or maybe even a
different sort order on the index, or a partial index.

Re: PSQL performance - TPS

[Rick Otten]

On Thu, Aug 1, 2019 at 2:15 PM Andres Freund  wrote:

> Hi,
>
> On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote:
> > > If you've set synchronous_commit = off, and you still get only 1200
> > > transactions/sec, something else is off. Are you sure you set that?
> > I am not very surprised with these results. However, what’s the disk
> type?
> > That can matter quite a bit.
>
>
Also a reminder that you should have a connection pooler in front of your
database such as PGBouncer.  If you are churning a lot of connections you
could be hurting your throughput.

Re: High concurrency same row (inventory)

[Rick Otten]

On Mon, Jul 29, 2019 at 2:16 AM Jean Baro  wrote:

>
> We have a new Inventory system running  on its own database (PG 10 AWS
> RDS.m5.2xlarge 1TB SSD EBS - Multizone). The DB effective size is less than
> 10GB at the moment. We provided 1TB to get more IOPS from EBS.
>
> As we don't have a lot of different products in our catalogue it's quite
> common (especially when a particular product is on sale) to have a high
> rate of concurrent updates against the same row. There is also a frequent
> (every 30 minutes) update to all items which changed their current
> stock/Inventory coming from the warehouses (SAP), the latter is a batch
> process. We have just installed this system for a new tenant (one of the
> smallest one) and although it's running great so far, we believe this
> solution would not scale as we roll out this system to new (and bigger)
> tenants. Currently there is up to 1.500 transactions per second (mostly
> SELECTS and 1 particular UPDATE which I believe is the one being
> aborted/deadlocked some tImes) in this inventory database.
>
> I am not a DBA, but as the DBAs (most of them old school Oracle DBAs who
> are not happy with the move to POSTGRES) are considering ditching
> Postgresql without any previous tunning I would like to understand the
> possibilities.
>
> Considering this is a highly concurrent (same row) system I thought to
> suggest:
>
>
>
Another thing which you might want to investigate is your checkpoint
tunables.  My hunch is with that many writes, the defaults are probably not
going to be ideal.
Consider the WAL tunables documentation:
https://www.postgresql.org/docs/10/wal-configuration.html

Re: benchmarking effective_io_concurrency

[Rick Otten]

On Mon, Jul 22, 2019 at 2:42 AM Fabio Pardi  wrote:

> Hello,
>
>
> I recently spent a bit of time benchmarking effective_io_concurrency on
> Postgres.
>
> I would like to share my findings with you:
>
>
> https://portavita.github.io/2019-07-19-PostgreSQL_effective_io_concurrency_benchmarked/
>
> Comments are welcome.
>
> regards,
>
> fabio pardi
>

You didn't mention what type of disk storage you are using, or if that
matters.  The number of cores in your database could also matter.

Does the max_parallel_workers setting have any influence on how
effective_io_concurrency works?

Based on your data, one should set effective_io_concurrency at the highest
possible setting with no ill effects with the possible exception that your
disk will get busier.  Somehow I suspect that as you scale the number of
concurrent disk i/o tasks, other things may start to suffer.  For example
does CPU wait time start to increase as more and more threads are consumed
waiting for i/o instead of doing other processing?  Do you run into lock
contention on the i/o subsystem?  (Back in the day, lock contention for
/dev/tcp was a major bottleneck for scaling busy webservers vertically.  I
have no idea if modern linux kernels could run into the same issue waiting
for locks for /dev/sd0.  Surely if anything was going to push that issue,
it would be setting effective_io_concurrency really high and then demanding
a lot of concurrent disk accesses.)

Re: Max_connections limit

[Rick Otten]

On Wed, Jun 26, 2019 at 5:16 AM Hervé Schweitzer (HER) <
herve.schweit...@dbi-services.com> wrote:

> You now that Postgres don’t have any shared_pool as Oracle, and the
> session information ( execution plan, etc..) are only available for the
> current session. Therefore I also highly recommend to us a connection poll
> as Laurent wrote, in order to have higher chance that some stuff is already
> cached in the shared session available.
>
> Regards
> Herve
>
>
The most popular stand-alone connection pooler for PostgreSQL is the oddly
named "pgbouncer":https://wiki.postgresql.org/wiki/PgBouncer
There are others, of course.

PgPool is also very popular:
https://www.pgpool.net/mediawiki/index.php/Main_Page

Some applications can also manage a connection pool efficiently entirely
within the application itself.

Configuring the maximum number of concurrent connections your database
supports incurs significant overhead in the running database.  New
connections and disconnections also have a high overhead as they occur.  By
moving the connecting/disconnecting logic to a connection pooler you remove
a lot of overhead and load from the database - letting it focus on the
important stuff -- your queries.

It is amazing how many fewer actual connections you need to the database
when you configure a pooler.  Most connections from applications and users
are idle most of the time.   Even on busy web servers.  They just keep that
pathway open in case they need to run a query to save on the overhead of
having to open a new one every time.   By using a pooler you only need to
configure connections for the number of concurrent _queries_ rather than
concurrent application and user open but idle connections.

Re: materialized view refresh of a foreign table

[Rick Otten]

On Sun, Jun 23, 2019 at 10:21 AM Rick Otten 
wrote:

> I'm not sure where else to look, so I'm asking here for tips.
>
> I have a table in a remote (Heroku-managed) postgresql database (PG 10.7).
>
> On the other end, (PG 11.3) I have a foreign table configured with a
> materialized view in front of it.
>
> Up until Thursday evening, it was taking about 12 - 15 seconds to refresh,
> it is only 15,000 rows with 41 columns.   Since Thursday evening it has
> been taking 15 _minutes_ or more to refresh.  Nothing changed on my end
> that I'm aware of.  It completes, it just takes forever.
>
>
I believe I've solved this mystery.  Thanks for hearing me out.  Just the
opportunity to summarize everything I'd tried helped me discover the root
cause.

In the middle of the table there is a 'text' column.   Since last Thursday
there were a number of rows that were populated with very long strings.
(lots of text in that column).   This appears to have completely bogged
down the materialized view refresh.  Since we weren't using that column in
our analytics database at this time, I simply removed it from the
materialized view.  If we do end up needing it, I'll give it its own
materialized view and/or look at chopping up the text into just the bits we
need.

materialized view refresh of a foreign table

[Rick Otten]

I'm not sure where else to look, so I'm asking here for tips.

I have a table in a remote (Heroku-managed) postgresql database (PG 10.7).

On the other end, (PG 11.3) I have a foreign table configured with a
materialized view in front of it.

Up until Thursday evening, it was taking about 12 - 15 seconds to refresh,
it is only 15,000 rows with 41 columns.   Since Thursday evening it has
been taking 15 _minutes_ or more to refresh.  Nothing changed on my end
that I'm aware of.  It completes, it just takes forever.

Here is a summary of what I've tried:

1) Refreshing the materialized views of other tables from that same source
database, some much bigger, still perform within seconds as they always
have.
2) Dropping the foreign table and the materialized view and recreating them
didn't help.
3) It doesn't matter whether I refresh concurrently or not.
4) Configuring the foreign table and materialized view on my laptop's
postgresql instance exhibited the same behavior for just this one table.
5) Querying the foreign table directly for a specific row was fast.
6) Reindex and vacuum full analyze on the source table didn't help.
7) Bumping the database on my end to 11.4, didn't help.
8) There are no locks on either database that I can see while the query
appears to be stalled.
9) Running the materialized view select directly against the source table
completes within seconds.
10) Running the materialized view select directly against the foreign table
also completes within a few seconds.
11) Dropping all of the indexes on the materialized view, including the
unique one and running the refresh (without 'concurrently') does not help.

I feel like I'm missing something obvious here, but I'm just not seeing
it.  Any thoughts about where else to look?

Re: Oracle to postgres migration

[Rick Otten]

On Mon, Apr 8, 2019 at 8:04 AM Julien Rouhaud  wrote:

> On Mon, Apr 8, 2019 at 1:49 PM Daulat Ram 
> wrote:
> >
> > Please confirm ! Can we migrate Oracle 12c database (12.1.0.1.0) running
> on Solaris to PostgreSQL 11.2 on  Linux (Ubuntu). Also, please suggest the
> tools and pre-requisites.
> A database migration is likely feasible, but might require quite a lot
> of work depending on what features you're using, and the amount of PL
> code.  Also, obviously migrating the database is only a part of the
> overall migration process, as you'll also need to take care of the
> application(s), the backup/restore, monitoring and all other tools you
> need.
>
> Concerning the database migration, the best tool is probably Gilles
> Darold's ora2pg.  The tool also provides a migration cost assessment
> report, to evaluate the difficulty of the database migration.  More
> information on http://ora2pg.darold.net/
>
>
>
The last big Oracle to PG migration that I did was several years ago.  We
stood up the PostgreSQL instance(s) and then used SymmetricDS to
synchronize the Oracle and PG databases.   After tuning and testing the
postgresql side, we cut over the applications live - with minimal downtime
- by releasing the updated application code and configuration.   If we
needed to fail back, it was also pretty easy to undo the release and
configuration changes.

Another approach you can play with is to leverage Foreign Data Wrappers.
In that scenario, you can run queries on your Oracle database from within
PostgreSQL.  You can use those queries to copy data directly into new
tables without any interim files, or as a hybrid transition while you get
the new database set up.

At the time I was working on that migration, we had too many
data-edge-cases for ora2pg to be very useful.  It has come a long ways
since then.  I'm not sure it can do a live cutover, so you may need to plan
a bit of downtime if you have a lot of data to move into the new database.

Note that you will also almost certainly want to use a connection pooler
like PGBouncer and/or PGPool II (or both at the same time), so be sure to
include that in your plans from the beginning.

That said, none of this is on topic for the performance mailing list.
Please try to direct your questions to the right group next time.

Re: Why Postgres doesn't use TID scan?

[Rick Otten]

On Wed, Dec 19, 2018 at 6:45 PM Vladimir Ryabtsev 
wrote:

> > The fundamental issue is that "ANY" has two meanings in PG, one of them
> following the SQL standard and one not:
>
> Oh yes, I was aware about two forms but it did not come into my mind, I
> was thinking I use the same form in both cases since my query returns only
> one row and column.
> Thanks for pointing me into that.
>
> --
> Vlad
>

For what it is worth, I have found that if I am checking for the presence
of an object in an array, while this syntax is easy to understand and more
intuitive to craft:

select
  *
from
  mytable
where
  ' test' = ANY (my_varchar_array_column)
;

This syntax is almost always much faster:

select
*
from
mytable
where
  ARRAY['test'::varchar] <@ my_varchar_array_column
;

(Since this is a performance list after all.)

Re: Database size 1T but unclear why

[Rick Otten]

On Sun, Dec 9, 2018 at 10:19 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:

> Hi,
> I'm trying to understand why my database consume so much space. I checked
> the space it consume on disk :
>
>
Have you tried running pg_repack?  (It is an extension.)

Re: How Do You Associate a Query With its Invoking Procedure?

[Rick Otten]

On Fri, Sep 14, 2018 at 12:34 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Sep 13, 2018 at 12:49 PM, Fd Habash  wrote:
>
>> In API function may invoke 10 queries. Ideally, I would like to know what
>> queries are invoked by it and how long each took.
>>
>>
>>
>> I’m using pg_stat_statement. I can see the API function statement, but
>> how do I deterministically identify all queries invoked by it?
>>
>
> pg_stat_statement is a global tracker that throws away execution context,
> in this case the process id, needed to track the level of detail you
> desire.  I think the best you can do is log all statements and durations to
> the log file and parse that.
>
>
If you have big queries you almost certainly will want to bump your
"track_activity_query_size" value bigger to be able to capture the whole
query.

You are going to have to find the queries in the api source code.  If they
are not distinct enough to easily figure out which was which you can do
things to make them distinct.  One of the easiest things is to add a
"literal" column to the query:

select
  'query_1',
   first_name,
...

Then when you look in the query statements in the database you can see that
literal column and tell which query it was that invoked it.

You can also make them unique by renaming columns:

select
  first_name as 'query1_first_name'
...

Depending on your ORM or whether your api calls queries directly, you could
add comments to the query as well:
select
  -- this one is query 1
  first_name,
...

Unfortunately there is no out of the box "github hook" that can
automatically connect a query from your postgresql logs to the lines of
code in your api.

Re: badly scaling performance with appending to bytea

[Rick Otten]

Can you use a materialized view to do the bytea_agg() and then refresh
concurrently whenever you need updated data?
The refresh concurrently might take a few hours or days to run to keep the
matview up to date, but your queries would be pretty fast.

A possible problem is  that you are running out of memory, so the larger
queries are going to disk.  If you can set up temp space on a faster
volume, or bump up your memory configuration it might help.
ie, work_mem, shared_buffers, and file system cache could all play into
larger aggregations running faster.


On Wed, Mar 21, 2018 at 8:03 AM, Gary Cowell  wrote:

> We are trying to implement postgresql code to load a large object into
> a postgresql bytea in chunks to avoid loading the file into memory in
> the client.
>
> First attempt was to do
>
> update build_attachment set chunk = chunk || newdata ;
>
> this did not scale and got significantly slower after 4000-5000 updates.
>
> The chunks are 4K in size, and I'm testing with a 128MB input file,
> requiring 32,774 chunk updates.
>
> Next, I tried creating an aggregate, thus:
>
> (taken from stackoverflow)
>
> CREATE AGGREGATE bytea_agg(bytea) (SFUNC=byteacat,STYPE=bytea);
>
> changed the code to insert the chunks to a temporary table :
>
> create temporary table build_attachment (seq bigserial primary key,
> chunk bytea ) on commit drop;
>
> we then insert our 4K chunks to this, which takes very little time (20
> seconds for the 32,774 inserts)
>
> Here's an example though of trying to select the aggregate:
>
> gary=> \timing
> Timing is on.
> gary=> select bytea_agg(chunk order by seq) from build_attachment
> where seq < 4000 \g output
> Time: 13372.843 ms
> gary=> select bytea_agg(chunk order by seq) from build_attachment
> where seq < 8000 \g output
> Time: 54447.541 ms
> gary=> select bytea_agg(chunk order by seq) from build_attachment
> where seq < 16000 \g output
> Time: 582219.773 ms
>
> So those partial aggregates completed in somewhat acceptable times but ...
>
> gary=> select bytea_agg(chunk order by seq) from build_attachment
> where seq < 32000 \g output
> this one hadn't completed in an hour - the PostgreSQL connection
> process for my connection on the server goes to 100% CPU and stays
> there, not using much RAM, not doing much IO, oddly
>
> EXPLAINing these aggregate selects doesn't show anything useful.
>
> Am I doomed to not be able to update a bytea this way? Is there some
> way I can tune this?
>
>

Re: effective_io_concurrency on EBS/gp2

[Rick Otten]

On Thu, Feb 8, 2018 at 11:40 AM, Vitaliy Garnashevich <
vgarnashev...@gmail.com> wrote:

> Anyway, there are still some strange things happening when
> effective_io_concurrency is non-zero.
>
> ...
>


> Vitaliy
>
>
I was researching whether I could optimize a concatenated lvm2 volume when
I have disks of different speeds (concatenated - not striped - and I think
I can if I concatenate them in the right order - still testing on that
front), when I came across this article from a few years ago:
http://www.techforce.com.br/content/lvm-raid-xfs-and-ext3-file-systems-tuning-small-files-massive-heavy-load-concurrent-parallel

In the article he talks about the performance of parallel io on different
file systems.

Since I am already running XFS that led me to this tunable:
http://xfs.org/docs/xfsdocs-xml-dev/XFS_Filesystem_Structure/tmp/en-US/html/Allocation_Groups.html

Which brought me back to this discussion about effective_io_concurrency
from a couple of weeks ago.  I noticed that the recent round of tests being
discussed never mentioned the file system used.  Was it XFS?  Does changing
the agcount change the behaviour?

blending fast and temp space volumes

[Rick Otten]

Some of my data processes use large quantities of temp space - 5 or 6T
anyway.

We are running in Google Cloud.  In order to get the best performance out
of all of my queries that might need temp space, I've configured temp space
on a concatenated local (volatile) SSD volume.  In GCE, local SSD's are
more than 20x faster than SAN SSD's in GCE.

side note:  The disadvantage of local SSD is that it won't survive "hitting
the virtual power button" on an instance, nor can it migrate automatically
to other hardware.  (We have to hit the power button to add memory/cpu to
the system, and sometimes the power button might get hit by accident.)
This is OK for temp space.  I never have my database come up automatically
on boot, and I have scripted the entire setup of the temp space volume and
data structures.  I can run that script before starting the database.
 I've done some tests and it seems to work great.  I don't mind rolling
back any transaction that might be in play during a power failure.

So here is the problem:   The largest local SSD configuration I can get in
GCE is 3T.  Since I have processes that sometimes use more than that, I've
configured a second temp space volume on regular SAN SSD.   My hope was
that if a query ran out of temp space on one volume it would spill over
onto the other volume.  Unfortunately it doesn't appear to do that
automatically.  When it hits the 3T limit on the one volume, the query
fails.  :-(

So, the obvious solution is to anticipate which processes will need more
than 3T temp space and then 'set temp_tablespaces' to not use the 3T
volume.  And that is what we'll try next.

Meanwhile, I'd like other processes to "prefer" the fast volume over the
slow one when the space is available.  Ideally I'd like to always use the
fast volume and have the planner know about the different performance
characteristics and capacity of the available temp space volumes and then
choose the best one (speed or size) depending on the query's needs.

I was wondering if there anyone had ideas for how to make that possible.
 I don't think I want to add the SAN disk to the same LVM volume group as
the local disk, but maybe that would work, since I'm already building it
with a script anyhow ... Is LVM smart enough to optimize radically
different disk performances?

At the moment it seems like when multiple temp spaces are available, the
temp spaces are chosen in a 'round robin' or perhaps 'random' fashion.  Is
that true?

I'm meeting with my GCE account rep next week to see if there is any way to
get more than 3T of local SSD, but I'm skeptical it will be available any
time soon.

thoughts?

Re: postgresql 10.1 wrong plan in when using partitions bug

[Rick Otten]

On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Rick Otten <rottenwindf...@gmail.com> writes:
> > I'm wrestling with a very similar problem too - except instead of
> official
> > partitions I have a views on top of a bunch (50+) of unioned materialized
> > views, each "partition" with 10M - 100M rows.  On 9.6.6 the queries would
> > use the indexes on each materialized view.  On 10.1, every materialized
> > view is sequence scanned.
>
> Can you post a self-contained example of this behavior?  My gut reaction
> is that the changes for the partitioning feature broke some optimization
> that used to work ... but it could easily be something else, too.  Hard
> to say with nothing concrete to look at.
>
>
I think it is worth trying to reproduce in an example.  I'll try to cook
something up that illustrates it.  It should be doable.



> > I'm mostly hoping with fingers crossed that something in 10.2, which is
> > coming out next week, fixes it.
>
> If you'd reported this in suitable detail awhile ago, we might have been
> able to fix it for 10.2.  At this point, with barely 30 hours remaining
> before the planned release wrap, it's unlikely that anything but the most
> trivial fixes could get done in time.
>
>
I wish I could move faster on identifying and reporting this sort of thing.

We only cut over to 10.1 about 2 weeks ago and didn't discover the issue
until we'd been running for a few days (and eliminated everything else we
could think of - including the bug that is fixed in 10.2 that crashes some
queries when they have parallel gather enabled).

My hope is that 10.2 will fix our issue "by accident" rather than on
purpose.

I'll try to build a test case this afternoon.

--

I use a view on top of the materialized views so I can swap them in and out
with a "create or replace" that doesn't disrupt downstream depndencies.

I'm currently thinking to work around this issue for the short term, I need
to build a mat view on top of the mat views, and then put my view on top of
that (so I can swap out the big matview without disrupting downstream
dependencies).  It means a lot more disk will be needed, and moving
partitions around will be much less elegant, but I can live with that if it
fixes the performance problems caused by the sequence scanning.  Hopefully
the planner will use the indexes on the "big" materialized view.

I'm going to try that hack this afternoon too.

I was going to blog about this approach of using a view to do partitioning
of materialized views, but I'm not sure when I'll ever get to it.  It was
this list that originally gave me the idea to try this approach.  The
partiions are actually materialized views of foreign tables from a Hadoop
cluster.

FWIW, here is the function that builds the view:

---
create or replace function treasure_data."relinkMyView"()
returns varchar
security definer
as
$$
declare
wrMatView  varchar;
fromString text;
begin

for wrMatView in

select
c.relname
from
pg_class c
join pg_namespace n on c.relnamespace = n.oid
where
c.relkind = 'm'
and
n.nspname = 'myschema'
and
c.relname ~ 'my_matview_partition_\d\d\d\d_\d\d$'
order by
c.relname

loop

if length(fromString) > 0 then
fromString := format ('%s union all select * from myschema.%I',
fromString, wrMatView);
else
fromString := format ('select * from myschema.%I', wrMatView);
end if;

end loop;

execute format ('create or replace view myschema.my_view as %s',
fromString);

grant select on myschema.my_view to some_read_only_role;
grant select on myschema.my_view to some_read_write_role;

return format ('create or replace view myschema.my_view as %s',
fromString);

end
$$ language plpgsql
;

---

To swap a partition out, I rename it to something that does not conform to
the regex pattern above, and then run the function.
To swap a partition in, I rename it to something that does conform to the
regex pattern, and then run the function.

(of course, that is mostly automated, but it works by hand too)

This has been working great for us until we jumped to PG 10, when suddenly
I can't get the planner to use the indexes in the partitions any more.

Re: postgresql 10.1 wrong plan in when using partitions bug

[Rick Otten]

On Sun, Feb 4, 2018 at 8:19 AM, legrand legrand  wrote:

> What is the value of guc constrain_exclusion ?
>
>
>
In my use case, which is a big union all behind a view, setting this to
off, on, or partition makes no difference.  It still sequence scans all of
the sub-tables in pg 10.1 whereas it used the indexes in 9.6.

Re: postgresql 10.1 wrong plan in when using partitions bug

[Rick Otten]

On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:

>
> Hi,
> I configured range partitions on a date column of my main table(log_full).
> Each partition represents a day in the month. Every day partition has a
> list parition of 4 tables on a text column.
>
> log_full
>   log_full_01_11_2017  -->
>   log_full _01_11_2017_x1
>   log_full _01_11_2017_x2
>   log_full _01_11_2017_x3
>   log_full _01_11_2017_x4
> log_full_02_11_2017
>   log_full _02_11_2017_x1
>   log_full _02_11_2017_x2
>   log_full _02_11_2017_x3
>   log_full _02_11_2017_x4
>
> and so on
>
>
> The date column consist of date in the next format : -MM-DD HH:24:SS
> for example : 2017-11-01 00:01:40
>
> I wanted to check the plan that I'm getting for a query that is using the
> date column and it seems that the planner choose to do seq scans on all
> tables.
>
> -Each partition consist from 15M rows.
> I have about 120 partitions.
>
> The query :
> explain select count(*) from log_full where end_date between
> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
>
> The output is too long but it do full scans on all paritions...
> any idea what can be the problem? Is it connected to the date format ?
>
> Thanks , Mariel.
>

I'm wrestling with a very similar problem too - except instead of official
partitions I have a views on top of a bunch (50+) of unioned materialized
views, each "partition" with 10M - 100M rows.  On 9.6.6 the queries would
use the indexes on each materialized view.  On 10.1, every materialized
view is sequence scanned.  (Killing the performance of many queries.)  I
have 4 or 5 sets of materialized views organized this way with views on top
of them.

I've checked for invalid indexes.

I've done Analyze, and Vaccuum Analyze on all sub-materialized views.

I've reindexed the materialized views.

I've experimented with geqo tunables.
I've experimented with  turning parallel gather off and on and setting it
to different levels.
I've tried setting random page cost very high, and very low.
I tried turning nested loops on and off.
I tried setting effective_cache_size very small.

None of the various queries using these views on top of my hand constructed
"partitions" are using indexes.

All of the exact same queries used the indexes in 9.6.6 before the
upgrade.  Without the indexes, hitting these 1B+ row aggregate tables I'm
seeing a 10x to 100x slowdown since upgrading.  This is killing us.

Not only that but with 50 tables under the view, and each one getting a
parallel sequence scan, it is kind of impressive how much CPU one of these
queries can use at once.

I'm mostly hoping with fingers crossed that something in 10.2, which is
coming out next week, fixes it.  I was planning on posting my dilemma to
this list this morning since I'm running out of ideas.  I really need to
fix the issue this weekend to meet some business deadlines for data
processing early in the week.  So my other hail mary pass this weekend,
besides seeking ideas on this list, was to see if I could bump my version
to 10.2 early.  (I'm not sure how to do that since I've been using Ubuntu
packages and waiting for official releases prior to now, but I'm sure I can
figure it out.)

PG 10 hash index create times

[Rick Otten]

Since upgrading to PG 10 a few weeks ago I've been experimenting with hash
indexes.  One thing I've noticed is that they seem to take a _lot_ longer
to create than btree indexes, particularly on large tables.

I've got a moderately sized table of about 38M rows and the create index
using hash for an integer column (with about 300 unique values) has been
running for 12 hours now and still hasn't finished.  I have not
successfully installed a hash index on a larger table (of which I have
many) yet because the create index never seems to finish.

The create index thread will consume an entire CPU while doing this.  It
does not seem to be I/O bound.  It just crunches away burning cpu cycles
with no apparent end.

Is expected?

primary key hash index

[Rick Otten]

After reading this article about keys in relational databases, highlighted
on hacker news this morning:
https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html

I keep pondering the performance chart, regarding uuid insert, shown
towards the bottom of the article.  I believe he was doing that test with
PostgreSQL.

My understanding is that the performance is degrading because he has a
btree primary key index.  Is it possible to try a hash index or some other
index type for a uuid primary key that would mitigate the performance issue
he is recording?

After all, I can't think of any use case where I query for a "range" of
uuid values.  They are always exact matches.  So a hash index would
possibly be a really good fit.

I have many tables, several with more than 1 billion rows, that use uuid's
as the primary key.  Many of those uuid's are generated off system, so I
can't play around with the uuid generation algorithm like he was doing.

I'm hoping to move to PG 10 any day now, and can migrate the data with
updated index definitions if it will actually help performance in any way.
(I'm always looking for ways to tweak the performance for the better any
chance I get.)

Re: Batch insert heavily affecting query performance.

[Rick Otten]

On Wed, Dec 27, 2017 at 10:13 AM, Jean Baro  wrote:

> Hello,
>
> We are still seeing queries  (by UserID + UserCountry) taking over 2
> seconds, even when there is no batch insert going on at the same time.
>
> Each query returns from 100 to 200 messagens, which would be a 400kb pay
> load, which is super tiny.
>
> I don't know what else I can do with the limitations (m4.large), 167MM
> rows, almost 500GB database and 29GB of indexes (all indexes).
>
> I am probably to optimistic, but I was expecting queries (up to 50 queries
> per second) to return  (99th) under 500ms or even less, as the index is
> simple, there is no aggregation or join involves.
>

> Any suggestion?
>


Although you aren't querying by it, if your id column is actually a UUID,
as a best practice I strongly recommend switching the column type to uuid.
If you do query by the primary key, a uuid query will be much faster than a
char or varchar column query.

You'll need to submit a more complete explain plan than what you have below.
  Try using:
   explain (analyze, costs, verbose, buffers) select ...



> The table structure:
> CREATE TABLE public.card
> (
> id character(36) NOT NULL,
> user_id character varying(40) NOT NULL,
> user_country character(2) NOT NULL,
> user_channel character varying(40),
> user_role character varying(40),
> created_by_system_key character(36) NOT NULL,
> created_by_username character varying(40),
> created_at timestamp with time zone NOT NULL,
> last_modified_at timestamp with time zone NOT NULL,
> date_start timestamp with time zone NOT NULL,
> date_end timestamp with time zone NOT NULL,
> payload json NOT NULL,
> tags character varying(500),
> menu character varying(50),
> deleted boolean NOT NULL,
> campaign character varying(500) NOT NULL,
> correlation_id character varying(50),
> PRIMARY KEY (id)
> );
>
> CREATE INDEX idx_user_country
> ON public.card USING btree
> (user_id COLLATE pg_catalog."default", user_country COLLATE
> pg_catalog."default");
>
> CREATE INDEX idx_last_modified_at
> ON public.card USING btree
> (last_modified_at ASC NULLS LAST);
>
> CREATE INDEX idx_campaign
> ON public.card USING btree
> (campaign ASC NULLS LAST)
>
> The EXPLAIN
>
> 'Index Scan using idx_user_country on card  (cost=0.57..1854.66 rows=460
> width=922)'
> '  Index Cond: (((user_id)::text = '4684'::text) AND (user_country =
> 'BR'::bpchar))'
>
>
>
> Em 25 de dez de 2017 01:10, "Jean Baro"  escreveu:
>
>> Thanks for the clarification guys.
>>
>> It will be super useful. After trying this I'll post the results!
>>
>> Merry Christmas!
>>
>> Em 25 de dez de 2017 00:59, "Danylo Hlynskyi" 
>> escreveu:
>>
>>> I had an opportunity to perform insertion of 700MM rows into Aurora
>>> Postgresql, for which performance insights are available. Turns out, that
>>> there are two stages of insert slowdown - first happens when max WAL
>>> buffers limit reached, second happens around 1 hour after.
>>>
>>> The first stage cuts insert performance twice, and WALWrite lock is main
>>> bottleneck. I think WAL just can't sync changes log that fast, so it waits
>>> while older log entries are flushed. This creates both read and write IO.
>>>
>>> The second stage is unique to Aurora/RDS and is characterized by
>>> excessive read data locks and total read IO. I couldn't figure out why does
>>> it read so much in a write only process, and AWS support didn't answer yet.
>>>
>>> So, for you, try to throttle inserts so WAL is never overfilled and you
>>> don't experience WALWrite locks, and then increase wal buffers to max.
>>>
>>> 24 груд. 2017 р. 21:51 "Jean Baro"  пише:
>>>
>>> Hi there,
>>>
>>> We are testing a new application to try to find performance issues.
>>>
>>> AWS RDS m4.large 500GB storage (SSD)
>>>
>>> One table only, called Messages:
>>>
>>> Uuid
>>> Country  (ISO)
>>> Role (Text)
>>> User id  (Text)
>>> GroupId (integer)
>>> Channel (text)
>>> Title (Text)
>>> Payload (JSON, up to 20kb)
>>> Starts_in (UTC)
>>> Expires_in (UTC)
>>> Seen (boolean)
>>> Deleted (boolean)
>>> LastUpdate (UTC)
>>> Created_by (UTC)
>>> Created_in (UTC)
>>>
>>> Indexes:
>>>
>>> UUID (PK)
>>> UserID + Country (main index)
>>> LastUpdate
>>> GroupID
>>>
>>>
>>> We inserted 160MM rows, around 2KB each. No partitioning.
>>>
>>> Insert started at around  3.000 inserts per second, but (as expected)
>>> started to slow down as the number of rows increased.  In the end we got
>>> around 500 inserts per second.
>>>
>>> Queries by Userd_ID + Country took less than 2 seconds, but while the
>>> batch insert was running the queries took over 20 seconds!!!
>>>
>>> We had 20 Lambda getting messages from SQS and bulk inserting them into
>>> Postgresql.
>>>
>>> The insert performance is important, but we would slow it down if needed
>>> in order to ensure a more flat query performance. (Below 2