What is a good mysql configuration for production setting?
The setting I am using currently is
dataSource {
pooled = true
driverClassName = "com.mysql.jdbc.Driver"
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
properties {
maxActive = 25
maxIdle = 10
minIdle = 1
initialSize = 1
maxWait = 10000
minEvictableIdleTimeMillis = 1800000
timeBetweenEvictionRunsMillis = 1800000
testOnBorrow = true
testOnReturn = true
testWhileIdle = true
validationQuery = "SELECT 1"
}
}
hibernate {
cache.use_second_level_cache = true
cache.use_query_cache = false
cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}
Sometimes I have been seeing
Cannot get a connection, pool error Timeout waiting for idle object
error. So i wanted to make sure our settings is good for production usage. The grails version is 2.2 but i assume these configuration are similar across newer versions.
The application is a low traffic registration system. The customers come, register and go. The user fills registration form and checks out. The number of registrations is low i.e 30-50 per day. The total traffic per day is less than 1000.
What production settings would you recommend for this scenario?
Thanks for the insights!
UPDATE:
I would like to add that recently the service went down for few hours. Around the time when the database crashed. i.e
the app started giving the following errors
ERROR 2021-05-17 15:50:31,726 [ajp-bio-8109-exec-32102] util.JDBCExceptionReporter: Cannot get a connection, pool error Timeout waiting for idle object
ERROR 2021-05-17 15:50:42,715 [ajp-bio-8109-exec-32106] util.JDBCExceptionReporter: Cannot get a connection, pool error Timeout waiting for idle object
ERROR 2021-05-17 15:51:03,596 [ajp-bio-8109-exec-32073] util.JDBCExceptionReporter: Cannot get a connection, pool error Timeout waiting for idle object
ERROR 2021-05-17 15:51:22,492 [quartzScheduler_Worker-1] util.JDBCExceptionReporter: Cannot get a connection, pool error Timeout waiting for idle object
the log file recorded a lot of get queries to a single endpoint reservation/create around the time the database gave up. I am not entirely sure at this point whether the database failure was caused by this flood of get queries but it doesnt seem very unlikely.
- - [17/May/2021:14:55:33 -0600] "GET /reservation/create/3415;jsessionid=BF6034DD587A862E45E19049ED629F0B?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:14:55:49 -0600] "GET /reservation/create/3415;jsessionid=FCB49864278884B3D049B03C6F6BE1D3 HTTP/1.1" 302 -
- - [17/May/2021:14:56:10 -0600] "GET /reservation/create/3415;jsessionid=FCB49864278884B3D049B03C6F6BE1D3?execution=e1s1 HTTP/1.1" 200 18300
- - [17/May/2021:14:56:38 -0600] "GET /reservation/create/3415;jsessionid=C20BF9437E3F9772CB0147874A8E1A5E?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:14:57:01 -0600] "GET /reservation/create/3415;jsessionid=75CCDD42A4C9D680EB5880AB65C11E53 HTTP/1.1" 302 -
- - [17/May/2021:14:57:34 -0600] "GET /reservation/create/3415;jsessionid=75CCDD42A4C9D680EB5880AB65C11E53?execution=e1s1 HTTP/1.1" 200 18300
- - [17/May/2021:14:57:50 -0600] "GET /reservation/create/3415;jsessionid=C2C8350D9F589290BC9395439278B33D?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:14:58:17 -0600] "GET /reservation/create/3415;jsessionid=C9BD5A253A5F44B10C3B1883135B62E4 HTTP/1.1" 302 -
- - [17/May/2021:14:58:38 -0600] "GET /reservation/create/3415;jsessionid=C9BD5A253A5F44B10C3B1883135B62E4?execution=e1s1 HTTP/1.1" 200 18300
- - [17/May/2021:14:58:54 -0600] "GET /reservation/create/3415;jsessionid=D56DC42D083150F35F519AFAE9E63815?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:14:59:16 -0600] "GET /reservation/create/3415;jsessionid=F3339DA78CC22C0FE16462FD5AB03EF9 HTTP/1.1" 302 -
- - [17/May/2021:14:59:37 -0600] "GET /reservation/create/3415;jsessionid=F3339DA78CC22C0FE16462FD5AB03EF9?execution=e1s1 HTTP/1.1" 200 18300
- - [17/May/2021:14:59:56 -0600] "GET /reservation/create/3415;jsessionid=E38AF8831BB9E6734A594AA1D23C860C?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:00:14 -0600] "GET /reservation/create/3415;jsessionid=2C7EAF17EB5C3BA7FACE0795E72D5A3D HTTP/1.1" 302 -
- - [17/May/2021:15:00:39 -0600] "GET /reservation/create/3415;jsessionid=2C7EAF17EB5C3BA7FACE0795E72D5A3D?execution=e1s1 HTTP/1.1" 200 18300
- - [17/May/2021:15:01:04 -0600] "GET /reservation/create/3415;jsessionid=F2C2CB5FBD8FF4843478D7DA7C49F758?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:01:30 -0600] "GET /reservation/create/3415;jsessionid=5EDB0D5E341572E8C7FE4BA664C73D27 HTTP/1.1" 302 -
- - [17/May/2021:15:01:54 -0600] "GET /reservation/create/3415;jsessionid=5EDB0D5E341572E8C7FE4BA664C73D27?execution=e1s1 HTTP/1.1" 200 18300
- - [17/May/2021:15:02:15 -0600] "GET /reservation/create/3415;jsessionid=F63900CF8466A570B4E6036AAF46D8E7?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:02:40 -0600] "GET /reservation/create/3415;jsessionid=4AF46A2D17805B85A560AAAF53BE7458 HTTP/1.1" 302 -
- - [17/May/2021:15:03:02 -0600] "GET /reservation/create/3415;jsessionid=4AF46A2D17805B85A560AAAF53BE7458?execution=e1s1 HTTP/1.1" 200 18300
- - [17/May/2021:15:03:27 -0600] "GET /reservation/create/3415;jsessionid=FB55E60BEF5C8154534929A51F0CE35A?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:03:50 -0600] "GET /reservation/create/3415;jsessionid=52B458F93E8C0077B2F8B1FB65739BE7 HTTP/1.1" 302 -
- - [17/May/2021:15:28:22 -0600] "GET /reservation/create/3226;jsessionid=002E454E14F353CF16B6AE27104CB41D?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:28:48 -0600] "GET /reservation/create/3226;jsessionid=D97F3C15CAAF91C97ACBEC3001C82C30 HTTP/1.1" 302 -
- - [17/May/2021:15:29:00 -0600] "GET /reservation/create/3226;jsessionid=D97F3C15CAAF91C97ACBEC3001C82C30?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:29:32 -0600] "GET /reservation/create/3226;jsessionid=008DF7B94000C7F9441138171DBE492E?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:29:56 -0600] "GET /reservation/create/3226;jsessionid=41F963C3411D4D9F3C601AA63F7353E7 HTTP/1.1" 302 -
- - [17/May/2021:15:30:20 -0600] "GET /reservation/create/3226;jsessionid=41F963C3411D4D9F3C601AA63F7353E7?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:30:40 -0600] "GET /reservation/create/3226;jsessionid=009EBF9D72B8FA292EA3781528CF281B?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:31:02 -0600] "GET /reservation/create/3226;jsessionid=0230C2E7F93F977DA8469F354F1C40BD HTTP/1.1" 302 -
- - [17/May/2021:15:31:22 -0600] "GET /reservation/create/3226;jsessionid=0230C2E7F93F977DA8469F354F1C40BD?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:31:36 -0600] "GET /reservation/create/3226;jsessionid=254E1DBF7171196CA4A94CC523F215A5?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:31:52 -0600] "GET /reservation/create/3226;jsessionid=15D662CD593432E9CF0034937764A1FE HTTP/1.1" 302 -
- - [17/May/2021:15:32:15 -0600] "GET /reservation/create/3226;jsessionid=15D662CD593432E9CF0034937764A1FE?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:32:28 -0600] "GET /reservation/create/3226;jsessionid=307DD4149B987793F2A7EBDED637897D?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:32:57 -0600] "GET /reservation/create/3226;jsessionid=9571077FAC8AB54E5578733C88CEBB0E HTTP/1.1" 302 -
- - [17/May/2021:15:33:15 -0600] "GET /reservation/create/3226;jsessionid=9571077FAC8AB54E5578733C88CEBB0E?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:33:36 -0600] "GET /reservation/create/3226;jsessionid=35767A7B28D0BD465A1FD24357A4E9DD?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:33:59 -0600] "GET /reservation/create/3226;jsessionid=4394E25FC667BD61EE06E2BF32908579 HTTP/1.1" 302 -
- - [17/May/2021:15:34:25 -0600] "GET /reservation/create/3226;jsessionid=4394E25FC667BD61EE06E2BF32908579?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:34:45 -0600] "GET /reservation/create/3226;jsessionid=3F69B07D938BCFF26C7CC94080B896AB?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:35:05 -0600] "GET /reservation/create/3226;jsessionid=EFD56E58BA76C4009B644C3D30BAF100 HTTP/1.1" 302 -
- - [17/May/2021:15:35:29 -0600] "GET /reservation/create/3226;jsessionid=EFD56E58BA76C4009B644C3D30BAF100?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:35:54 -0600] "GET /reservation/create/3226;jsessionid=4617BD51C657D61707DFE9CAB8DA2E82?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:36:11 -0600] "GET /reservation/create/3226;jsessionid=1309B3F5DE1F281A1DFD1DA5AD687258 HTTP/1.1" 302 -
- - [17/May/2021:15:36:31 -0600] "GET /reservation/create/3226;jsessionid=1309B3F5DE1F281A1DFD1DA5AD687258?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:36:47 -0600] "GET /reservation/create/3226;jsessionid=769DE9F3CE2018F2DD82AFFB6B28C647?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:37:06 -0600] "GET /reservation/create/3226;jsessionid=BDC16622E0B4CA13530F6D30386D9EB3 HTTP/1.1" 302 -
- - [17/May/2021:15:37:27 -0600] "GET /reservation/create/3226;jsessionid=BDC16622E0B4CA13530F6D30386D9EB3?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:37:51 -0600] "GET /reservation/create/3226;jsessionid=7BB4CA813DD741901751BB240F84127E?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:38:27 -0600] "GET /reservation/create/3226;jsessionid=8AF1B5443AF64F681F0126AF1F637C9F HTTP/1.1" 302 -
- - [17/May/2021:15:38:54 -0600] "GET /reservation/create/3226;jsessionid=8AF1B5443AF64F681F0126AF1F637C9F?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:39:13 -0600] "GET /reservation/create/3226;jsessionid=81968600051B9D6DB2541E7DA824A98B?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:39:39 -0600] "GET /reservation/create/3226;jsessionid=5DF27A28AC7FB74C88877D680FAD03FC HTTP/1.1" 302 -
- - [17/May/2021:15:40:08 -0600] "GET /reservation/create/3226;jsessionid=5DF27A28AC7FB74C88877D680FAD03FC?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:40:35 -0600] "GET /reservation/create/3226;jsessionid=DC4C2B8C3770B7E25E55AE39B1AA2781?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:40:55 -0600] "GET /reservation/create/3226;jsessionid=F9A7F6E8A4E340193183BE07D4F8C691 HTTP/1.1" 302 -
- - [17/May/2021:15:41:24 -0600] "GET /reservation/create/3226;jsessionid=F9A7F6E8A4E340193183BE07D4F8C691?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:42:02 -0600] "GET /reservation/create/3226;jsessionid=E5CE9DC93523995C6733075E068B1AE5?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:42:34 -0600] "GET /reservation/create/3226;jsessionid=9284EB5FF25015D7A2DEB4BE0987E8A5 HTTP/1.1" 302 -
- - [17/May/2021:15:43:03 -0600] "GET /reservation/create/3226;jsessionid=9284EB5FF25015D7A2DEB4BE0987E8A5?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:43:22 -0600] "GET /reservation/create/3226;jsessionid=E7D16263E438D285385E6FA7AFE7DB0A?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:43:42 -0600] "GET /reservation/create/3226;jsessionid=DFABAD2FBC5C11B5DFC12570E9F29089 HTTP/1.1" 302 -
- - [17/May/2021:15:44:10 -0600] "GET /reservation/create/3226;jsessionid=DFABAD2FBC5C11B5DFC12570E9F29089?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:15:44:38 -0600] "GET /reservation/create/3227;jsessionid=56C373A6D19BA627EACF4E2C6B349E16?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:45:54 -0600] "GET /reservation/create/3227;jsessionid=7FC1A8350759E113395E8EC758DC1F7B HTTP/1.1" 302 -
- - [17/May/2021:15:46:03 -0600] "GET /reservation/create/3253;jsessionid=00F663170E5EC47D85A8BED9CEF66BD8?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:49:31 -0600] "GET /reservation/create/3253;jsessionid=033CF4AE66A81865B1978C0523AE0C9E?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:49:41 -0600] "GET /reservation/create/3253;jsessionid=299997AF0D303D5CC83E3184F779E14E HTTP/1.1" 302 -
- - [17/May/2021:15:50:00 -0600] "GET /reservation/create/3253;jsessionid=179E3C307B9350C73E647A37FB0255FD?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:50:31 -0600] "GET /reservation/create/3253;jsessionid=182DE157EFF21137BFEB4ED869007080?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:52:55 -0600] "GET /reservation/create/3253;jsessionid=186F5190B5A758F7FF278D1BB043865F?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:53:22 -0600] "GET /reservation/create/3253;jsessionid=18BC5E1989115FA3FB32365291ED75CA?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:59:41 -0600] "GET /reservation/create/3253;jsessionid=18D1E1E83728FFF0A1F3A21C17EBBDF4?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:15:59:41 -0600] "GET /reservation/create/3253;jsessionid=18F8C849ED51EEFBEBD61FBA20B328C5?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:19:55:14 -0600] "GET /reservation/create/3316;jsessionid=2958FEB4DBF9CDF4AF4CB52805B15FC5?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:19:55:14 -0600] "GET /reservation/create/3316;jsessionid=2A2B008629121F46FA914F2A306D2071?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:19:55:33 -0600] "GET /reservation/create/3316;jsessionid=D6C9E2A2359CC6C76DC6375D1B8C19B7 HTTP/1.1" 302 -
- - [17/May/2021:19:56:01 -0600] "GET /reservation/create/3316;jsessionid=D6C9E2A2359CC6C76DC6375D1B8C19B7?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:19:56:20 -0600] "GET /reservation/create/3316;jsessionid=2A4F2F2341662340B4F10ACA6B8A853C?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:19:56:38 -0600] "GET /reservation/create/3316;jsessionid=1732E35B9F75C16AA7C2198FEA24F32B HTTP/1.1" 302 -
- - [17/May/2021:19:57:00 -0600] "GET /reservation/create/3316;jsessionid=1732E35B9F75C16AA7C2198FEA24F32B?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:19:57:26 -0600] "GET /reservation/create/3316;jsessionid=2A73DECA2A398F06B6D6CA74A35502D7?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:19:57:43 -0600] "GET /reservation/create/3316;jsessionid=71391DCD368A885C7CCB77C1AB8CD65C HTTP/1.1" 302 -
- - [17/May/2021:19:58:14 -0600] "GET /reservation/create/3316;jsessionid=71391DCD368A885C7CCB77C1AB8CD65C?execution=e1s1 HTTP/1.1" 200 12764
- - [17/May/2021:19:58:36 -0600] "GET /reservation/create/3316;jsessionid=2A8895DDAF229077C626AD0AA8816BD4?execution=e1s1 HTTP/1.1" 302 -
- - [17/May/2021:19:59:01 -0600] "GET /reservation/create/3316;jsessionid=5032E139718E25F210400D205D498A3F HTTP/1.1" 302 -
- - [17/May/2021:19:59:31 -0600] "GET /reservation/create/3316;jsessionid=5032E139718E25F210400D205D498A3F?execution=e1s1 HTTP/1.1" 200 12764
What you have doesn't look bad. Of course, you also say you're getting pool timeouts so clearly something isn't working!
I'd start by looking on the database side. Check the max connections allowed (default is 151, so that's probably not your limit unless someone turned it down, or it's a shared database). Also look at the active client connections, ideally at or near when you're seeing pool errors.
If you are frequently using at or near your max connections on the database side, turn that up. (DBA might want to weigh in on reasonable limits, based on your database server/container/whatever size.)
If you are seeing a lot (but not 151/max) of long-running client connections, look at what queries they're running and try to see if it's possible to rearchitect those parts of your application to use more granular connections. If it's not, you'll at least know you need to turn up the size of your client pool.
Try tweaking some of the numbers in an environment that approximates your production. There's nothing inherently wrong with having maxActive and maxIdle be the same, or even increasing minIdle if you want to increase your chances of having a valid connection in the pool when you need it. If your environment is pretty stable, you can also turn off testOn* to decrease the overhead of getting a new connection, though that is probably minimal impact.
It's not unthinkable that you might be leaking connections, which would mean that eventually you'll be unable to get a new one from the (now full but useless) pool. If this starts happening more frequently after your application has been running for a while, I'd pursue that. Logging would probably show you what you need to know, but a heap analysis certainly would if logging isn't sufficient.