Re: performance bottlenecks on lock transactionid

2019-08-14 Thread Laurenz Albe 
王若楠 wrote:
> We used benchmarksql 4.1.0 to test the performance of PG12 beta TPCC.
> We found performance bottlenecks on lock transactionid.

You included an attachment with results from the "pg_locks" view
where "granted" is FALSE for all entries.

I'll assume that these are not *all* the entries in the view, right?

Since the locks are waiting for different transaction IDs, I'd
assume that this is just a case of contention: many transactions are
trying to modify the same rows concurrently.

This is to be expected.
Perhaps your benchmark is running with too many connections on
too few table rows?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

re:Re: performance bottlenecks on lock transactionid

2019-08-14 Thread 王若楠 
hello,Laurenz Albe

Yes, pg_locks is only an item that does not get a lock in the view. The test data is 300 warehouses connections, and the CPU is only about 60%. I think the lock becomes a performance bottleneck at this time. I want to find a way to reduce the lock waiting and improve the performance.
 

-- 原始邮件 --
发件人: "Laurenz Albe" https://www.cybertec-postgresql.com

Re: Re: performance bottlenecks on lock transactionid

2019-08-14 Thread Laurenz Albe 
王若楠 wrote:
> I want to find a way to reduce the lock waiting and improve the
> performance.

You either have to make the transactions shorter, or you let the
different clients modify different rows, so that they don't lock each
other.

That concurrent writers on the same data lock each other is
unavoidable, and all database management systems I know do it the same
way.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

RE: ORA-24345: A Truncation or null fetch error occurred -ora2pg

2019-08-14 Thread Daulat Ram 
Hi Adrian ,

We have the below output. What we need to change. 

bash-4.2$ ora2pg -c ora2pg.bidder.conf -t SHOW_ENCODING

Current encoding settings that will be used by Ora2Pg:
Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
Oracle NLS_NCHAR AL32UTF8
Oracle NLS_TIMESTAMP_FORMAT -MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT -MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING UTF8
Perl output encoding ''
Showing current Oracle encoding and possible PostgreSQL client encoding:
Oracle NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
Oracle NLS_NCHAR WE8MSWIN1252
Oracle NLS_TIMESTAMP_FORMAT -MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT -MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING WIN1252
bash-4.2$

thanks

 
-Original Message-
From: Adrian Klaver  
Sent: Tuesday, August 13, 2019 11:27 PM
To: Daulat Ram ; Luca Ferrari 
Cc: [email protected]; [email protected]
Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg

On 8/13/19 10:34 AM, Daulat Ram wrote:
> H,
> 
> We are using  below the ora2pg version and the data types for tables.
> 
> bash-4.2$ ora2pg -v
> Ora2Pg v20.0
> bash-4.2$
> 
> SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP;
> 
> DATA_TYPE
> 
> TIMESTAMP(6)
> FLOAT
> CLOB
> NUMBER
> CHAR
> DATE
> VARCHAR2
> BLOB
> 
> SQL>
> 
> We are getting the same issue for tables which are having blob, clob and char 
> data types.

The ora2pg issue below seems to have more information on this:

https://github.com/darold/ora2pg/issues/342

> 
> Thanks,
> Daulat
> 
> -Original Message-
> From: Luca Ferrari 
> Sent: Tuesday, August 13, 2019 8:32 PM
> To: Daulat Ram 
> Cc: [email protected]; [email protected]
> Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg
> 
> On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram  
> wrote:
>> Initially did not have LongReadLen set, so I thought this was the cause. 
>> But, I have set LongReadLen, on the db handle, equal to 9000.
> 
> Apparently this is an oracle problem because it acceppted data longer than 
> its type, so my guess would be that in your table you have a
> char(n) column that could be enlarged before the migration.
> 
> Hope this helps.
> And please report the version of ora2pg when asking for help.
> 
> Luca
> 


-- 
Adrian Klaver
[email protected]