TOAST table performance problem

2020-02-07 Thread Asya Nevra Buyuksoy 
Hello everybody,



I have trouble with my table that has four columns which their data types
are text, JSON, boolean and timestamp.

Also, I have 1K rows, but my JSON column size approximately 110KB and maybe
over it.

When I select all the data from my table, it takes 600 seconds.

But I explain my query;





*Seq Scan on zamazin  (cost=0.00..21.77 rows=1077 width=49) (actual
time=0.004..0.112 rows=1077 loops=1)*

*Planning time: 0.013 ms*

*Execution time: 0.194 ms*





When I investigated why these execution times are so different, I find a
new storage logic like TOAST.

I overlook some details on TOAST logic and  increased some config like
shared_buffers, work_mem, maintenance_work_mem, max_file_per_process.

But there was no performance improvement on my query.



I do not understand why it happens. My table size is 168 MB, but my TOAST
table size that is related to that table,  is 123 MB.



*My environment is;*

PostgreSQL 9.4.1

Windows Server 2012 R2

16 GB RAM

100 GB HardDisk (Not SSD)

My database size 20 GB.



*My server configuration ;*

Shared_buffers: 8GB



( If I understand correctly, PostgreSQL says, For 9.4 The useful range for
shared_buffers on Windows systems is generally from 64MB to 512MB. Link:
https://www.postgresql.org/docs/9.4/runtime-config-resource.html )



work_mem : 512 MB

maintenance_work_mem: 1GB

max_file_per_process: 1

effective_cache_size: 8GB



How I can achieve good performance?

Regards,

Mustafa BÜYÜKSOY

Sv: TOAST table performance problem

2020-02-07 Thread Andreas Joseph Krogh 

På fredag 07. februar 2020 kl. 14:07:28, skrev Asya Nevra Buyuksoy <
[email protected] >: 

Hello everybody,

[...]

How I can achieve good performance?


Nobody here understands anything unless you show the exact query and schema... 

And of course you'll be encurraged to upgrade to latest version (12.1) as 
9.4.1 is now 5 years old.. 


--
 Andreas Joseph Krogh

Re: TOAST table performance problem

2020-02-07 Thread Asya Nevra Buyuksoy 
Sorry for the misunderstanding.
I have a table like;
CREATE TABLE zamazin
(
  paramuser_id text,
  paramperson_id integer,
  paramdata json,
  paramisdeleted boolean,
  paramactiontime timestamp without time zone
)
paramdata row size is 110KB and over.

When I execute this query like;
*select * from zamazin*
it takes *600 seconds*.
But when analyze the query ;




*"Seq Scan on public.zamazin  (cost=0.00..21.77 rows=1077 width=49) (actual
time=0.008..0.151 rows=1077 loops=1)""  Output: paramuser_id,
paramperson_id, paramdata, paramisdeleted, paramactiontime""  Buffers:
shared hit=11""Planning time: 0.032 ms""Execution time: 0.236 ms"*
 Why the query takes a long time, I do not understand. I assume that this
relates to the TOAST structure.

png.png
(11K)


Andreas Joseph Krogh , 7 Şub 2020 Cum, 16:12 tarihinde
şunu yazdı:

> På fredag 07. februar 2020 kl. 14:07:28, skrev Asya Nevra Buyuksoy <
> [email protected]>:
>
> Hello everybody,
>
> [...]
>
> How I can achieve good performance?
>
>
> Nobody here understands anything unless you show the exact query and
> schema...
>
> And of course you'll be encurraged to upgrade to latest version (12.1) as
> 9.4.1 is now 5 years old..
>
> --
> Andreas Joseph Krogh
>

Re: TOAST table performance problem

2020-02-07 Thread Asya Nevra Buyuksoy 
>
> Andreas Joseph Krogh , 7 Şub 2020 Cum, 16:12
> tarihinde şunu yazdı:
>
>> På fredag 07. februar 2020 kl. 14:07:28, skrev Asya Nevra Buyuksoy <
>> [email protected]>:
>>
>>
>>
>> *[...]*
>>
>> *And of course you'll be encurraged to upgrade to latest version (12.1)
>> as 9.4.1 is now 5 years old..*
>>   You are right but for now I have to use this version :)
>>
> --
>> Andreas Joseph Krogh
>>
>

Re: TOAST table performance problem

2020-02-07 Thread Andreas Joseph Krogh 

På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy <
[email protected] >: 

Sorry for the misunderstanding. 
I have a table like; 
CREATE TABLE zamazin
 (
 paramuser_id text,
 paramperson_id integer,
 paramdata json,
 paramisdeleted boolean,
 paramactiontime timestamp without time zone
 ) 
paramdata row size is 110KB and over. 

When I execute this query like;
select * from zamazin 
it takes 600 seconds. 
But when analyze the query ; 
"Seq Scan on public.zamazin (cost=0.00..21.77 rows=1077 width=49) (actual 
time=0.008..0.151 rows=1077 loops=1)"
 " Output: paramuser_id, paramperson_id, paramdata, paramisdeleted, 
