Thread: Performance problems with a higher number of clients
Hello,
I am facing a problem trying to put 500 concurrent users accessing
a postgresql instance. Basically, the machine begins to do a lot i/o...
swap area increases more and more...
The vmstat began with 9200 (swpd) and after 20 minutes it was like that:
VMSTAT:
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us
sy id
2 29 1 106716 9576 7000 409876 32 154 5888 1262 616 1575 8
12 80
0 29 1 107808 9520 6896 409904 60 220 5344 1642 662 1510 9
15 76
0 89 1 108192 9528 6832 410184 172 138 6810 1750 693 2466 11
16 73
0 27 1 108192 9900 6824 409852 14 112 4488 1294 495 862 2
9 88
8 55 1 108452 9552 6800 410284 26 12 6266 1082 651 2284 8
11 81
5 78 2 109220 8688 6760 410816 148 534 6318 1632 683 1230 6
13 81
The application that I am trying to running mimmics the tpc-c benchmark...
Actually, I am simulating the tpc-c workload without considering
screens and other details. The only interesting is
on the database workload proposed by the benchmark and its distributions.
The machine is a dual-processor pentium III, with 1GB, external storage
device. It runs Linux version 2.4.21-dt1 (root@dupond) (gcc version 2.96
20000731 (Red Hat Linux 7.3 2.96-113)) #7 SMP Mon Apr 21 19:43:17 GMT
2003, Postgresql 7.5devel.
Postgresql configuration:
effective_cache_size = 35000
shared_buffers = 5000
random_page_cost = 2
cpu_index_tuple_cost = 0.0005
sort_mem = 10240
I would like to know if this behaivor is normal considering
the number of clients, the workload and the database size (7.8 GB) ?
Or if there is something that I can change to get better results.
Best regards,
Alfranio Junior.
Alfranio Correia Junior wrote: > Postgresql configuration: > > effective_cache_size = 35000 > shared_buffers = 5000 > random_page_cost = 2 > cpu_index_tuple_cost = 0.0005 > sort_mem = 10240 Lower sort mem to say 2000-3000, up shared buffers to 10K and up effective cache size to around 65K. That should make it behave bit better. I guess tuning sort mem alone would give you performance you are expecting.. Tune them one by one. HTH Shridhar
On Thu, 11 Dec 2003 04:13:28 +0000 Alfranio Correia Junior <alfranio@lsd.di.uminho.pt> wrote: > r b w swpd free buff cache si so bi bo in cs > us sy id > 2 29 1 106716 9576 7000 409876 32 154 5888 1262 616 1575 > 8 12 80 On linux I've found as soon as it has to swap its oh-so-wonderful VM brings the machine to a screeching halt. > sort_mem = 10240 > Here's a big problem This gives _EACH SORT_ 10MB (No more, no less) to play with. 10MB * 500 connections == 5000MB in one case.. Some queries may have more sort steps. It is possible 1 connection could be using 30-40MB of sort_mem. You'll need to bring that value down to prevent swapping. If you have a few "common" queries that are run a lot check out hte explain analyze. You can see about how much sort_mem you'll need. Look in the sort step. it should tell you the width and the # of rows. Multiply those. That is sort of how much memory you'll need (I'd round it up a bit) If under normal workload your DB is swapping you have problems. You'll need to either tune your config or get bigger hardware. You may want to also consider an OS that deals with that situation a bit better. good luck. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
Alfranio Correia Junior <alfranio@lsd.di.uminho.pt> writes:
> I am facing a problem trying to put 500 concurrent users accessing
> a postgresql instance.
I think you're going to need to buy more RAM. 1Gb of RAM means there
is a maximum of 2Mb available per Postgres process before you start
to go into swap hell --- in practice a lot less, since you have to allow
for other things like the kernel and other applications.
AFAIR TPC-C doesn't involve any complex queries, so it's possible you
could run it with only 1Mb of workspace per process, but not when
you've configured
> sort_mem = 10240
That's ten times more than your configuration can possibly support.
(I don't recall whether TPC-C uses any queries that would sort, so
it's possible this setting isn't affecting you; but if you are doing
any sorts then it's killing you.)
Bottom line is you probably need more RAM.
regards, tom lane
Thanks for the advices,
The performance is a bit better now. Unfortunately, the machine does not
allow
to put more than 200 - ~250 users without noticing swap hell.
I have to face the fact that I don't have enough memory....
I used the following configuration:
effective_cache_size = 65000
shared_buffers = 10000
random_page_cost = 2
cpu_index_tuple_cost = 0.0005
sort_mem = 512 - I tested each query to see the amount of space
required to sort as Jeff suggested --> nothing above this value
I tested the system with 100, 200, 300, 400, 500 and finally 250 users.
Until ~250 users the system presents good response time and the swap
almost does not exist.
During these expirements, I also started psql and tried to run some
queries.
Unfortunately, even with ~250 users there is one query that takes too
long to finish...
In fact, I canceled its execution after 5 minutes waiting to see anything.
This is the query:
select count(distinct(s_i_id))
from stock, order_line
where ol_w_id = _xx_ and
ol_d_id = _xx_ and
ol_o_id between _xx_ and
_xx_ and
s_w_id = ol_w_id and
s_i_id = ol_i_id and
s_quantity < _xx_;
When the system has no load, after a vacuum -f, I can execute the query
and the plan produced is presented as follows:
Aggregate (cost=49782.16..49782.16 rows=1 width=4) (actual
time=52361.573..52361.574 rows=1 loops=1)
-> Nested Loop (cost=0.00..49780.24 rows=768 width=4) (actual
time=101.554..52328.913 rows=952 loops=1)
-> Index Scan using pk_order_line on order_line o
(cost=0.00..15779.32 rows=8432 width=4) (actual time=84.352..151.345
rows=8964 loops=1)
Index Cond: ((ol_w_id = 4) AND (ol_d_id = 4) AND (ol_o_id
>= 100) AND (ol_o_id <= 1000))
-> Index Scan using pk_stock on stock (cost=0.00..4.02 rows=1
width=4) (actual time=5.814..5.814 rows=0 loops=8964)
Index Cond: ((stock.s_w_id = 4) AND (stock.s_i_id =
"outer".ol_i_id))
Filter: (s_quantity < 20)
Total runtime: 52403.673 ms
(8 rows)
The talbes are designed as follows:
--ROWS ~5000000
CREATE TABLE stock (
s_i_id int NOT NULL ,
s_w_id int NOT NULL ,
s_quantity int NULL ,
s_dist_01 char (24) NULL ,
s_dist_02 char (24) NULL ,
s_dist_03 char (24) NULL ,
s_dist_04 char (24) NULL ,
s_dist_05 char (24) NULL ,
s_dist_06 char (24) NULL ,
s_dist_07 char (24) NULL ,
s_dist_08 char (24) NULL ,
s_dist_09 char (24) NULL ,
s_dist_10 char (24) NULL ,
s_ytd int NULL ,
s_order_cnt int NULL ,
s_remote_cnt int NULL ,
s_data char (50) NULL
);
--ROWS ~15196318
CREATE TABLE order_line (
ol_o_id int NOT NULL ,
ol_d_id int NOT NULL ,
ol_w_id int NOT NULL ,
ol_number int NOT NULL ,
ol_i_id int NULL ,
ol_supply_w_id int NULL ,
ol_delivery_d timestamp NULL ,
ol_quantity int NULL ,
ol_amount numeric(6, 2) NULL ,
ol_dist_info char (24) NULL
);
ALTER TABLE stock ADD
CONSTRAINT PK_stock PRIMARY KEY
(
s_w_id,
s_i_id
);
ALTER TABLE order_line ADD
CONSTRAINT PK_order_line PRIMARY KEY
(
ol_w_id,
ol_d_id,
ol_o_id,
ol_number
);
CREATE INDEX IX_order_line ON order_line(ol_i_id);
Any suggestion ?
Tom Lane wrote:
>Alfranio Correia Junior <alfranio@lsd.di.uminho.pt> writes:
>
>
>>I am facing a problem trying to put 500 concurrent users accessing
>>a postgresql instance.
>>
>>
>
>I think you're going to need to buy more RAM. 1Gb of RAM means there
>is a maximum of 2Mb available per Postgres process before you start
>to go into swap hell --- in practice a lot less, since you have to allow
>for other things like the kernel and other applications.
>
>AFAIR TPC-C doesn't involve any complex queries, so it's possible you
>could run it with only 1Mb of workspace per process, but not when
>you've configured
>
>
>
>>sort_mem = 10240
>>
>>
>
>That's ten times more than your configuration can possibly support.
>(I don't recall whether TPC-C uses any queries that would sort, so
>it's possible this setting isn't affecting you; but if you are doing
>any sorts then it's killing you.)
>
>Bottom line is you probably need more RAM.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>