On Thursday, August 23, 2012 8:05:02 PM UTC-7, Ravi wrote:
>
> That is good thing to know, I will check the configuration and try to
> increase number of connection if possible.
>
Hopefully that will fix the issue.
> You can't change the number of allowed connections from Sequel, you need
>> to change your MySQL database configuration to increase the number of
>> connections. The only work around in Sequel is lowering the number of
>> connections that Sequel will create (like you saw, the default is 4). You
>> mentioned raising the number from 4 to 30, which could be causing your
>> problem. Do you get the same error message when running with the default
>> of 4 connections?
>>
>
> I got same error before (2-3 times) with default :max_connection settings
> so I have increased the :max_connections variable to 30 and that worsen the
> problem.
> Since there are more than 50 instances/thread that update the database at
> same time and if I lower :max_connections; wont the Sequal will throw
> Sequel::PoolTimeout error.. if it could not connect to all 50 instances.
>
It's true that if more threads request a connection than there are
connections in the pool, some of the threads will have to wait for a
connection, and if they wait too long, you'll get a PoolTimeout. But you
should not need a 50-connection pool to serve 50 threads unless each thread
is using the database 100% of the time.
> The other workaround I was thinking as following
>
> attempt = 1
> begin
> DB = Sequel.connect(....)
> rescue Sequel::DatabaseConnectionError
> if attempt<6
> DB.disconnect #it will serve all open connections first
> sleep(attempt*2)
> attempt +=1
> retry
> else
> raise_error("reached max attempts")
> end
> rescue => e
> raise_error(...)
> end
>
This is a bad idea, more likely to cause additional problems than fix
existing problems.
> Also can the other variables ( :pool_sleep_time=>0.01, :pool_timeout=>30)
> do this work for me?
>
>>
>> Note that you can have a connection pool with 4 connections serve 50
>> threads, as long as each thread on average accesses the database 8% of the
>> time.
>>
> What error will Sequel raise if it crosses the above criteria?
>
With that configuration, you'll get an error if a thread waits more than 30
seconds and still cannot acquire a connection. You might want to increase
the sleep time, as otherwise you can end up with a lot of threads waking
up, seeing there are no available connections, and going back to sleep.
Also the way Sequel's connection pools currently work, threads are not
serviced in order of request. So you can end up with a situation like this:
1) thread A requests a connection from the pool, none in pool, so sleeps
for sleep time
2) thread B returns a connection to the pool
3) thread B requests a connection from the pool, gets the one it just
returned
4) go to 1)
This is obviously a non-optimal situation, but it's not a major issue as in
general practice you should make sure your connection pool is large enough
that threads are rarely if ever waiting for connections.
I'd be open to fixing this so that thread A gets the connection immediately
when thread B checks it in, but it requires a Queue class where Queue#pop
accepts a timeout. Currently, ruby's Queue class only takes a single
non-block flag, so it isn't suitable.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/DkjT8pv5A6AJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