paramactiontime"
 " Buffers: shared hit=11"
 "Planning time: 0.032 ms"
 "Execution time: 0.236 ms" 
 Why the query takes a long time, I do not understand. I assume that this 
relates to the TOAST structure. 

My guess is the time is spent in the client retrieving the data, not in the DB 
itself. Are you on a slow network? 


-- 
Andreas Joseph Krogh 
CTO / Partner - Visena AS 
Mobile: +47 909 56 963 
[email protected]  
www.visena.com

Re: TOAST table performance problem

2020-02-07 Thread MichaelDBA 
Yes, I would concur that this planning time and execution time do not 
take into account the network time sending the data back to the client, 
especially since your are sending back the entire contents of the table.


Regards,
Michael Vitale

Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM:
På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy 
mailto:[email protected]>>:


Sorry for the misunderstanding.
I have a table like;
CREATE TABLE zamazin
(
  paramuser_id text,
  paramperson_id integer,
  paramdata json,
  paramisdeleted boolean,
  paramactiontime timestamp without time zone
)
paramdata row size is 110KB and over.
When I execute this query like;
*select * from zamazin*
it takes *600 seconds*.
But when analyze the query ;
*"Seq Scan on public.zamazin  (cost=0.00..21.77 rows=1077
width=49) (actual time=0.008..0.151 rows=1077 loops=1)"
"  Output: paramuser_id, paramperson_id, paramdata,
paramisdeleted, paramactiontime"
"  Buffers: shared hit=11"
"Planning time: 0.032 ms"
"Execution time: 0.236 ms"*
 Why the query takes a long time, I do not understand. I
assume that this relates to the TOAST structure.

My guess is the time is spent in the /client/ retrieving the data, not 
in the DB itself. Are you on a slow network?

--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
[email protected] 
www.visena.com

Re: TOAST table performance problem

2020-02-07 Thread Asya Nevra Buyuksoy 
>
>
> Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM:
>
> På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy <
> [email protected]>:
>
>
> My guess is the time is spent in the *client* retrieving the data, not in
> the DB itself. Are you on a slow network?
>
>It works in my local area and speed is 1 Gbps. When I use
another local computer that has SSD disk the query execution time reduced
to 12 seconds. But this query has to execute my local computer.

Re: TOAST table performance problem

2020-02-07 Thread Andreas Joseph Krogh 

På fredag 07. februar 2020 kl. 15:16:13, skrev Asya Nevra Buyuksoy <
[email protected] >: 


Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM: 
På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy <
[email protected] >: 


My guess is the time is spent in the client retrieving the data, not in the DB 
itself. Are you on a slow network? 
 It works in my local area and speed is 1 Gbps. When I use another local 
computer that has SSD disk the query execution time reduced to 12 seconds. But 
this query has to execute my local computer. 

What client are you using? 


--
 Andreas Joseph Krogh

Re: TOAST table performance problem

2020-02-07 Thread Andreas Joseph Krogh 

På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <
[email protected] >: 
I use pgadmin3. 

Try "psql", it has the lowest overhead (I think). pgAdmin might use time 
presenting the results etc. which is easy to overlook. 


-- 
Andreas Joseph Krogh

Re: TOAST table performance problem

2020-02-07 Thread Asya Nevra Buyuksoy 
I try it, but there is no enhancement.
I read this link is about TOAST and also its sub_links;
https://blog.gojekengineering.com/a-toast-from-postgresql-83b83d0d0683
When I execute this query, except JSON data like;
SELECT paramuser_id, paramperson_id, paramisdeleted, paramactiontime
  FROM zamazin;
It takes 94 ms. :)


Andreas Joseph Krogh , 7 Şub 2020 Cum, 17:42 tarihinde
şunu yazdı:

> På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <
> [email protected]>:
>
> I use pgadmin3.
>
>
> Try "psql", it has the lowest overhead (I think). pgAdmin might use time
> presenting the results etc. which is easy to overlook.
>
> --
> Andreas Joseph Krogh
> ​
>

Re: TOAST table performance problem

2020-02-07 Thread Andreas Joseph Krogh 
Try \o  in psql, to redirect the output to file, and prevent it from 
processing the json (ie. format it)

Den 7. februar 2020 15:59:05 CET, skrev Asya Nevra Buyuksoy 
:
>I try it, but there is no enhancement.
>I read this link is about TOAST and also its sub_links;
>https://blog.gojekengineering.com/a-toast-from-postgresql-83b83d0d0683
>When I execute this query, except JSON data like;
>SELECT paramuser_id, paramperson_id, paramisdeleted, paramactiontime
>  FROM zamazin;
>It takes 94 ms. :)
>
>
>Andreas Joseph Krogh , 7 Şub 2020 Cum, 17:42 tarihinde
>şunu yazdı:
>
>> På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <
>> [email protected]>:
>>
>> I use pgadmin3.
>>
>>
>> Try "psql", it has the lowest overhead (I think). pgAdmin might use time
>> presenting the results etc. which is easy to overlook.
>>
>> --
>> Andreas Joseph Krogh
>> ​
>>

-- 
Sendt fra min Android-enhet med K-9 e-post. Unnskyld min kortfattethet.