Re: Default ordering option
Adrian:
Are you really looking for a pseudo-random name?
No, the code I pasted was an existing production bug: the last_name should
have been unique, so the selected patient would always be the same.
This should have been detected in tests, but since the order was "almost
always the same", our test was green 99% of the time, so we discarded it as
flaky.
Fuzzy testing could be an option, but this would go too far, as for Peter
extension suggestion.
We have huge existing codebase with more than 10K tests, and I do not want
to modify our whole testing strategy.
Meanwhile, I went for an ORM patch (ActiveRecord) and forbid usages that
can workaround it.
If you are interested, here is a gist:
https://gist.github.com/cyrilchampier/fdb945e8a09f93d50c7e89305c2f53f0
I wish there was a simple flag to activate in PostgreSQL to do that!
Thanks for your ideas!
On Thu, Jul 25, 2019 at 4:55 PM Adrian Klaver
wrote:
> On 7/25/19 12:43 AM, Cyril Champier wrote:
> > *Adrian*:
> >
> > If order is not an issue in the production code why test for it in
> the
> > test code?
> >
> >
> > In many cases, it would not be a problem in tests if we had an unordered
> > array comparison helper.
> > But in other cases, it is a production issue.
> > In ruby ActiveRecord for exemple, you can do `Patient.find_by(last_name:
> > 'champier')`,
> > which translates to `SELECT "patients".* FROM "patients" WHERE
> > "patients"."last_name" = 'champier' LIMIT 1`.
> > If last_name is not unique, the returned record will be random.
>
> Are you really looking for a pseudo-random name?
>
> If so would not(warning not a Ruby developer, so below is tentative):
>
> Patient.where(["last_name = :last_name", {last_name:
> "champier"}]).order('RANDOM()').first
>
> work better?
>
> If not why not use something that returns all possible matches?
>
> >
> > So yes, everything as to be randomized, because the sources are
> > multiples and the consequences can vary to a dramatic production bug, a
> > failed CI 1% of the time, or to a useless test assertion.
>
> One way I can think of doing this is write a script that walks through
> your tables in the test db and does an UPDATE across the rows. It is
> going to add time to your tests, but then I believe that is going to be
> the case for anything you do. Or you could look at something I have
> never tried, fuzzy testing. As a starting point:
>
> https://www.guru99.com/fuzz-testing.html
>
> Maybe other folks have suggestions on tools you could use for fuzzy
> testing.
>
>
> >
> >
> > *Peter*:
> >
> > It might be an interesting exercise to implement this as a
> post-parsing
> > hook.
> >
> > I known nothing about that, but that sounds interesting, do you have any
> > documentation pointer to help me implement that?
> >
> >
> >
> > On Wed, Jul 24, 2019 at 10:36 PM Peter Eisentraut
> > > > wrote:
> >
> > On 2019-07-23 17:43, Cyril Champier wrote:
> > > In this documentation
> > > , it is
> said:
> > >
> > > If sorting is not chosen, the rows will be returned in an
> > > unspecified order. The actual order in that case will depend
> > on the
> > > scan and join plan types and the order on disk, but it must
> > not be
> > > relied on.
> > >
> > >
> > > I would like to know if there is any way to change that to have a
> > "real"
> > > random behaviour.
> >
> > It might be an interesting exercise to implement this as a
> post-parsing
> > hook.
> >
> > --
> > Peter Eisentraut http://www.2ndQuadrant.com/
> > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
Re: Too slow to create new schema and their tables, functions, triggers.
On Fri, Jul 26, 2019 at 1:01 AM PegoraroF10 wrote: > > Nope, no one message near those statements. > I haven´t changed anything on Postgres.conf related with autovacuum. > Please take a look and post results of the following query: select name, setting from pg_settings where name like 'autovacuum%'; any chance autovacuum is stopped?
Re: Default ordering option
On Fri, Jul 26, 2019 at 9:53 AM Cyril Champier wrote: > > Adrian: > >> Are you really looking for a pseudo-random name? > > > No, the code I pasted was an existing production bug: the last_name should > have been unique, so the selected patient would always be the same. > This should have been detected in tests, but since the order was "almost > always the same", our test was green 99% of the time, so we discarded it as > flaky. If the filter should return at most 1 row, why put a LIMIT in the first place? Even with a forced random() you won't get a failure every time, while asserting there's at most 1 row returned is guaranteed to fail?
Re: Default ordering option
Julien, Because it's production code generated by our ORM for this command: `Patient.find_by(last_name: 'champier')`. Of course this was not intended by the developer that though the last_name was unique. On Fri, Jul 26, 2019 at 10:10 AM Julien Rouhaud wrote: > On Fri, Jul 26, 2019 at 9:53 AM Cyril Champier > wrote: > > > > Adrian: > > > >> Are you really looking for a pseudo-random name? > > > > > > No, the code I pasted was an existing production bug: the last_name > should have been unique, so the selected patient would always be the same. > > This should have been detected in tests, but since the order was "almost > always the same", our test was green 99% of the time, so we discarded it as > flaky. > > If the filter should return at most 1 row, why put a LIMIT in the > first place? Even with a forced random() you won't get a failure > every time, while asserting there's at most 1 row returned is > guaranteed to fail? >
Re: Too slow to create new schema and their tables, functions, triggers.
PegoraroF10 schrieb am 26.07.2019 um 00:16: > statement: create or replace function valoresdfe... 0 mins 1.135 secs > statement: create or replace function dadosorigem...0 mins 0.055 secs > statement: CREATE OR REPLACE FUNCTION SONU... 0 mins 0.013 secs > statement: create or replace function contatoscampa...2 mins 13.492 > secs > statement: create or replace function FORMATARTELEF... 0 mins 0.013 secs > statement: create or replace function ChecaVar 0 mins 0.012 secs > statement: CREATE or replace FUNCTION criatrigge... 1 mins 16.42 secs > Is it possible those functions (were creating was slow) were still in use by another session and the create script had to wait for an exclusive lock to replace the function?
Re: Too slow to create new schema and their tables, functions, triggers.
Running that sql: namesetting autovacuum on autovacuum_analyze_scale_factor 0.1 autovacuum_analyze_threshold50 autovacuum_freeze_max_age 2 autovacuum_max_workers 3 autovacuum_multixact_freeze_max_age 4 autovacuum_naptime 60 autovacuum_vacuum_cost_delay20 autovacuum_vacuum_cost_limit-1 autovacuum_vacuum_scale_factor 0.2 autovacuum_vacuum_threshold 50 autovacuum_work_mem -1 -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Too slow to create new schema and their tables, functions, triggers.
Nope, that schema and all its entire structure did not exist. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Too slow to create new schema and their tables, functions, triggers.
select count(*), count(*) filter (where last_autovacuum is not null) from pg_stat_all_tables count count 36605 1178 But what tables should I see if vacuum ran on it ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Running concurrent txns and measuring the timings in Postgres
On 25/7/19 5:24 AM, Souvik Bhattacherjee wrote:
> I got this thing running and hopefully works as expected. The txns are
> stored in insert_txn1.sql, insert_txn2.sql, ...
> Please let me know if you find any issues with this.
> Script is attached.
>
Even if you're using the ancient Bash version 3 (AFAIK only macOS still
uses it out of the box), about half the lines are unnecessary:
#!/bin/bash
SECONDS=0
for i in {1..4}
do
psql -d mydb -f insert_txn${i}.sql &
done
wait
echo "Elapsed time: $SECONDS secs"
Read the bash man page to understand that SECONDS "magic", and why I
didn't bother with PIDs at all. I also fixed a variable-dereferencing
bug in your original script ("insert_txn[$i].sql" literally expands to
"insert_txn[1].sql", etc.)
Best Regards,
Adrian
Re: Too slow to create new schema and their tables, functions, triggers.
On 7/26/19 4:15 AM, PegoraroF10 wrote: select count(*), count(*) filter (where last_autovacuum is not null) from pg_stat_all_tables count count 36605 1178 But what tables should I see if vacuum ran on it ? I would recommend reading this: https://www.postgresql.org/docs/11/routine-vacuuming.html and in particular: https://www.postgresql.org/docs/11/routine-vacuuming.html#AUTOVACUUM For now look at the actual values of last_autovacuum to see how current the autovacuuming is. My guess is that the issues you are having has to do with bloat in the system tables, so I would start there. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
Re: Too slow to create new schema and their tables, functions, triggers.
On Fri, Jul 26, 2019 at 1:15 PM PegoraroF10 wrote: > > select count(*), count(*) filter (where last_autovacuum is not null) from > pg_stat_all_tables > count count > 36605 1178 > What are the results of the same query against pg_stat_sys_tables and pg_stat_user_tables? That's would help understanding which set of tables are not being vacuumed. Luca
Re: Too slow to create new schema and their tables, functions, triggers.
pg_stat_all_tables count count 37158 807 pg_stat_sys_tables count count 16609 223 pg_stat_user_tables count count 20549 584 -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Too slow to create new schema and their tables, functions, triggers.
Correct, seems to be something wrong on system tables. Maybe our script is running something is already there, we will check. select * from pg_stat_sys_tables where schemaname = 'pg_catalog' relname n_live_tup n_dead_tup pg_attrdef 3699 1095 pg_index 4756 1183 pg_sequence 20827 1482 pg_statistic 171699 27101 pg_trigger221319 20718 pg_shdepend225017 22337 pg_attribute 883023 164153 pg_depend 1553586 142960 and all them last_vacuum is null and last_autovacuum is too old. So, is it better to configure autovacuum properly to these tables or should I run vacuum periodically ? Obviously I´ll check our script too. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Hardware for writing/updating 12,000,000 rows per hour
Would it be possible to achieve 12,000,000 writes/updates on a single server? If so what kind of hardware should I be looking for?
Re: Hardware for writing/updating 12,000,000 rows per hour
On 7/26/19 2:56 PM, Arya F wrote: Would it be possible to achieve 12,000,000 writes/updates on a single server? If so what kind of hardware should I be looking for? That's only 3,333 modifications/second. How big are your records? -- Angular momentum makes the world go 'round.
Re: Hardware for writing/updating 12,000,000 rows per hour
As most about 2000 characters. On Fri, Jul 26, 2019 at 3:03 PM Ron wrote: > On 7/26/19 2:56 PM, Arya F wrote: > > Would it be possible to achieve 12,000,000 writes/updates on a single > > server? If so what kind of hardware should I be looking for? > > That's only 3,333 modifications/second. How big are your records? > > -- > Angular momentum makes the world go 'round. > > >
Re: Hardware for writing/updating 12,000,000 rows per hour
[ please don't top-post ] Arya F writes: > On Fri, Jul 26, 2019 at 3:03 PM Ron wrote: >> On 7/26/19 2:56 PM, Arya F wrote: >>> Would it be possible to achieve 12,000,000 writes/updates on a single >>> server? If so what kind of hardware should I be looking for? >> That's only 3,333 modifications/second. How big are your records? > As most about 2000 characters. Do you need 3K independent commits per second? Or can you batch them? Even just turning off synchronous_commit would move the goalposts pretty far in terms of the storage hardware you'll need for this. regards, tom lane
Re: Hardware for writing/updating 12,000,000 rows per hour
On Fri, Jul 26, 2019 at 3:24 PM Tom Lane wrote: > [ please don't top-post ] > > Arya F writes: > > On Fri, Jul 26, 2019 at 3:03 PM Ron wrote: > >> On 7/26/19 2:56 PM, Arya F wrote: > >>> Would it be possible to achieve 12,000,000 writes/updates on a single > >>> server? If so what kind of hardware should I be looking for? > > >> That's only 3,333 modifications/second. How big are your records? > > > As most about 2000 characters. > > Do you need 3K independent commits per second? Or can you batch them? > Even just turning off synchronous_commit would move the goalposts > pretty far in terms of the storage hardware you'll need for this. > > regards, tom lane > I think I can modify my application to do a batch update. Right now the server has an HDD and it really can't handle a lot of updates and inserts per second. Would changing to a regular SSD be able to easily do 3000 updates per second?
Re: Hardware for writing/updating 12,000,000 rows per hour
On 2019-Jul-26, Arya F wrote: > I think I can modify my application to do a batch update. Right now the > server has an HDD and it really can't handle a lot of updates and inserts > per second. Would changing to a regular SSD be able to easily do 3000 > updates per second? That's a pretty hard question in isolation -- you need to consider how many indexes are there to update, whether the updated columns are indexed or not, what the datatypes are, how much locality of access you'll have ... I'm probably missing some other important factors. (Of course, you'll have to tune various PG server settings to find your sweet spot.) I suggest that should be measuring instead of trying to guess. A reasonably cheap way is to rent a machine somewhere with the type of hardware you think you'll need, and run your workload there for long enough, making sure to carefully observe important metrics such as table size, accumulated bloat, checkpoint regime, overall I/O activity, and so on. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: DRY up GUI wiki pages
On 2019-Jul-10, Steve Atkins wrote: > > On Jul 10, 2019, at 7:38 PM, Bruce Momjian wrote: > >> I'd like to DRY them up so there aren't two lists which confuses > >> newcomers. Any objections? If not I'll probably make one of those > >> pages into GUI's and one into "non GUI's" or something like that. > > I created the first one because the second one was full of old, stale, > useless things. I believe that everything valid on the second one was > added to the first one at the time. I think we should delete the obsolete pages; maybe make them redirects to the ones that have been kept up to date. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
