Hi Ryan,
Most of your pool settings look fine.
You can switch back to "auto-commit" mode of validation.
Regarding the following thread dump :
http://forums.java.net/jive/servlet/JiveServlet/download/56-59323-338842-8159/jstack_dump_2009-03-24_09:26:34.txt
As the resizer is cleaning & validating the pool, all the connection
requests are made to wait. This will be completed in few seconds, again
dependent on the number of connections to be validated. You can confirm
this by taking another thread dump in a couple of minutes.
However, you can tune the pool settings to minimize the frequency of
resizer calls.
This is not the solution to the actual issue you may be facing (as your
other stack traces confirm that the real issue is not w.r.t database),
but it will help to further reduce the number of database validation
calls / cleanup jobs.
idle-timeout : 300 seconds can be made to 1500 seconds
validate-atmost-once-period-in-seconds : 10 seconds can be made to 60
seconds
max-connection-usage : 300 can be made to 500
Hope this helps.
Thanks,
-Jagadish
On Tue, 2009-03-24 at 11:44 -0700, glassfish_at_javadesktop.org wrote:
> I'm not sure which connection pool you were referring to, so here are both:
>
> <connection-pool max-pending-count="4096" queue-size-in-bytes="4096" receive-buffer-size-in-bytes="4096" send-buffer-size-in-bytes="8192"/>
>
> And the JDBC pool:
> <jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="true" is-isolation-level-guaranteed="false" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="300" max-pool-size="400" max-wait-time-in-millis="60000" name="fathom-pool2" non-transactional-connections="false" pool-resize-quantity="10" res-type="javax.sql.DataSource" statement-timeout-in-seconds="55" steady-pool-size="40" validate-atmost-once-period-in-seconds="10" wrap-jdbc-objects="false">
> <property name="SelfDestructOnPingMaxOperations" value="0"/>
> <property name="UseUltraDevWorkAround" value="false"/>
> <property name="UseStreamLengthsInPrepStmts" value="true"/>
> <property name="TcpRcvBuf" value="0"/>
> <property name="UltraDevHack" value="false"/>
> <property name="InitialTimeout" value="2"/>
> <property name="UseNanosForElapsedTime" value="false"/>
> <property name="MetadataCacheSize" value="50"/>
> <property name="UseColumnNamesInFindColumn" value="false"/>
> <property name="RequireSSL" value="false"/>
> <property name="LogXaCommands" value="false"/>
> <property name="CacheCallableStatements" value="false"/>
> <property name="NoDatetimeStringSync" value="false"/>
> <property name="EmulateUnsupportedPstmts" value="true"/>
> <property name="TreatUtilDateAsTimestamp" value="true"/>
> <property name="EmulateLocators" value="false"/>
> <property name="ClientInfoProvider" value="com.mysql.jdbc.JDBC4CommentClientInfoProvider"/>
> <property name="IsInteractiveClient" value="false"/>
> <property name="IncludeInnodbStatusInDeadlockExceptions" value="false"/>
> <property name="ProcessEscapeCodesForPrepStmts" value="true"/>
> <property name="GatherPerformanceMetrics" value="false"/>
> <property name="UseOnlyServerErrorMessages" value="true"/>
> <property name="HoldResultsOpenOverStatementClose" value="false"/>
> <property name="LoadBalanceBlacklistTimeout" value="0"/>
> <property name="UseServerPreparedStmts" value="false"/>
> <property name="UseTimezone" value="false"/>
> <property name="BlobsAreStrings" value="false"/>
> <property name="IgnoreNonTxTables" value="false"/>
> <property name="UseSSL" value="false"/>
> <property name="AllowLoadLocalInfile" value="true"/>
> <property name="SocketFactoryClassName" value="com.mysql.jdbc.StandardSocketFactory"/>
> <property name="SelfDestructOnPingSecondsLifetime" value="0"/>
> <property name="AutoClosePStmtStreams" value="false"/>
> <property name="NullCatalogMeansCurrent" value="true"/>
> <property name="UseInformationSchema" value="false"/>
> <property name="DynamicCalendars" value="false"/>
> <property name="CreateDatabaseIfNotExist" value="false"/>
> <property name="UseServerPrepStmts" value="false"/>
> <property name="Pedantic" value="false"/>
> <property name="UseSSPSCompatibleTimezoneShift" value="false"/>
> <property name="ReconnectAtTxEnd" value="false"/>
> <property name="Url" value="jdbc:mysql://:3306/"/>
> <property name="UseJvmCharsetConverters" value="false"/>
> <property name="FunctionsNeverReturnBlobs" value="false"/>
> <property name="NoAccessToProcedureBodies" value="false"/>
> <property name="AllowNanAndInf" value="false"/>
> <property name="CachePreparedStatements" value="false"/>
> <property name="ExplainSlowQueries" value="false"/>
> <property name="CacheResultSetMetadata" value="false"/>
> <property name="TcpKeepAlive" value="true"/>
> <property name="PreparedStatementCacheSqlLimit" value="256"/>
> <property name="SlowQueryThresholdNanos" value="0"/>
> <property name="LoginTimeout" value="0"/>
> <property name="UseCompression" value="false"/>
> <property name="AutoSlowLog" value="true"/>
> <property name="TcpSndBuf" value="0"/>
> <property name="TcpTrafficClass" value="0"/>
> <property name="RoundRobinLoadBalance" value="false"/>
> <property name="CallableStatementCacheSize" value="100"/>
> <property name="InteractiveClient" value="false"/>
> <property name="NoTimezoneConversionForTimeType" value="false"/>
> <property name="UseHostsInPrivileges" value="true"/>
> <property name="UseSqlStateCodes" value="true"/>
> <property name="GatherPerfMetrics" value="false"/>
> <property name="ReportMetricsIntervalMillis" value="30000"/>
> <property name="UseFastDateParsing" value="true"/>
> <property name="LoggerClassName" value="com.mysql.jdbc.log.StandardLogger"/>
> <property name="DontTrackOpenResources" value="false"/>
> <property name="UseOldUTF8Behavior" value="false"/>
> <property name="OverrideSupportsIntegrityEnhancementFacility" value="false"/>
> <property name="RollbackOnPooledClose" value="true"/>
> <property name="GenerateSimpleParameterMetadata" value="false"/>
> <property name="SecondsBeforeRetryMaster" value="30"/>
> <property name="ResultSetSizeThreshold" value="100"/>
> <property name="TransformedBitIsBoolean" value="false"/>
> <property name="SocketTimeout" value="0"/>
> <property name="Paranoid" value="false"/>
> <property name="FailOverReadOnly" value="true"/>
> <property name="CapitalizeTypeNames" value="true"/>
> <property name="VerifyServerCertificate" value="true"/>
> <property name="TinyInt1isBit" value="true"/>
> <property name="UseDynamicCharsetInfo" value="true"/>
> <property name="UseBlobToStoreUTF8OutsideBMP" value="false"/>
> <property name="EnableQueryTimeouts" value="true"/>
> <property name="PopulateInsertRowWithDefaultValues" value="false"/>
> <property name="UseDirectRowUnpack" value="true"/>
> <property name="PadCharsWithSpace" value="false"/>
> <property name="UseOldAliasMetadataBehavior" value="false"/>
> <property name="PrepStmtCacheSqlLimit" value="256"/>
> <property name="MaxReconnects" value="3"/>
> <property name="AutoDeserialize" value="false"/>
> <property name="JdbcCompliantTruncationForReads" value="true"/>
> <property name="CompensateOnDuplicateKeyUpdateCounts" value="false"/>
> <property name="PinGlobalTxToPhysicalConnection" value="false"/>
> <property name="ProfileSQL" value="false"/>
> <property name="RewriteBatchedStatements" value="false"/>
> <property name="UseUnicode" value="true"/>
> <property name="Logger" value="com.mysql.jdbc.log.StandardLogger"/>
> <property name="DumpMetadataOnColumnNotFound" value="false"/>
> <property name="MaxQuerySizeToLog" value="2048"/>
> <property name="LocatorFetchBufferSize" value="1048576"/>
> <property name="CallableStmtCacheSize" value="100"/>
> <property name="ClobberStreamingResults" value="false"/>
> <property name="TcpNoDelay" value="true"/>
> <property name="LoadBalanceStrategy" value="random"/>
> <property name="StrictFloatingPoint" value="false"/>
> <property name="AutoReconnectForPools" value="false"/>
> <property name="CacheCallableStmts" value="false"/>
> <property name="MaxRows" value="-1"/>
> <property name="UseLocalTransactionState" value="false"/>
> <property name="UseLegacyDatetimeCode" value="true"/>
> <property name="BlobSendChunkSize" value="1048576"/>
> <property name="NetTimeoutForStreamingResults" value="600"/>
> <property name="UseGmtMillisForDatetimes" value="false"/>
> <property name="AllowMultiQueries" value="false"/>
> <property name="JdbcCompliantTruncation" value="true"/>
> <property name="AutoGenerateTestcaseScript" value="false"/>
> <property name="EnablePacketDebug" value="false"/>
> <property name="ContinueBatchOnError" value="true"/>
> <property name="TraceProtocol" value="false"/>
> <property name="UseCursorFetch" value="false"/>
> <property name="UseAffectedRows" value="false"/>
> <property name="EmptyStringsConvertToZero" value="true"/>
> <property name="PreparedStatementCacheSize" value="25"/>
> <property name="StrictUpdates" value="true"/>
> <property name="DumpQueriesOnException" value="false"/>
> <property name="PacketDebugBufferSize" value="20"/>
> <property name="AllowUrlInLocalInfile" value="false"/>
> <property name="UseUsageAdvisor" value="false"/>
> <property name="QueriesBeforeRetryMaster" value="50"/>
> <property name="SocketFactory" value="com.mysql.jdbc.StandardSocketFactory"/>
> <property name="DefaultFetchSize" value="0"/>
> <property name="ProfilerEventHandler" value="com.mysql.jdbc.profiler.LoggingProfilerEventHandler"/>
> <property name="ConnectTimeout" value="0"/>
> <property name="YearIsDateType" value="true"/>
> <property name="RunningCTS13" value="false"/>
> <property name="UseJDBCCompliantTimezoneShift" value="false"/>
> <property name="UseFastIntParsing" value="true"/>
> <property name="ProfileSql" value="false"/>
> <property name="RelaxAutoCommit" value="false"/>
> <property name="NullNamePatternMatchesAll" value="true"/>
> <property name="RetriesAllDown" value="120"/>
> <property name="CachePrepStmts" value="true"/>
> <property name="UseReadAheadInput" value="false"/>
> <property name="SlowQueryThresholdMillis" value="30000"/>
> <property name="LogSlowQueries" value="true"/>
> <property name="MaintainTimeStats" value="false"/>
> <property name="PrepStmtCacheSize" value="512"/>
> <property name="ZeroDateTimeBehavior" value="convertToNull"/>
> <property name="AlwaysSendSetIsolation" value="false"/>
> <property name="UseUnbufferedInput" value="false"/>
> <property name="CacheServerConfiguration" value="true"/>
> <property name="ElideSetAutoCommits" value="true"/>
> <property name="UseLocalSessionState" value="true"/>
> <property name="DatabaseName" value="XXX"/>
> <property name="PortNumber" value="XXX"/>
> <property name="Port" value="XXX"/>
> <property name="User" value="XXX"/>
> <property name="URL" value="XXX"/>
> <property name="Password" value=""/>
> </jdbc-connection-pool>
> [Message sent by forum member 'rwillie6' (rwillie6)]
>
> http://forums.java.net/jive/thread.jspa?messageID=338782
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>