Thread: How can I Improve performance in Solaris?
I want to know, how can I improve the performance of postgres, I have a java class thar inserts register every 30 min but is very slow.
The process of postgres consume the 78% of CPU.
I have in /etc/system
set shmsys:shminfo_shmmax=0x50000000
set shmsys:shminfo_shmmni=0x100
set shmsys:shminfo_shmseg=0x10
Thanks
Ingrid
On Wed, Aug 13, 2003 at 09:03:31AM -0500, ingrid martinez wrote:
> I want to know, how can I improve the performance of postgres, I
> have a java class thar inserts register every 30 min but is very
> slow.
What does the query do? How is postgres configured?
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
The query that execute is only inserts, I use a batch of 300 and then do commit. insert into FLOWS values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and postgresql.conf looks like this # # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form # # name = value # # (The `=' is optional.) White space is collapsed, comments are # introduced by `#' anywhere on a line. The complete list of option # names and allowed values can be found in the PostgreSQL # documentation. The commented-out settings shown in this file # represent the default values. # Any option can also be given as a command line switch to the # postmaster, e.g., 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. #======================================================================== # # Connection Parameters # #tcpip_socket = false #ssl = false #max_connections = 32 #port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 #virtual_host = '' #krb_server_keyfile = '' # # Shared Memory Size # #shared_buffers = 64 # 2*max_connections, min 16 #max_fsm_relations = 100 # min 10, fsm is free space map #max_fsm_pages = 10000 # min 1000, fsm is free space map #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8 # min 4 # # Non-shared Memory Sizes # #sort_mem = 512 # min 32 #vacuum_mem = 8192 # min 1024 # # Write-ahead log (WAL) # #wal_files = 0 # range 0-64 #wal_sync_method = fsync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 #commit_delay = 0 # range 0-100000 #commit_siblings = 5 # range 1-1000 #checkpoint_segments = 3 # in logfile segments (16MB each), min 1 #checkpoint_timeout = 300 # in seconds, range 30-3600 #fsync = true # # Optimizer Parameters # #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = true #ksqo = false #effective_cache_size = 1000 # default in 8k pages #random_page_cost = 4 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 # # GEQO Optimizer Parameters # #geqo = true #geqo_selection_bias = 2.0 # range 1.5-2.0 #geqo_threshold = 11 #geqo_pool_size = 0 # default based on #tables in query, range 128-1024 #geqo_effort = 1 #geqo_generations = 0 #geqo_random_seed = -1 # auto-compute seed # # Debug display # #silent_mode = false #log_connections = false #log_timestamp = false #log_pid = false #debug_level = 0 # range 0-16 #debug_print_query = false #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false # requires USE_ASSERT_CHECKING #debug_assertions = true # # Syslog # # requires ENABLE_SYSLOG #syslog = 0 # range 0-2 #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # # Statistics # #show_parser_stats = false #show_planner_stats = false #show_executor_stats = false #show_query_stats = false # requires BTREE_BUILD_STATS #show_btree_build_stats = false # # Access statistics collection # #stats_start_collector = true #stats_reset_on_server_start = true #stats_command_string = false #stats_row_level = false #stats_block_level = false # # Lock Tracing # #trace_notify = false # requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0 # # Misc # #dynamic_library_path = '$libdir' #australian_timezones = false #authentication_timeout = 60 # min 1, max 600 #deadlock_timeout = 1000 #default_transaction_isolation = 'read committed' #max_expr_depth = 10000 # min 10 #max_files_per_process = 1000 # min 25 #password_encryption = false #sql_inheritance = true #transform_null_equals = false ----- Original Message ----- From: "Andrew Sullivan" <andrew@libertyrms.info> To: <pgsql-performance@postgresql.org> Sent: Wednesday, August 13, 2003 9:32 AM Subject: Re: [PERFORM] How can I Improve performance in Solaris? > On Wed, Aug 13, 2003 at 09:03:31AM -0500, ingrid martinez wrote: > > I want to know, how can I improve the performance of postgres, I > > have a java class thar inserts register every 30 min but is very > > slow. > > What does the query do? How is postgres configured? > > A > > -- > ---- > Andrew Sullivan 204-4141 Yonge Street > Liberty RMS Toronto, Ontario Canada > <andrew@libertyrms.info> M2P 2A8 > +1 416 646 3304 x110 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Wed, 2003-08-13 at 11:17, ingrid martinez wrote: > The query that execute is only inserts, I use a batch of 300 and then do > commit. > > insert into FLOWS values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) Any foreign keys on this table? Triggers or Rules? What kind of hardware do you have? Anything else running on it? Could you provide the header information from top? Off the cuff, modify your postgresql.conf for the below and restart PostgreSQL. shared_buffers = 1000 # 2*max_connections, min 16 effective_cache_size = 4000 # default in 8k pages
Attachment
On Wed, Aug 13, 2003 at 10:17:45AM -0500, ingrid martinez wrote: > The query that execute is only inserts, I use a batch of 300 and then do > commit. > > insert into FLOWS values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) Are there any foreign keys, &c? > > and > > postgresql.conf looks like this [ . . .] The configuration is the default. You'll certainly want to increase the shared memory and fiddle with some of the other usual pieces. There is some discussion of the config file at <http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html>. Unless the INSERTs are causing SELECTs, though, I can't see what exactly might be causing you so much difficulty. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
Floes table looks like this
Table "flows"
Column | Type | Modifiers
----------------------+--------------------------+-----------
flidload | bigint | not null
firsttime | bigint |
fldestpeeraddress | character varying(30) |
fldesttransaddress | bigint |
fldesttranstype | smallint |
fldfromoctets | bigint |
fldscodepoint | smallint |
fldtooctets | bigint |
flfrompdus | bigint |
flid | text |
flidrule | bigint |
flsourcepeeraddress | character varying(30) |
flsourcetransaddress | bigint |
flsourcetranstype | smallint |
fltime | timestamp with time zone |
fltopdus | bigint |
lasttime | bigint |
sourceinterface | smallint |
destinterface | smallint |
sourceasn | smallint |
destasn | smallint |
Primary key: flows_pkey
insert into FLOWS values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
postgresql.conf looks like this
#
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form
#
# name = value
#
# (The `=' is optional.) White space is collapsed, comments are
# introduced by `#' anywhere on a line. The complete list of option
# names and allowed values can be found in the PostgreSQL
# documentation. The commented-out settings shown in this file
# represent the default values.
# Any option can also be given as a command line switch to the
# postmaster, e.g., 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#========================================================================
#
# Connection Parameters
#
#tcpip_socket = false
#ssl = false
#max_connections = 32
#port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
#shared_buffers = 64 # 2*max_connections, min 16
#max_fsm_relations = 100 # min 10, fsm is free space map
#max_fsm_pages = 10000 # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4
#
# Non-shared Memory Sizes
#
#sort_mem = 512 # min 32
#vacuum_mem = 8192 # min 1024
#
# Write-ahead log (WAL)
#
#wal_files = 0 # range 0-64
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300 # in seconds, range 30-3600
#fsync = true
#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true
#ksqo = false
#effective_cache_size = 1000 # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on #tables in query, range
128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed
#
# Debug display
#
#silent_mode = false
#log_connections = false
#log_timestamp = false
#log_pid = false
#debug_level = 0 # range 0-16
#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
# requires USE_ASSERT_CHECKING
#debug_assertions = true
#
# Syslog
#
# requires ENABLE_SYSLOG
#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#
# Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
# requires BTREE_BUILD_STATS
#show_btree_build_stats = false
#
# Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false
#
# Lock Tracing
#
#trace_notify = false
# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#
# Misc
#
#dynamic_library_path = '$libdir'
#australian_timezones = false
#authentication_timeout = 60 # min 1, max 600
#deadlock_timeout = 1000
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = false
#sql_inheritance = true
#transform_null_equals = false
----- Original Message -----
From: "Andrew Sullivan" <andrew@libertyrms.info>
To: <pgsql-performance@postgresql.org>
Sent: Wednesday, August 13, 2003 11:17 AM
Subject: Re: [PERFORM] How can I Improve performance in Solaris?
> On Wed, Aug 13, 2003 at 10:17:45AM -0500, ingrid martinez wrote:
> > The query that execute is only inserts, I use a batch of 300 and then do
> > commit.
> >
> > insert into FLOWS values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>
> Are there any foreign keys, &c?
>
> >
> > and
> >
> > postgresql.conf looks like this
>
> [ . . .]
>
> The configuration is the default. You'll certainly want to increase
> the shared memory and fiddle with some of the other usual pieces.
> There is some discussion of the config file at
>
<http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html>.
> Unless the INSERTs are causing SELECTs, though, I can't see what
> exactly might be causing you so much difficulty.
>
> A
>
> --
> ----
> Andrew Sullivan 204-4141 Yonge Street
> Liberty RMS Toronto, Ontario Canada
> <andrew@libertyrms.info> M2P 2A8
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
On Wed, 13 Aug 2003, ingrid martinez wrote: > Floes table looks like this > > Table "flows" > Column | Type | Modifiers > ----------------------+--------------------------+----------- > flidload | bigint | not null > firsttime | bigint | > fldestpeeraddress | character varying(30) | > fldesttransaddress | bigint | > fldesttranstype | smallint | > fldfromoctets | bigint | > fldscodepoint | smallint | > fldtooctets | bigint | > flfrompdus | bigint | > flid | text | > flidrule | bigint | > flsourcepeeraddress | character varying(30) | > flsourcetransaddress | bigint | > flsourcetranstype | smallint | > fltime | timestamp with time zone | > fltopdus | bigint | > lasttime | bigint | > sourceinterface | smallint | > destinterface | smallint | > sourceasn | smallint | > destasn | smallint | > Primary key: flows_pkey Which columns are in the pkey?
the primary key is flidload ----- Original Message ----- From: "scott.marlowe" <scott.marlowe@ihs.com> To: "ingrid martinez" <ingridm@qoslabs.com> Cc: "Andrew Sullivan" <andrew@libertyrms.info>; <pgsql-performance@postgresql.org> Sent: Wednesday, August 13, 2003 11:47 AM Subject: Re: [PERFORM] How can I Improve performance in Solaris? > On Wed, 13 Aug 2003, ingrid martinez wrote: > > > Floes table looks like this > > > > Table "flows" > > Column | Type | Modifiers > > ----------------------+--------------------------+----------- > > flidload | bigint | not null > > firsttime | bigint | > > fldestpeeraddress | character varying(30) | > > fldesttransaddress | bigint | > > fldesttranstype | smallint | > > fldfromoctets | bigint | > > fldscodepoint | smallint | > > fldtooctets | bigint | > > flfrompdus | bigint | > > flid | text | > > flidrule | bigint | > > flsourcepeeraddress | character varying(30) | > > flsourcetransaddress | bigint | > > flsourcetranstype | smallint | > > fltime | timestamp with time zone | > > fltopdus | bigint | > > lasttime | bigint | > > sourceinterface | smallint | > > destinterface | smallint | > > sourceasn | smallint | > > destasn | smallint | > > Primary key: flows_pkey > > Which columns are in the pkey? > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
More than likely you are suffering from an affliction known as type mismatch. This is listed as tip 9 here on the performance list (funny, it was sent at the bottom of your reply :-) What happens is that when you do: select * from some_table where id=123; where id is a bigint the query planner assumes you must want 123 cast to int4, which doesn't match int8 (aka bigint) and uses a sequential scan to access that row. I.e. it reads the whole table in. You can force the planner to do the right thing here in a couple of ways: select * from some_table where id=123::bigint; -- OR -- select * from some_table where id='123'; On Wed, 13 Aug 2003, ingrid martinez wrote: > the primary key is flidload > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >