understanding of maxpoolsize

[yiwong2001]

Hi there,

I want to have better understanding of maxpoolSize configuration.

As you saw below, I have 1 master node and 5 slave nodes. Each of nodes can handle max to 1000 connections.

I wonder what is value I should set in maxpoolSize. @swaldman, you mentioned before that there would be a maximum of 1000 Connections on the client (I set to 1k now), so total to all servers would be max 1000. If I increase maxpoolSize to 4000, will Master and slave nodes all have 4000/6 each? I want to set correct number for better performance but do not want to break master node since it can only handle 1000 connections :)

Can you give me some suggestions?

Thanks!

[swaldman]

1000 is a really big value for maxPoolSize. Be sure to set a high numHelperThreads. Your performance will probably still suffer from Thread contention.

I don't know what you mean in this context by master and slave nodes. Do you have a replicated database, for each copy of which you keep a separate DataSource? I don't understand your topology, so I'm not sure I can answer your questions.

[yiwong2001]

Hi swaldman,

thanks for quick response.

We are using amazon RDs mysql databases. They are dedicated memory optimized db servers. For each of db server, it can handle 1200 max connections.

We run 1 master database for writing data, and 5 read replicas/slaves to sharing read requests. I only configure one data source in our application. We have this structure try to split up read/write request on databases.

Below is our configures.

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">  
<property name="driverClass" value="com.mysql.jdbc.ReplicationDriver"/>  
 <property name="jdbcUrl" value="jdbc:mysql://db-prod-db.cb9tere52i0u.eu-west-1.rds.amazonaws.com:3306,db-prod-db-replica1.cb9tere52i0u.eu-west-1.rds.amazonaws.com:3306,db-prod-db-replica2.cb9tere52i0u.eu-west-1.rds.amazonaws.com:3306,db-prod-db-replica3.cb9tere52i0u.eu-west-1.rds.amazonaws.com:3306,db-prod-db-replica4.cb9tere52i0u.eu-west-1.rds.amazonaws.com:3306,db-prod-db-replica5.cb9tere52i0u.eu-west-1.rds.amazonaws.com:3306/mydb?autoReconnect=true&amp;failOverReadOnly=false&amp;roundRobinLoadBalance=true"/> 

<property name="maxPoolSize" value="1000"/>
		<property name="minPoolSize" value="300"/>  
		<property name="acquireIncrement" value="5"/>  
		<property name="maxIdleTime" value="300"/>  
		<property name="maxStatements" value="0"/>
		<property name="numHelperThreads" value="3"/>
		<property name="breakAfterAcquireFailure" value="false"/>
		<property name="testConnectionOnCheckout" value="false"/>
		<property name="checkoutTimeout" value="5000"/>  
		<property name="acquireRetryDelay" value="1000"/>
		<property name="acquireRetryAttempts" value="60"/>
		<property name="idleConnectionTestPeriod" value="60"/><!-- check idle connection every 14400 seconds -->
		<property name="properties">  
			<props>
				<prop key="user">root</prop>
				<prop key="password">rootpasswrd</prop>
             </props>
		</property>
	</bean>

[yiwong2001]

In addition, what is correct value should set on numHelperThreads based on connection numbers?

[swaldman]

So, I think you'll end up with up to 1000 Connections total, with reads quietly directed to your replicas and writes going to your master. You'll want a much bigger numHelperThreads, something like 30 instead of 3. (Ideally you'll monitor for a back log in the thread pool via JMX to make sure it's keeping up.)

This is a very large scale for a single DataSource, and Thread contention may well become an issue. To prevent that, you might define multiple DataSources and have clients round-robin or randomly choose between them. (Eventually, I hope to add a facade for multiple pools behind a single DataSource facade, but I have not done that yet, so you'd have to implement that logic yourself.)

Before you go to the trouble of that, give a hard thought to your pool size. Even if your DBMS can handle 1000 simultaneous Connections, you may want to choose a much smaller maxPoolSize. See this, by the author of HikariCP. Everything is empirical. I don't know the details of your replicated AWS setup. But you might try with a much more modest maxPoolSize (50 - 100), and compare your application's performance to when you have a very large setting. You may find it does not suffer.

[yiwong2001]

Thanks @swaldman .. will 30 of numHelperThreads generate 300 threads? is it fine for one single web server? what value I should set in acquireIncrement, is it need to be bigger than numberhelperthreads? Some people suggest to set same value in minPoolSize and maxPoolSize, is it true for good performance

I am sorry for so many questions :)

[swaldman]

No, numHelperThreads of 30 will create a thread pool with 30 threads. That's all. There's no obvious relationship between numHelperThreads and acquireIncrement, but given so large a pool you might want an acquireIncrement a bit larger than its default of 3. Setting maxPoolSize and minPoolSize the same effectively gives you a fixed-size pool, rather than one that grows and potentially shrinks (depending on other settings) in response to load.