Query Timing Out

[kishanp519]

The table is pretty large and when a query is being executed, it times out and prints out nothing. Is there a way to print an error when it exceeds the timeout, or adjust it?

Jan 23, 2023 @ 10:39:11.082 | 2023-01-23 15:39:11.081 ERROR 12907 --- [nio-4001-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariPool-1 - Connection is not available, request timed out after 30001ms.
-- | --

  | Jan 23, 2023 @ 10:39:11.081 | 2023-01-23 15:39:11.081  WARN 12907 --- [nio-4001-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null

  | Jan 23, 2023 @ 10:38:52.759 | 2023-01-23 15:38:52.759 DEBUG 12907 --- [nnection-reaper] com.amazonaws.internal.SdkSSLSocket      : closing ssm.us-east-1.amazonaws.com/**********

[wog48]

Can you provide more details?

• Can you provide the URL, at least in an abstract way?
• How many entries does the table contains?
• maybe you can also provide the generated SQL from a log?

[kishanp519]

Error being thrown: "org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection"

URL Endpoint is /User and along with that my log shows Query completed with an execution time of 30166 ms. This only happens when the query execution time is over 30000. Right before this I ran the exact query 4-5 times and it worked perfectly.

Hibernate: 
    select
        user0_.Username as col_0_0_,
        user0_.UserPreferencesShowStateToExternalUsers as col_1_0_,
        user0_.MediumPhotoUrl as col_2_0_,
        user0_.UserPermissionsAvantgoUser as col_3_0_,
        user0_.UserPreferencesFavoritesWTShown as col_4_0_,
        user0_.UserPreferencesNewLightningReportRunPageEnabled as col_5_0_,
        user0_.UserRoleId as col_6_0_,
        user0_.CommunityNickname as col_7_0_,
        user0_.UserPreferencesCacheDiagnostics as col_8_0_,
        user0_.UserPreferencesShowManagerToExternalUsers as col_9_0_,
        user0_.MediumBannerPhotoUrl as col_10_0_,
        user0_.UserPreferencesShowWorkPhoneToGuestUsers as col_11_0_,
        user0_.UserPreferencesHideS1BrowserUI as col_12_0_,
        user0_.UserPreferencesDisableMentionsPostEmail as col_13_0_,
        user0_.UserPreferencesDisableFileShareNotificationsForApi as col_14_0_,
        user0_.UserPreferencesHideLightningMigrationModal as col_15_0_,
        user0_.UserPreferencesShowEmailToExternalUsers as col_16_0_,
        user0_.StayInTouchSignature as col_17_0_,
        user0_.EDY_ORGCUSTOM__DB__c as col_18_0_,
        user0_.LastModifiedById as col_19_0_,
        user0_.sales_management__c as col_20_0_,
        user0_.SystemModstamp as col_21_0_,
        user0_.UserPreferencesHideCSNGetChatterMobileTask as col_22_0_,
        user0_.ContactId as col_23_0_,
        user0_.UserPreferencesEventRemindersCheckboxDefault as col_24_0_,
        user0_.UserPreferencesDisProfPostCommentEmail as col_25_0_,
        user0_.Work_com_Points__c as col_26_0_,
        user0_.UserPreferencesSortFeedByComment as col_27_0_,
        user0_.SBQQ__OutputFormatChangeAllowed__c as col_28_0_,
        user0_.Phone as col_29_0_,
        user0_.UserPreferencesShowFaxToGuestUsers as col_30_0_,
        user0_.EDY_ORGCUSTOM__Old_Last_Name__c as col_31_0_,
        user0_.UserPreferencesShowPostalCodeToGuestUsers as col_32_0_,
        user0_.Fax as col_33_0_,
        user0_.UserPermissionsKnowledgeUser as col_34_0_,
        user0_.SBQQ__DefaultProductLookupTab__c as col_35_0_,
        user0_.UserPreferencesHideSecondChatterOnboardingSplash as col_36_0_,
        user0_.NumberOfFailedLogins as col_37_0_,
        user0_.UserPermissionsSFContentUser as col_38_0_,
        user0_.TimeZoneSidKey as col_39_0_,
        user0_.IsPortalEnabled as col_40_0_,
        user0_.IndividualId as col_41_0_,
        user0_.StayInTouchSubject as col_42_0_,
        user0_.UserPreferencesLightningExperiencePreferred as col_43_0_,
        user0_.TrialforceDate__c as col_44_0_,
        user0_.PostalCode as col_45_0_,
        user0_.LanguageLocaleKey as col_46_0_,
        user0_.UserPreferencesShowEmailToGuestUsers as col_47_0_,
        user0_.Longitude as col_48_0_,
        user0_.SenderName as col_49_0_,
        user0_.Signature as col_50_0_,
        user0_.Manager_PTO__c as col_51_0_,
        user0_.UserPreferencesPreviewLightning as col_52_0_,
        user0_.PortalRole as col_53_0_,
        user0_.UserPreferencesHideOnlineSalesAppWelcomeMat as col_54_0_,
        user0_.ProfileId as col_55_0_,
        user0_.JigsawImportLimitOverride as col_56_0_,
        user0_.DefaultGroupNotificationFrequency as col_57_0_,
        user0_.UserPreferencesExcludeMailAppAttachments as col_58_0_,
        user0_.UserPreferencesHasSentWarningEmail as col_59_0_,
        user0_.UserPermissionsWorkDotComUserFeature as col_60_0_,
        user0_.EmployeeNumber as col_61_0_,
        user0_.AccountName2__c as col_62_0_,
        user0_.Show_on_Checkin_Map__c as col_63_0_,
        user0_.UserPreferencesDisableSharePostEmail as col_64_0_,
        user0_.UserPreferencesHideBrowseProductRedirectConfirmation as col_65_0_,
        user0_.Department as col_66_0_,
        user0_.Id as col_67_0_,
        user0_.DelegatedApproverId as col_68_0_,
        user0_.Alias as col_69_0_,
        user0_.UserPreferencesEnableAutoSubForFeeds as col_70_0_,
        user0_.SBQQ__Theme__c as col_71_0_,
        user0_.OfflineTrialExpirationDate as col_72_0_,
        user0_.UserPreferencesSuppressEventSFXReminders as col_73_0_,
        user0_.UserPermissionsMarketingUser as col_74_0_,
        user0_.SenderEmail as col_75_0_,
        user0_.UserPreferencesSuppressTaskSFXReminders as col_76_0_,
        user0_.UserPermissionsJigsawProspectingUser as col_77_0_,
        user0_.UserPreferencesShowMobilePhoneToExternalUsers as col_78_0_,
        user0_.UserPreferencesReceiveNoNotificationsAsApprover as col_79_0_,
        user0_.AboutMe as col_80_0_,
        user0_.FederationIdentifier as col_81_0_,
        user0_.ReceivesAdminInfoEmails as col_82_0_,
        user0_.ReceivesInfoEmails as col_83_0_,
        user0_.UserPreferencesHideEndUserOnboardingAssistantModal as col_84_0_,
        user0_.UserPreferencesReverseOpenActivitiesView as col_85_0_,
        user0_.IsActive as col_86_0_,
        user0_.Name as col_87_0_,
        user0_.UserPreferencesDisMentionsCommentEmail as col_88_0_,
        user0_.SBQQ__ResetProductLookup__c as col_89_0_,
        user0_.BannerPhotoUrl as col_90_0_,
        user0_.UserPermissionsInteractionUser as col_91_0_,
        user0_.UserType as col_92_0_,
        user0_.FullPhotoUrl as col_93_0_,
        user0_.FirstName as col_94_0_,
        user0_.CreatedById as col_95_0_,
        user0_.UserPreferencesReminderSoundOff as col_96_0_,
        user0_.UserPreferencesShowMobilePhoneToGuestUsers as col_97_0_,
        user0_.External_ID__c as col_98_0_,
        user0_.UserPreferencesShowProfilePicToGuestUsers as col_99_0_,
        user0_.UserPreferencesDisCommentAfterLikeEmail as col_100_0_,
        user0_.BadgeText as col_101_0_,
        user0_.IsExtIndicatorVisible as col_102_0_,
        user0_.City as col_103_0_,
        user0_.IsPortalSelfRegistered as col_104_0_,
        user0_.UserPreferencesDisableEndorsementEmail as col_105_0_,
        user0_.DigestFrequency as col_106_0_,
        user0_.UserPreferencesSRHOverrideActivities as col_107_0_,
        user0_.UserPermissionsOfflineUser as col_108_0_,
        user0_.UserPreferencesHideCSNDesktopTask as col_109_0_,
        user0_.UserPreferencesContentEmailAsAndWhen as col_110_0_,
        user0_.UserPreferencesShowForecastingChangeSignals as col_111_0_,
        user0_.ManagerId as col_112_0_,
        user0_.OfflinePdaTrialExpirationDate as col_113_0_,
        user0_.UserPreferencesTaskRemindersCheckboxDefault as col_114_0_,
        user0_.UserPreferencesFavoritesShowTopFavorites as col_115_0_,
        user0_.UserPreferencesDisableFollowersEmail as col_116_0_,
        user0_.salesedgedemo__Can_View_Not_Assigned_Prospects__c as col_117_0_,
        user0_.UserPreferencesHasCelebrationBadge as col_118_0_,
        user0_.UserPreferencesShowTitleToExternalUsers as col_119_0_,
        user0_.Extension as col_120_0_,
        user0_.UserPreferencesShowManagerToGuestUsers as col_121_0_,
        user0_.AccountId as col_122_0_,
        user0_.OutOfOfficeMessage as col_123_0_,
        user0_.UserPreferencesReceiveNotificationsAsDelegatedApprover as col_124_0_,
        user0_.SmallBannerPhotoUrl as col_125_0_,
        user0_.UserPreferencesShowCityToGuestUsers as col_126_0_,
        user0_.UserPreferencesDisableLikeEmail as col_127_0_,
        user0_.Division as col_128_0_,
        user0_.EmailPreferencesAutoBccStayInTouch as col_129_0_,
        user0_.salesedgedemo__Pardot_User_Key__c as col_130_0_,
        user0_.SBQQ__ProductSortPreference__c as col_131_0_,
        user0_.Latitude as col_132_0_,
        user0_.UserPermissionsSiteforcePublisherUser as col_133_0_,
        user0_.UserPreferencesShowPostalCodeToExternalUsers as col_134_0_,
        user0_.EmailPreferencesAutoBcc as col_135_0_,
        user0_.StayInTouchNote as col_136_0_,
        user0_.UserPreferencesGlobalNavGridMenuWTShown as col_137_0_,
        user0_.Email as col_138_0_,
        user0_.UserPreferencesContentNoEmail as col_139_0_,
        user0_.UserPreferencesRecordHomeSectionCollapseWTShown as col_140_0_,
        user0_.UserPreferencesDisableChangeCommentEmail as col_141_0_,
        user0_.UserPreferencesRecordHomeReservedWTShown as col_142_0_,
        user0_.salesedgedemo__Pardot_Api_Key__c as col_143_0_,
        user0_.UserPreferencesHideBiggerPhotoCallout as col_144_0_,
        user0_.SmallPhotoUrl as col_145_0_,
        user0_.UserPreferencesShowStreetAddressToExternalUsers as col_146_0_,
        user0_.UserPreferencesShowCityToExternalUsers as col_147_0_,
        user0_.GeocodeAccuracy as col_148_0_,
        user0_.UserPreferencesHideSfxWelcomeMat as col_149_0_,
        user0_.CallCenterId as col_150_0_,
        user0_.UserPreferencesCreateLEXAppsWTShown as col_151_0_,
        user0_.UserPreferencesDisableBookmarkEmail as col_152_0_,
        user0_.Branding_Template__c as col_153_0_,
        user0_.Street as col_154_0_,
        user0_.UserPreferencesShowCountryToGuestUsers as col_155_0_,
        user0_.UserPreferencesJigsawListUser as col_156_0_,
        user0_.Consumer_User__c as col_157_0_,
        user0_.UserPreferencesDisableMessageEmail as col_158_0_,
        user0_.EmailEncodingKey as col_159_0_,
        user0_.UserPermissionsLiveAgentUser as col_160_0_,
        user0_.UserPreferencesShowFaxToExternalUsers as col_161_0_,
        user0_.UserPermissionsChatterAnswersUser as col_162_0_,
        user0_.ForecastEnabled as col_163_0_,
        user0_.AccountID__c as col_164_0_,
        user0_.salesedgedemo__Pardot_User_Id__c as col_165_0_,
        user0_.UserPreferencesHideChatterOnboardingSplash as col_166_0_,
        user0_.IsProfilePhotoActive as col_167_0_,
        user0_.UserPreferencesShowCountryToExternalUsers as col_168_0_,
        user0_.MobilePhone as col_169_0_,
        user0_.UserPermissionsSiteforceContributorUser as col_170_0_,
        user0_.CompanyName as col_171_0_,
        user0_.UserPreferencesDisableLaterCommentEmail as col_172_0_,
        user0_.UserPermissionsSupportUser as col_173_0_,
        user0_.UserPreferencesPreviewCustomTheme as col_174_0_,
        user0_.UserPreferencesShowStreetAddressToGuestUsers as col_175_0_,
        user0_.UserPreferencesGlobalNavBarWTShown as col_176_0_,
        user0_.LocaleSidKey as col_177_0_,
        user0_.UserPreferencesActivityRemindersPopup as col_178_0_,
        user0_.UserPreferencesUserDebugModePref as col_179_0_,
        user0_.UserPreferencesApexPagesDeveloperMode as col_180_0_,
        user0_.UserPreferencesPathAssistantCollapsed as col_181_0_,
        user0_.Title as col_182_0_,
        user0_.UserPermissionsCallCenterAutoLogin as col_183_0_,
        user0_.LastModifiedDate as col_184_0_,
        user0_.UserPreferencesShowTitleToGuestUsers as col_185_0_,
        user0_.Country as col_186_0_,
        user0_.EDY_ORGCUSTOM__Old_First_Name__c as col_187_0_,
        user0_.UserPreferencesShowWorkPhoneToExternalUsers as col_188_0_,
        user0_.UserPreferencesDisableProfilePostEmail as col_189_0_,
        user0_.EmailPreferencesStayInTouchReminder as col_190_0_,
        user0_.Employee_Number__c as col_191_0_,
        user0_.LastName as col_192_0_,
        user0_.salesedgedemo__Pardot_User_Role__c as col_193_0_,
        user0_.State as col_194_0_,
        user0_.UserPreferencesShowStateToGuestUsers as col_195_0_,
        user0_.UserPreferencesDisableAllFeedsEmail as col_196_0_,
        user0_.CreatedDate as col_197_0_ 
    from
        User user0_ limit ?
2023-02-02 18:24:34.361  WARN 136 --- [nio-4000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2023-02-02 18:24:34.362 ERROR 136 --- [nio-4000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariPool-1 - Connection is not available, request timed out after 30001ms.
2023-02-02 18:24:34.373  INFO 136 --- [nio-4000-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    30002072916 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    30004677381 nanoseconds spent preparing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

There are 77 entries in the table.

[kishanp519]

Made adjustments on my end, fixed. Sorry for the issue.