Pool Fragmentation

[sabbir]

Hi,
I have a Company table in my master database.There are 200 rows(each for one company) in that table.Each company has its own database according to there name in Company table in master database.so i have 200 other databases.I have a windows service running continuously in a specified intervals.Windows service actually import data from different ERP & Accounting systems and also export to those systems.in each run, it compares a field named "LastExecutedTime" in Company table to estimate numbers of companies, for which it needs to import and export in this run.The service create same number(numbers of companies) of parrell task and import & export data.

In the each parallel task, after import/export, it connections to different databases in my server to save imported data.So if the number is 20, it is creating 21 connection pools(20 different databases and one master Database).As the number of company is increasing i have to do the pool fragmentation.

https://docs.microsoft.com/en-us/dotnet ... on-pooling shows the "Pool Fragmentation Due to Many Databases", it suggest to connect with master database and use Transact-SQL USE statement to change to the desired database, it this case it will create one connection pool and i need to increase MaxPoolSize to 200.As number of companies is increasing day by day, i need to set a big value to MaxPoolSize for safety.This is one solution.

https://msdn.microsoft.com/en-us/librar ... L.80).aspx said "In an efficient production system, typically the number of pools is low (1 to 10) and the total number of connections in use is also low (fewer than 12)".I have another solution in my mind.Is it possible to use custom key value pair in connection string? if possible, i will distribute this value to 200 companies.20 companies will fall with each value. i will use this value with connection string of master database to create different pools for different key/values. In this way it will allow me to maximum 10* 100 = 1000 connections with 10 connection pools.i don't need to increase the MaxPoolSize

Is there anything wrong with my second solution?

[Pinturiccio]

We can offer you a third variant with using one pool. You create a connection to your database master, for example, with the following connection string:

Code: Select all

"Host=<HOST>;port=3306;user id=<USER ID>;password=<PASSWORD>;Max Pool Size=1000;database=master"

When you need to connect to another database, create and execute the following command after opening the connection:

Code: Select all

MySqlCommand comm = new MySqlCommand("use database1", conn);

where database1 is the database you want to connect. After this, the connection will use database1, and it will be placed to the pool when closed. If you are sure that you will always have at least 20 open connections, you can assign the corresponding number for the "Min Pool Size" connection string parameter for all connections.

Such approach has its benefits. For example, you had 20 open connections, and thus, 21 pools were created. After this, connections are closed, but 20 pools still exist. If new 20 connections to new databases are open, you will have 41 pools with a small number of connections. With changes that we offer, any closed connection is placed to the same pool, and can be reused for connecting to another database.

When under peak load, the number of connections in a pool can be close to 1000. In this case you can call the ClearPool or ClearAllPools method periodically for clearing unused connections left in the pool after peak load. For example, you can do it once per hour. For more information, please refer to
https://www.devart.com/dotconnect/mysql ... Pools.html
https://www.devart.com/dotconnect/mysql ... rPool.html

[sabbir]

Hi Pinturiccio,
Thanks for your reply. Sorry for my late.

You third variant is almost similar with the first one.But you gave a very good explanation and that help me a lot.i want to implement the way you showed because my second variant is a bit difficult to implement according to my project. I have few questions
1.Is there any maximum limit of Max Pool Size?
2.Is there any limit of Number of Pools?
3.How can i check the peak load? By checking the number of users online?

Please answer when you have time

Best Regards
Sabbir

[Pinturiccio]

1.Is there any maximum limit of Max Pool Size?
2.Is there any limit of Number of Pools?

There are no limits for the number of pools or for the Max Pool Size. They are limited only by Int32 value.

3.How can i check the peak load? By checking the number of users online?

We mean a lot of users simultaneously by peak load. In this case many connections are simultaneously created, and after this, when there are less users, the pool will contain a lot of unused connections. The ClearPool and ClearAllPools methods remove unused connections from the pool. Thus, you will free the unused connections. You can set a timer for clearing pool, for example, every 10 minutes. Connections that are in use, won't be affected by these methods.

[sabbir]

Thanks a lot
I understand correctly

Thanks
sabbir