pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with database performance, Debian 4gb ram ?
11+ messages / 8 participants
[nested] [flat]
* Problem with database performance, Debian 4gb ram ?
@ 2009-11-02 13:58 Massan <grant.massan@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Massan @ 2009-11-02 13:58 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have now readed many many forums and tried many different solutions and I
am not getting good performance to database. My server is Debian linux, with
4gb ram, there is also java application and I am giving to that 512mb
(JAVA_OPTS) memory. In database there is now like 4milj rows. What should I
do better.
Now .conf is:
max_connections = 80
shared_buffers = 512MB
temp_buffers = 8MB
work_mem = 20MB
maintenance_work_mem = 384MB
wal_buffers = 8MB
checkpoint_segments = 128MB
effective_cache_size = 2304MB
cpu_tuple_cost = 0.0030
cpu_index_tuple_cost = 0.0010
cpu_operator_cost = 0.0005
fsync = off
checkpoint_timeout = 1h
and I am giving kernels like:
sysctl -w kernel.shmmax=1073741824
sysctl -w kernel.shmall=2097152
btw, what file I should modify to give this kernels as defaults ?
Thank you very much ! Hope you can clear my problem !
--
View this message in context: http://old.nabble.com/Problem-with-database-performance%2C-Debian-4gb-ram---tp26157096p26157096.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Problem with database performance, Debian 4gb ram ?
@ 2009-11-02 14:16 Grant Masan <grant.massan@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Grant Masan @ 2009-11-02 14:16 UTC (permalink / raw)
To: pgsql-performance
Hi Hi all,
I have now readed many many forums and tried many different solutions and I
am not getting good performance to database. My server is Debian linux, with
4gb ram, there is also java application and I am giving to that 512mb
(JAVA_OPTS) memory. In database there is now like 4milj rows. What should I
do better.
Now .conf is:
max_connections = 80
shared_buffers = 512MB
temp_buffers = 8MB
work_mem = 20MB
maintenance_work_mem = 384MB
wal_buffers = 8MB
checkpoint_segments = 128MB
effective_cache_size = 2304MB
cpu_tuple_cost = 0.0030
cpu_index_tuple_cost = 0.0010
cpu_operator_cost = 0.0005
fsync = off
checkpoint_timeout = 1h
and I am giving kernels like:
sysctl -w kernel.shmmax=1073741824
sysctl -w kernel.shmall=2097152
btw, what file I should modify to give this kernels as defaults ?
Thank you very much ! Hope you can clear my problem !
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-02 14:19 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Grant Masan <grant.massan@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Grzegorz Jaśkiewicz @ 2009-11-02 14:19 UTC (permalink / raw)
To: Grant Masan <grant.massan@gmail.com>; +Cc: pgsql-performance
On Mon, Nov 2, 2009 at 2:16 PM, Grant Masan <grant.massan@gmail.com> wrote:
> Hi Hi all,
>
> I have now readed many many forums and tried many different solutions and I
> am not getting good performance to database. My server is Debian linux, with
> 4gb ram, there is also java application and I am giving to that 512mb
> (JAVA_OPTS) memory. In database there is now like 4milj rows. What should I
> do better.
>
>
I would rather start to look at queries performance.
What's the size of db ?
select pg_size_pretty(pg_database_size('yourDbName'));
start logging queries, with time of execution, to see which ones are causing
problems.
--
GJ
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-02 14:22 Ivan Voras <ivoras@freebsd.org>
parent: Grant Masan <grant.massan@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Ivan Voras @ 2009-11-02 14:22 UTC (permalink / raw)
To: pgsql-performance
Grant Masan wrote:
> Hi Hi all,
>
> I have now readed many many forums and tried many different solutions
> and I am not getting good performance to database. My server is Debian
> linux, with 4gb ram, there is also java application and I am giving to
> that 512mb (JAVA_OPTS) memory. In database there is now like 4milj rows.
> What should I do better.
> Now .conf is:
>
> max_connections = 80
> shared_buffers = 512MB
> temp_buffers = 8MB
> work_mem = 20MB
> maintenance_work_mem = 384MB
> wal_buffers = 8MB
> checkpoint_segments = 128MB
> effective_cache_size = 2304MB
> cpu_tuple_cost = 0.0030
> cpu_index_tuple_cost = 0.0010
> cpu_operator_cost = 0.0005
> fsync = off
> checkpoint_timeout = 1h
>
> and I am giving kernels like:
>
> sysctl -w kernel.shmmax=1073741824
> sysctl -w kernel.shmall=2097152
>
> btw, what file I should modify to give this kernels as defaults ?
>
> Thank you very much ! Hope you can clear my problem !
You have given almost no information that can be used to help you. In
particular, you seem to be mixing up Java performance and database
performance (JAVA_OPTS has nothing to do with pg performance).
How do you know your performance is low? What is your hardware, what
performance do you get and what do you expect?
If after this you are still convinced the problem is database-related,
you will probably need to run a tool like
http://pqa.projects.postgresql.org/ to search which queries are slow and
then start analyzing each particular query.
In short, there is no magical answer to your question :)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PERFORM] Problem with database performance, Debian 4gb ram ?
@ 2009-11-02 14:33 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Grzegorz Jaśkiewicz @ 2009-11-02 14:33 UTC (permalink / raw)
To: Grant Masan <grant.massan@gmail.com>; +Cc: pgsql-general@postgresql.org
2009/11/2 Grant Masan <grant.massan@gmail.com>
> Size is "6154 MB". I have checked all queries, and those are as good as
> they can be in this situation. You think that this confs doesn't make really
> no difference at all ?
>
> you gotta hit 'reply all' next time ;)
configuration makes difference, but you need to know what is your issue
before you start changing stuff randomly.
try using pg_tune , that should give you good config for your hardware.
--
GJ
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-03 15:13 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Grant Masan <grant.massan@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Kevin Grittner @ 2009-11-03 15:13 UTC (permalink / raw)
To: Grant Masan <grant.massan@gmail.com>; pgsql-performance
Grant Masan <grant.massan@gmail.com> wrote:
> max_connections = 80
> shared_buffers = 512MB
> temp_buffers = 8MB
> work_mem = 20MB
> maintenance_work_mem = 384MB
> wal_buffers = 8MB
> checkpoint_segments = 128MB
> effective_cache_size = 2304MB
> checkpoint_timeout = 1h
Pending further information, these seem sane to me.
> cpu_tuple_cost = 0.0030
> cpu_index_tuple_cost = 0.0010
> cpu_operator_cost = 0.0005
Why did you make these adjustments? I usually have to change the
ratio between page and cpu costs toward the other direction. Unless
you have carefully measured performance with and without these changes
and found a clear win with these, I would recommend going back to the
defaults for these three and tuning from there.
> fsync = off
Only use this if you can afford to lose all data in the database.
(There are some situations where this is actually OK, but they are
unusual.)
As others have stated, though, we'd need more information to really
give much useful advice. An EXPLAIN ANALYZE of a query which isn't
performing to expectations would be helpful, especially if you include
the table definitions (with indexes) of all tables involved in the
query. Output from vmstat or iostat with a fairly small interval (I
usually use 1) while the query is running would be useful, too.
Knowing the exact version of PostgreSQL (like from SELECT version();)
would be useful, as well as knowing more about you disk array and
controller(s).
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-03 17:23 Jeff Janes <jeff.janes@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 11+ messages in thread
From: Jeff Janes @ 2009-11-03 17:23 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Grant Masan <grant.massan@gmail.com>; pgsql-performance
On Tue, Nov 3, 2009 at 7:13 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Grant Masan <grant.massan@gmail.com> wrote:
>
>
>> cpu_tuple_cost = 0.0030
>> cpu_index_tuple_cost = 0.0010
>> cpu_operator_cost = 0.0005
>
> Why did you make these adjustments? I usually have to change the
> ratio between page and cpu costs toward the other direction.
Is that because the database is mostly cached in memory? If I take the
documented descriptions of the costs parameters at face value, I find
that cpu_tuple_cost should be even lower yet.
Cheer,
Jeff
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-03 17:54 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Kevin Grittner @ 2009-11-03 17:54 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Grant Masan <grant.massan@gmail.com>; pgsql-performance
Jeff Janes <jeff.janes@gmail.com> wrote:
> On Tue, Nov 3, 2009 at 7:13 AM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> Grant Masan <grant.massan@gmail.com> wrote:
>>
>>
>>> cpu_tuple_cost = 0.0030
>>> cpu_index_tuple_cost = 0.0010
>>> cpu_operator_cost = 0.0005
>>
>> Why did you make these adjustments? I usually have to change the
>> ratio between page and cpu costs toward the other direction.
>
> Is that because the database is mostly cached in memory? If I take
> the documented descriptions of the costs parameters at face value, I
> find that cpu_tuple_cost should be even lower yet.
Right, the optimizer doesn't model caching effects very well, so I
find that in practice I have to fudge these from their putative
meanings to allow for typical caching. Even with only a small
fraction of the database cached, the heavily accessed indexes tend to
be fairly well cached, so overall performance improves markedly by
dropping random_page_cost to about 2, even in our lowest-percentage-
cached databases.
I've occasionally tried using the defaults for that GUC, which has
always resulted in user complaints about unacceptable performance of
important queries. While I tend to reduce the random_page_cost and
seq_page_cost to tweak things, raising the cpu_*_cost settings would
accomplish the same thing, so reducing them as show above would tend
to push things into sequential scans where indexed access might be
faster.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-06 14:37 Craig James <craig_james@emolecules.com>
parent: Massan <grant.massan@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Craig James @ 2009-11-06 14:37 UTC (permalink / raw)
To: Massan <grant.massan@gmail.com>; +Cc: pgsql-performance
Massan wrote:
> Hi all,
>
> I have now readed many many forums and tried many different solutions and I
> am not getting good performance to database.
You don't give nearly enough information. To begin with, why do you think performance is poor? What exactly are you measuring? What does your application do, at least in broad terms? Have you identified any particular SQL queries that are slow, and if so, have you run EXPLAIN ANALYZE on them? What is the output of EXPlAIN ANALYZE?
What about your hardware, do you have a single disk, a huge RAID10 array, or something in between? How much memory, how many processors?
The more information you provide, the more likely you will get a good answer.
Craig
> My server is Debian linux, with
> 4gb ram, there is also java application and I am giving to that 512mb
> (JAVA_OPTS) memory. In database there is now like 4milj rows. What should I
> do better.
> Now .conf is:
>
> max_connections = 80
> shared_buffers = 512MB
> temp_buffers = 8MB
> work_mem = 20MB
> maintenance_work_mem = 384MB
> wal_buffers = 8MB
> checkpoint_segments = 128MB
> effective_cache_size = 2304MB
> cpu_tuple_cost = 0.0030
> cpu_index_tuple_cost = 0.0010
> cpu_operator_cost = 0.0005
> fsync = off
> checkpoint_timeout = 1h
>
> and I am giving kernels like:
>
> sysctl -w kernel.shmmax=1073741824
> sysctl -w kernel.shmall=2097152
>
> btw, what file I should modify to give this kernels as defaults ?
>
> Thank you very much ! Hope you can clear my problem !
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-06 15:26 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2009-11-06 15:26 UTC (permalink / raw)
To: Grant Masan <grant.massan@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-performance
Please keep the list copied.
Grant Masan <grant.massan@gmail.com> wrote:
> CREATE FUNCTION ... RETURNS SETOF ...
> FOR ... IN SELECT ... LOOP
> FOR ... IN SELECT ... LOOP
> FOR ... IN SELECT ... LOOP
>
> RETURN NEXT text_output;
>
> END LOOP;
> END LOOP;
> END LOOP;
I don't have time to work through the logic of all this to try to
discern what your goal is; but in my experience, such procedural code
can usually be rewritten as a single query. The results are typically
orders of magnitude better.
> SELECT * FROM info_tool(linest,date,date)
> "Function Scan on info_tool (cost=0.00..260.00 rows=1000 width=108)
> (actual time=437712.611..437712.629 rows=14 loops=1)"
> "Total runtime: 437712.686 ms"
To get useful information you need EXPLAIN ANALYZE from statements
inside the function, not of the execution of the function.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with database performance, Debian 4gb ram ?
@ 2009-11-06 15:39 Alvaro Herrera <alvherre@commandprompt.com>
parent: Massan <grant.massan@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2009-11-06 15:39 UTC (permalink / raw)
To: Massan <grant.massan@gmail.com>; +Cc: pgsql-performance
Massan escribió:
> and I am giving kernels like:
>
> sysctl -w kernel.shmmax=1073741824
> sysctl -w kernel.shmall=2097152
>
> btw, what file I should modify to give this kernels as defaults ?
/etc/sysctl.conf
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2009-11-06 15:39 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-02 13:58 Problem with database performance, Debian 4gb ram ? Massan <grant.massan@gmail.com>
2009-11-06 14:37 ` Craig James <craig_james@emolecules.com>
2009-11-06 15:39 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-11-02 14:16 Problem with database performance, Debian 4gb ram ? Grant Masan <grant.massan@gmail.com>
2009-11-02 14:19 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-02 14:33 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-02 14:22 ` Ivan Voras <ivoras@freebsd.org>
2009-11-03 15:13 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 17:23 ` Jeff Janes <jeff.janes@gmail.com>
2009-11-03 17:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-06 15:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox