1. #1
    Futur Membre du Club
    Inscrit en
    avril 2011
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : avril 2011
    Messages : 6
    Points : 5
    Points
    5

    Par défaut com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource

    Bonjour à tous,

    j'utilise centos 6.5 comme système d'exploitation et ma version de postgres est postgreSql 9.3 et j'utilise ces deux jar c3p0-0.9.1.1.jar et postgresql-9.4.1208.jre6.jar.

    mon application et la base de donnée sont dans le même serveur physique qui est très puissant et qui a cette performance :24 cpu , 256 GB memory

    ceci est la configuration de la connexion à la base de donnée :applicationContext-db.xml

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">
     
    <beans>
             <bean id="dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
           <property name="driverClass" value="org.postgresql.Driver"/>
     
            <property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/test"/> 
     
            <property name="user" value="postgres"/>
            <property name="password" value="postgres"/>
               <!-- pool sizing -->
        <property name="initialPoolSize" value="20" />
            <property name="minPoolSize" value="20" />
            <property name="maxPoolSize" value="200" />
            <property name="acquireIncrement" value="3" />
            <property name="maxStatements" value="0" />
     
            <!-- retries -->
            <property name="acquireRetryAttempts" value="30" />
            <property name="acquireRetryDelay" value="1000" /> <!-- 1s -->
            <property name="breakAfterAcquireFailure" value="false" />
     
            <!-- refreshing connections -->
            <property name="maxIdleTime" value="180" /> <!-- 3min -->
            <property name="maxConnectionAge" value="10" /> <!-- 1h -->
     
            <!-- timeouts and testing -->
            <property name="checkoutTimeout" value="30000" /> <!-- 30s -->
            <property name="idleConnectionTestPeriod" value="60" /> <!-- 60 -->
            <property name="testConnectionOnCheckout" value="true" />
            <property name="preferredTestQuery" value="SELECT 1" />
            <property name="testConnectionOnCheckin" value="true" /> 
     
        </bean>
    </beans>
    et ceci la configuration de postgresql.conf :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    293
    294
    295
    296
    297
    298
    299
    300
    301
    302
    303
    304
    305
    306
    307
    308
    309
    310
    311
    312
    313
    314
    315
    316
    317
    318
    319
    320
    321
    322
    323
    324
    325
    326
    327
    328
    329
    330
    331
    332
    333
    334
    335
    336
    337
    338
    339
    340
    341
    342
    343
    344
    345
    346
    347
    348
    349
    350
    351
    352
    353
    354
    355
    356
    357
    358
    359
    360
    361
    362
    363
    364
    365
    366
    367
    368
    369
    370
    371
    372
    373
    374
    375
    376
    377
    378
    379
    380
    381
    382
    383
    384
    385
    386
    387
    388
    389
    390
    391
    392
    393
    394
    395
    396
    397
    398
    399
    400
    401
    402
    403
    404
    405
    406
    407
    408
    409
    410
    411
    412
    413
    414
    415
    416
    417
    418
    419
    420
    421
    422
    423
    424
    425
    426
    427
    428
    429
    430
    431
    432
    433
    434
    435
    436
    437
    438
    439
    440
    441
    442
    443
    444
    445
    446
    447
    448
    449
    450
    451
    452
    453
    454
    455
    456
    457
    458
    459
    460
    461
    462
    463
    464
    465
    466
    467
    468
    469
    470
    471
    472
    473
    474
    475
    476
    477
    478
    479
    480
    481
    482
    483
    484
    485
    486
    487
    488
    489
    490
    491
    492
    493
    494
    495
    496
    497
    498
    499
    500
    501
    502
    503
    504
    505
    506
    507
    508
    509
    510
    511
    512
    513
    514
    515
    516
    517
    518
    519
    520
    521
    522
    523
    524
    525
    526
    527
    528
    529
    530
    531
    532
    533
    534
    535
    536
    537
    538
    539
    540
    541
    542
    543
    544
    545
    546
    547
    548
    549
    550
    551
    552
    553
    554
    555
    556
    557
    558
    559
    560
    561
    562
    563
    564
    565
    566
    567
    568
    569
    570
    571
    572
    573
    574
    575
    576
    577
    578
    579
    580
    581
    582
    583
    584
    585
    586
    587
    588
    589
    590
    591
    592
    593
    594
    595
    596
    597
    598
    599
    600
    601
    602
    603
    604
    605
    606
    607
    608
    609
    610
    611
    612
    # HEADER: This file was autogenerated at 2015-04-15 19:27:27 +0300
    # HEADER: by puppet.  While it can still be managed manually, it
    # HEADER: is definitely not recommended.
    # -----------------------------
    # PostgreSQL configuration file
    # -----------------------------
    #
    # This file consists of lines of the form:
    #
    #   name = value
    #
    # (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
    # "#" anywhere on a line.  The complete list of parameter names and allowed
    # values can be found in the PostgreSQL documentation.
    #
    # The commented-out settings shown in this file represent the default values.
    # Re-commenting a setting is NOT sufficient to revert it to the default value;
    # you need to reload the server.
    #
    # This file is read on server startup and when the server receives a SIGHUP
    # signal.  If you edit the file on a running system, you have to SIGHUP the
    # server for the changes to take effect, or use "pg_ctl reload".  Some
    # parameters, which are marked below, require a server shutdown and restart to
    # take effect.
    #
    # Any parameter can also be given as a command-line option to the server, e.g.,
    # "postgres -c log_connections=on".  Some parameters can be changed at run time
    # with the "SET" SQL command.
    #
    # Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
    #                MB = megabytes                     s   = seconds
    #                GB = gigabytes                     min = minutes
    #                                                   h   = hours
    #                                                   d   = days
     
     
    #------------------------------------------------------------------------------
    # FILE LOCATIONS
    #------------------------------------------------------------------------------
     
    # The default values of these variables are driven from the -D command-line
    # option or PGDATA environment variable, represented here as ConfigDir.
     
    #data_directory = 'ConfigDir'		# use data in another directory
    					# (change requires restart)
    #hba_file = 'ConfigDir/pg_hba.conf'	# host-based authentication file
    					# (change requires restart)
    #ident_file = 'ConfigDir/pg_ident.conf'	# ident configuration file
    					# (change requires restart)
     
    # If external_pid_file is not explicitly set, no extra PID file is written.
    #external_pid_file = ''			# write an extra PID file
    					# (change requires restart)
     
     
    #------------------------------------------------------------------------------
    # CONNECTIONS AND AUTHENTICATION
    #------------------------------------------------------------------------------
     
    # - Connection Settings -
     
    #listen_addresses = 'localhost'		# what IP address(es) to listen on;
    					# comma-separated list of addresses;
    					# defaults to 'localhost'; use '*' for all
    					# (change requires restart)
    #port = 5432				# (change requires restart)
    max_connections = 1500 # (change requires restart)
    # Note:  Increasing max_connections costs ~400 bytes of shared memory per
    # connection slot, plus lock space (see max_locks_per_transaction).
    #superuser_reserved_connections = 3	# (change requires restart)
    #unix_socket_directories = '/tmp'	# comma-separated list of directories
    					# (change requires restart)
    #unix_socket_group = ''			# (change requires restart)
    #unix_socket_permissions = 0777		# begin with 0 to use octal notation
    					# (change requires restart)
    #bonjour = off				# advertise server via Bonjour
    					# (change requires restart)
    #bonjour_name = ''			# defaults to the computer name
    					# (change requires restart)
     
    # - Security and Authentication -
     
    authentication_timeout = 5min		# 1s-600s
    #ssl = off				# (change requires restart)
    #ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'	# allowed SSL ciphers
    					# (change requires restart)
    #ssl_renegotiation_limit = 512MB	# amount of data between renegotiations
    #ssl_cert_file = 'server.crt'		# (change requires restart)
    #ssl_key_file = 'server.key'		# (change requires restart)
    #ssl_ca_file = ''			# (change requires restart)
    #ssl_crl_file = ''			# (change requires restart)
    #password_encryption = on
    #db_user_namespace = off
     
    # Kerberos and GSSAPI
    #krb_server_keyfile = ''
    #krb_srvname = 'postgres'		# (Kerberos only)
    #krb_caseins_users = off
     
    # - TCP Keepalives -
    # see "man 7 tcp" for details
     
    #tcp_keepalives_idle = 0		# TCP_KEEPIDLE, in seconds;
    					# 0 selects the system default
    #tcp_keepalives_interval = 0		# TCP_KEEPINTVL, in seconds;
    					# 0 selects the system default
    #tcp_keepalives_count = 0		# TCP_KEEPCNT;
    					# 0 selects the system default
     
     
    #------------------------------------------------------------------------------
    # RESOURCE USAGE (except WAL)
    #------------------------------------------------------------------------------
     
    # - Memory -
     
    shared_buffers = 8GB # min 128kB
    					# (change requires restart)
    #temp_buffers = 8MB			# min 800kB
    #max_prepared_transactions = 0		# zero disables the feature
    					# (change requires restart)
    # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
    # per transaction slot, plus lock space (see max_locks_per_transaction).
    # It is not advisable to set max_prepared_transactions nonzero unless you
    # actively intend to use prepared transactions.
    #work_mem = 1MB				# min 64kB
    #maintenance_work_mem = 16MB		# min 1MB
    #max_stack_depth = 2MB			# min 100kB
     
    # - Disk -
     
    #temp_file_limit = -1			# limits per-session temp file space
    					# in kB, or -1 for no limit
     
    # - Kernel Resource Usage -
     
    #max_files_per_process = 1000		# min 25
    					# (change requires restart)
    #shared_preload_libraries = ''		# (change requires restart)
     
    # - Cost-Based Vacuum Delay -
     
    #vacuum_cost_delay = 0			# 0-100 milliseconds
    #vacuum_cost_page_hit = 1		# 0-10000 credits
    #vacuum_cost_page_miss = 10		# 0-10000 credits
    #vacuum_cost_page_dirty = 20		# 0-10000 credits
    #vacuum_cost_limit = 200		# 1-10000 credits
     
    # - Background Writer -
     
    #bgwriter_delay = 200ms			# 10-10000ms between rounds
    #bgwriter_lru_maxpages = 100		# 0-1000 max buffers written/round
    #bgwriter_lru_multiplier = 2.0		# 0-10.0 multipler on buffers scanned/round
     
    # - Asynchronous Behavior -
     
    #effective_io_concurrency = 1		# 1-1000; 0 disables prefetching
     
     
    #------------------------------------------------------------------------------
    # WRITE AHEAD LOG
    #------------------------------------------------------------------------------
     
    # - Settings -
     
    #wal_level = minimal			# minimal, archive, or hot_standby
    					# (change requires restart)
    #fsync = on				# turns forced synchronization on or off
    #synchronous_commit = on		# synchronization level;
    					# off, local, remote_write, or on
    #wal_sync_method = fsync		# the default is the first option
    					# supported by the operating system:
    					#   open_datasync
    					#   fdatasync (default on Linux)
    					#   fsync
    					#   fsync_writethrough
    					#   open_sync
    #full_page_writes = on			# recover from partial page writes
    #wal_buffers = -1			# min 32kB, -1 sets based on shared_buffers
    					# (change requires restart)
    #wal_writer_delay = 200ms		# 1-10000 milliseconds
     
    #commit_delay = 0			# range 0-100000, in microseconds
    #commit_siblings = 5			# range 1-1000
     
    # - Checkpoints -
     
    #checkpoint_segments = 3		# in logfile segments, min 1, 16MB each
    #checkpoint_timeout = 5min		# range 30s-1h
    #checkpoint_completion_target = 0.5	# checkpoint target duration, 0.0 - 1.0
    #checkpoint_warning = 30s		# 0 disables
     
    # - Archiving -
     
    #archive_mode = off		# allows archiving to be done
    				# (change requires restart)
    #archive_command = ''		# command to use to archive a logfile segment
    				# placeholders: %p = path of file to archive
    				#               %f = file name only
    				# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
    #archive_timeout = 0		# force a logfile segment switch after this
    				# number of seconds; 0 disables
     
     
    #------------------------------------------------------------------------------
    # REPLICATION
    #------------------------------------------------------------------------------
     
    # - Sending Server(s) -
     
    # Set these on the master and on any standby that will send replication data.
     
    #max_wal_senders = 0		# max number of walsender processes
    				# (change requires restart)
    wal_keep_segments = 32		
    #wal_keep_segments = 0		# in logfile segments, 16MB each; 0 disables
    #wal_sender_timeout = 60s	# in milliseconds; 0 disables
     
    # - Master Server -
     
    # These settings are ignored on a standby server.
     
    #synchronous_standby_names = ''	# standby servers that provide sync rep
    				# comma-separated list of application_name
    				# from standby(s); '*' = all
    #vacuum_defer_cleanup_age = 0	# number of xacts by which cleanup is delayed
     
    # - Standby Servers -
     
    # These settings are ignored on a master server.
     
    #hot_standby = off			# "on" allows queries during recovery
    					# (change requires restart)
    #max_standby_archive_delay = 30s	# max delay before canceling queries
    					# when reading WAL from archive;
    					# -1 allows indefinite delay
    #max_standby_streaming_delay = 30s	# max delay before canceling queries
    					# when reading streaming WAL;
    					# -1 allows indefinite delay
    #wal_receiver_status_interval = 10s	# send replies at least this often
    					# 0 disables
    #hot_standby_feedback = off		# send info from standby to prevent
    					# query conflicts
    #wal_receiver_timeout = 60s		# time that receiver waits for
    					# communication from master
    					# in milliseconds; 0 disables
     
     
    #------------------------------------------------------------------------------
    # QUERY TUNING
    #------------------------------------------------------------------------------
     
    # - Planner Method Configuration -
     
    #enable_bitmapscan = on
    #enable_hashagg = on
    #enable_hashjoin = on
    #enable_indexscan = on
    #enable_indexonlyscan = on
    #enable_material = on
    #enable_mergejoin = on
    #enable_nestloop = on
    #enable_seqscan = on
    #enable_sort = on
    #enable_tidscan = on
     
    # - Planner Cost Constants -
     
    #seq_page_cost = 1.0			# measured on an arbitrary scale
    #random_page_cost = 4.0			# same scale as above
    #cpu_tuple_cost = 0.01			# same scale as above
    #cpu_index_tuple_cost = 0.005		# same scale as above
    #cpu_operator_cost = 0.0025		# same scale as above
    #effective_cache_size = 128MB
     
    # - Genetic Query Optimizer -
     
    #geqo = on
    #geqo_threshold = 12
    #geqo_effort = 5			# range 1-10
    #geqo_pool_size = 0			# selects default based on effort
    #geqo_generations = 0			# selects default based on effort
    #geqo_selection_bias = 2.0		# range 1.5-2.0
    #geqo_seed = 0.0			# range 0.0-1.0
     
    # - Other Planner Options -
     
    #default_statistics_target = 100	# range 1-10000
    #constraint_exclusion = partition	# on, off, or partition
    #cursor_tuple_fraction = 0.1		# range 0.0-1.0
    #from_collapse_limit = 8
    #join_collapse_limit = 8		# 1 disables collapsing of explicit
    					# JOIN clauses
     
     
    #------------------------------------------------------------------------------
    # ERROR REPORTING AND LOGGING
    #------------------------------------------------------------------------------
     
    # - Where to Log -
     
    log_destination = stderr # Valid values are combinations of
    					# stderr, csvlog, syslog, and eventlog,
    					# depending on platform.  csvlog
    					# requires logging_collector to be on.
     
    # This is used when logging to stderr:
    logging_collector = on # Enable capturing of stderr and csvlog
    					# into log files. Required to be on for
    					# csvlogs.
    					# (change requires restart)
     
    # These are only used if logging_collector is on:
    log_directory = pg_log # directory where log files are written,
    					# can be absolute or relative to PGDATA
    log_filename = 'postgresql-%a.log' # log file name pattern,
    					# can include strftime() escapes
    #log_file_mode = 0600			# creation mode for log files,
    					# begin with 0 to use octal notation
    log_truncate_on_rotation = on # If on, an existing log file with the
    					# same name as the new log file will be
    					# truncated rather than appended to.
    					# But such truncation only occurs on
    					# time-driven rotation, not on restarts
    					# or size-driven rotation.  Default is
    					# off, meaning append to existing files
    					# in all cases.
    log_rotation_age = 1d # Automatic rotation of logfiles will
    					# happen after that time.  0 disables.
    log_rotation_size = 0 # Automatic rotation of logfiles will
    					# happen after that much log output.
    					# 0 disables.
     
    # These are relevant when logging to syslog:
    #syslog_facility = 'LOCAL0'
    #syslog_ident = 'postgres'
     
    # This is only relevant when logging to eventlog (win32):
    #event_source = 'PostgreSQL'
     
    # - When to Log -
     
    #client_min_messages = notice		# values in order of decreasing detail:
    					#   debug5
    					#   debug4
    					#   debug3
    					#   debug2
    					#   debug1
    					#   log
    					#   notice
    					#   warning
    					#   error
     
    #log_min_messages = warning		# values in order of decreasing detail:
    					#   debug5
    					#   debug4
    					#   debug3
    					#   debug2
    					#   debug1
    					#   info
    					#   notice
    					#   warning
    					#   error
    					#   log
    					#   fatal
    					#   panic
     
    #log_min_error_statement = error	# values in order of decreasing detail:
    					#   debug5
    					#   debug4
    					#   debug3
    					#   debug2
    					#   debug1
    					#   info
    					#   notice
    					#   warning
    					#   error
    					#   log
    					#   fatal
    					#   panic (effectively off)
     
    #log_min_duration_statement = -1	# -1 is disabled, 0 logs all statements
    					# and their durations, > 0 logs only
    					# statements running at least this number
    					# of milliseconds
     
     
    # - What to Log -
     
    #debug_print_parse = off
    #debug_print_rewritten = off
    #debug_print_plan = off
    #debug_pretty_print = on
    #log_checkpoints = off
    #log_connections = off
    #log_disconnections = off
    #log_duration = off
    #log_error_verbosity = default		# terse, default, or verbose messages
    #log_hostname = off
    log_line_prefix = '----------------- %m --------------------------------------------------------\n  %a %u@%d %p \n %r \n %e' # special values:
    					#   %a = application name
    					#   %u = user name
    					#   %d = database name
    					#   %r = remote host and port
    					#   %h = remote host
    					#   %p = process ID
    					#   %t = timestamp without milliseconds
    					#   %m = timestamp with milliseconds
    					#   %i = command tag
    					#   %e = SQL state
    					#   %c = session ID
    					#   %l = session line number
    					#   %s = session start timestamp
    					#   %v = virtual transaction ID
    					#   %x = transaction ID (0 if none)
    					#   %q = stop here in non-session
    					#        processes
    					#   %% = '%'
    					# e.g. '<%u%%%d> '
    #log_lock_waits = off			# log lock waits >= deadlock_timeout
    #log_statement = 'none'			# none, ddl, mod, all
    #log_temp_files = -1			# log temporary files equal or larger
    					# than the specified size in kilobytes;
    					# -1 disables, 0 logs all temp files
    log_timezone = 'Asia/Aden'
     
     
    #------------------------------------------------------------------------------
    # RUNTIME STATISTICS
    #------------------------------------------------------------------------------
     
    # - Query/Index Statistics Collector -
     
    #track_activities = on
    #track_counts = on
    #track_io_timing = off
    #track_functions = none			# none, pl, all
    #track_activity_query_size = 1024	# (change requires restart)
    #update_process_title = on
    #stats_temp_directory = 'pg_stat_tmp'
     
     
    # - Statistics Monitoring -
     
    #log_parser_stats = off
    #log_planner_stats = off
    #log_executor_stats = off
    #log_statement_stats = off
     
     
    #------------------------------------------------------------------------------
    # AUTOVACUUM PARAMETERS
    #------------------------------------------------------------------------------
     
    #autovacuum = on			# Enable autovacuum subprocess?  'on'
    					# requires track_counts to also be on.
    #log_autovacuum_min_duration = -1	# -1 disables, 0 logs all actions and
    					# their durations, > 0 logs only
    					# actions running at least this number
    					# of milliseconds.
    #autovacuum_max_workers = 3		# max number of autovacuum subprocesses
    					# (change requires restart)
    #autovacuum_naptime = 1min		# time between autovacuum runs
    #autovacuum_vacuum_threshold = 50	# min number of row updates before
    					# vacuum
    #autovacuum_analyze_threshold = 50	# min number of row updates before
    					# analyze
    #autovacuum_vacuum_scale_factor = 0.2	# fraction of table size before vacuum
    #autovacuum_analyze_scale_factor = 0.1	# fraction of table size before analyze
    #autovacuum_freeze_max_age = 200000000	# maximum XID age before forced vacuum
    					# (change requires restart)
    #autovacuum_multixact_freeze_max_age = 400000000	# maximum Multixact age
    					# before forced vacuum
    					# (change requires restart)
    #autovacuum_vacuum_cost_delay = 20ms	# default vacuum cost delay for
    					# autovacuum, in milliseconds;
    					# -1 means use vacuum_cost_delay
    #autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for
    					# autovacuum, -1 means use
    					# vacuum_cost_limit
     
     
    #------------------------------------------------------------------------------
    # CLIENT CONNECTION DEFAULTS
    #------------------------------------------------------------------------------
     
    # - Statement Behavior -
     
    #search_path = '"$user",public'		# schema names
    #default_tablespace = ''		# a tablespace name, '' uses the default
    #temp_tablespaces = ''			# a list of tablespace names, '' uses
    					# only default tablespace
    #check_function_bodies = on
    #default_transaction_isolation = 'read committed'
    #default_transaction_read_only = off
    #default_transaction_deferrable = off
    #session_replication_role = 'origin'
    #statement_timeout = 0			# in milliseconds, 0 is disabled
    #lock_timeout = 0			# in milliseconds, 0 is disabled
    #vacuum_freeze_min_age = 50000000
    #vacuum_freeze_table_age = 150000000
    #vacuum_multixact_freeze_min_age = 5000000
    #vacuum_multixact_freeze_table_age = 150000000
    #bytea_output = 'hex'			# hex, escape
    #xmlbinary = 'base64'
    #xmloption = 'content'
     
    # - Locale and Formatting -
     
    datestyle = 'iso, mdy'
    #intervalstyle = 'postgres'
    timezone = 'Asia/Aden'
    #timezone_abbreviations = 'Default'     # Select the set of available time zone
    					# abbreviations.  Currently, there are
    					#   Default
    					#   Australia (historical usage)
    					#   India
    					# You can create your own file in
    					# share/timezonesets/.
    #extra_float_digits = 0			# min -15, max 3
    #client_encoding = sql_ascii		# actually, defaults to database
    					# encoding
     
    # These settings are initialized by initdb, but they can be changed.
    lc_messages = C # locale for system error message
    					# strings
    lc_monetary = C # locale for monetary formatting
    lc_numeric = C # locale for number formatting
    lc_time = C # locale for time formatting
     
    # default configuration for text search
    default_text_search_config = 'pg_catalog.english'
     
    # - Other Defaults -
     
    #dynamic_library_path = '$libdir'
    #local_preload_libraries = ''
     
     
    #------------------------------------------------------------------------------
    # LOCK MANAGEMENT
    #------------------------------------------------------------------------------
     
    #deadlock_timeout = 1s
    #max_locks_per_transaction = 64		# min 10
    					# (change requires restart)
    # Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
    # max_locks_per_transaction * (max_connections + max_prepared_transactions)
    # lock table slots.
    #max_pred_locks_per_transaction = 64	# min 10
    					# (change requires restart)
     
     
    #------------------------------------------------------------------------------
    # VERSION/PLATFORM COMPATIBILITY
    #------------------------------------------------------------------------------
     
    # - Previous PostgreSQL Versions -
     
    #array_nulls = on
    #backslash_quote = safe_encoding	# on, off, or safe_encoding
    #default_with_oids = off
    #escape_string_warning = on
    #lo_compat_privileges = off
    #quote_all_identifiers = off
    #sql_inheritance = on
    #standard_conforming_strings = on
    #synchronize_seqscans = on
     
    # - Other Platforms and Clients -
     
    #transform_null_equals = off
     
     
    #------------------------------------------------------------------------------
    # ERROR HANDLING
    #------------------------------------------------------------------------------
     
    #exit_on_error = off			# terminate session on any error?
    #restart_after_crash = on		# reinitialize after backend crash?
     
     
    #------------------------------------------------------------------------------
    # CONFIG FILE INCLUDES
    #------------------------------------------------------------------------------
     
    # These options allow settings to be loaded from files other than the
    # default postgresql.conf.
     
    #include_dir = 'conf.d'			# include files ending in '.conf' from
    					# directory 'conf.d'
    #include_if_exists = 'exists.conf'	# include file only if it exists
    #include = 'special.conf'		# include file
     
     
    #------------------------------------------------------------------------------
    # CUSTOMIZED OPTIONS
    #------------------------------------------------------------------------------
     
    # Add settings for extensions here
    maintenance_work_mem = 1GB
    temp_buffers = 32MB
    work_mem = 64MB
    checkpoint_segments = 32
    max_wal_senders = 5
    wal_keep_segments = 32
    wal_level = hot_standby
    data_directory = '/var/lib/pgsql/9.3/data'
    listen_addresses = '*'
    log_min_duration_statement = 2min
    port = 5432
    effective_cache_size = 16GB
    lorsque l'utilisation de l'application est devenue importante après un certain temps et lorsque le nombre de demandes augmente

    j'ai cette erreur :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
    	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527)
    	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    	at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    	... 202 more
    Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@50e84eee -- timeout at awaitAvailable()
    	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317)
    	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
    	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
    	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
    	... 205 more

    note : le nombre des utilisateurs qui utilisent l'application est environ 1500


    je pense que l'erreur est au niveau de la configuration de postgres ( postgresql.conf )et spécifiquement dans ces deux paramètres :

    shared_buffers
    max_connections


    je n'arrive pas a bien calculer la valeur de max_connections ( maintenant est 1500 et c'est énorme a mon avis ) et cette valeur normalement devrait être compatible avec les paramétrés qui sont dans le fichier applicationContext-db.xml et aussi devrait être compatible avec la performance de serveur.

    y'a t'il quelqu'un qui peut m'aider à bien configurer les paramètres de pool de connexion

    merci d'avance

  2. #2
    Membre expert
    Avatar de alassanediakite
    Homme Profil pro
    Recherche, formation, développement
    Inscrit en
    août 2006
    Messages
    1 468
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : Mali

    Informations professionnelles :
    Activité : Recherche, formation, développement

    Informations forums :
    Inscription : août 2006
    Messages : 1 468
    Points : 3 172
    Points
    3 172
    Billets dans le blog
    7

    Par défaut

    Salut
    Je soupçonne un dépassement du nombre maximal de connexions au niveau du gestionnaire de pool de connexion.
    Il faut t'informer sur ton gestionnaire de pool pour bien configurer le nombre maximal de connexion SIMULTANÉES.
    Par ailleurs il faut...
    1. jeter un coup d’œil dans les journaux d'erreurs de PostgreSQL(le dossier data/pg_log)
    2. utiliser try pour bien capter l'erreur

    @+
    Le monde est trop bien programmé pour être l’œuvre du hasard…
    Mon produit pour la gestion d'école: www.logicoles.com

  3. #3
    Futur Membre du Club
    Inscrit en
    avril 2011
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : avril 2011
    Messages : 6
    Points : 5
    Points
    5

    Par défaut

    bonjour,

    merci pour votre réponse,

    je vais essayer d'utiliser la dernière version de c3p0 qui est c3p0-0.9.5.2.jar et j’espère qu'elle va résoudre le problème de la perte de connexion,

    pour ces deux paramètres dans le fichier postgresql.conf:

    shared_buffers
    max_connections


    à mon avis il vaut mieux donner ces valeurs :

    shared_buffers = 32GB
    max_connections = 200


    200 c'est la même valeur de maxPoolSize qui est dans applicationContext-db.xml

    y'a t'il quelqu'un qui peut me donner son avis pour ce changement ou qui a une autre modification a faire pour résoudre le problème de la perte de connexion.

  4. #4
    Futur Membre du Club
    Inscrit en
    avril 2011
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : avril 2011
    Messages : 6
    Points : 5
    Points
    5

    Par défaut

    j'ai utilisé la dernière version de c3p0 qui est c3p0-0.9.5.2.jar mais le même problème de la perte de connexion existe encore :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
     
    2017-12-03 11:32:08,414 WARN  [org.hibernate.util.JDBCExceptionReporter] (ajp-0.0.0.0-8010-68) SQL Error: 0, SQLState: null
    2017-12-03 11:32:08,415 ERROR [org.hibernate.util.JDBCExceptionReporter] (ajp-0.0.0.0-8010-68) An attempt by a client to checkout a Connection has timed out.
    2017-12-03 11:32:08,415 WARN  [org.hibernate.util.JDBCExceptionReporter] (ajp-0.0.0.0-8010-68) SQL Error: 0, SQLState: null
    2017-12-03 11:32:08,415 ERROR [org.hibernate.util.JDBCExceptionReporter] (ajp-0.0.0.0-8010-68) An attempt by a client to checkout a Connection has timed out.
    2017-12-03 11:32:08,414 WARN  [org.hibernate.util.JDBCExceptionReporter] (ajp-0.0.0.0-8010-228) SQL Error: 0, SQLState: null
    2017-12-03 11:32:08,415 ERROR [org.hibernate.util.JDBCExceptionReporter] (ajp-0.0.0.0-8010-228) An attempt by a client to checkout a Connection has timed out.
    2017-12-03 11:32:08,415 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:336)
     
    ............................
    ...........................
    ..........................
     
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1353)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
    2017-12-03 11:32:08,421 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	... 182 more
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    2017-12-03 11:32:08,422 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	... 187 more
    2017-12-03 11:32:08,423 ERROR [STDERR] (ajp-0.0.0.0-8010-130) Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@49d523bd -- timeout at awaitAvailable()
    2017-12-03 11:32:08,423 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
    2017-12-03 11:32:08,423 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
    2017-12-03 11:32:08,423 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
    2017-12-03 11:32:08,423 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
    2017-12-03 11:32:08,423 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
    2017-12-03 11:32:08,423 ERROR [STDERR] (ajp-0.0.0.0-8010-130) 	... 190 more
    j'ai trouvé après une recherche que certains gens ont ce même type de problème et ils ont mis cette configuration dans applicationContext-db.xml:


    <property name="maxStatements" value="1100" />
    <property name="checkoutTimeout" value="60000" /> <!-- 60s -->



    je vais savoir vos opinions pour ce changement avant de le mettre en production.

  5. #5
    Membre expert
    Avatar de alassanediakite
    Homme Profil pro
    Recherche, formation, développement
    Inscrit en
    août 2006
    Messages
    1 468
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : Mali

    Informations professionnelles :
    Activité : Recherche, formation, développement

    Informations forums :
    Inscription : août 2006
    Messages : 1 468
    Points : 3 172
    Points
    3 172
    Billets dans le blog
    7

    Par défaut

    Salut
    Voir la doc pour shared_buffers et pour max_connections.
    @+
    Le monde est trop bien programmé pour être l’œuvre du hasard…
    Mon produit pour la gestion d'école: www.logicoles.com

Discussions similaires

  1. client sybase time out
    Par vince2005 dans le forum Adaptive Server Enterprise
    Réponses: 1
    Dernier message: 29/03/2010, 18h05
  2. [Session]maîtriser le time out de la session
    Par croquedur dans le forum Servlets/JSP
    Réponses: 8
    Dernier message: 23/10/2007, 11h57
  3. ConnectException: timed out - appli client serveur
    Par IdF-Socrateus dans le forum Servlets/JSP
    Réponses: 2
    Dernier message: 13/06/2006, 19h45
  4. Problème de time-out avec un socket client.
    Par Depteam1 dans le forum MFC
    Réponses: 3
    Dernier message: 17/10/2005, 11h30
  5. error time out expired
    Par the_new dans le forum MS SQL-Server
    Réponses: 2
    Dernier message: 30/03/2005, 14h03

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo