sqlite3 and sessions

[GoogleCodeExporter]

I've made some load tests with a webservice I've developped with Staff.
The service only gets the params from the request, display them into the logs 
and makes a response.
Here are some results.

Users ; Req/user ; Total ; %Errors ; 
20    ; 300  ; 6000  ; 4.98%   ; 115 req/sec
20    ; 3000     ; 60000 ; 15%     ; 99 req/s
10    ; 3000     ; 30000 ; 5%      ; 106 req/s
10    ; 1 hour   ; 236969; 8%      ; 106 req/s

There is a random number of errors (but always the same error).

Into the staff logs:
faultstring: src/ServiceInstanceManager.cpp[226] GetServiceInstance: Session 
does not exists: 00000000000000000000000000000000

I thought the problem came from the sqlite3 database used by all the processes. 
So I modified the ./staff/core/security/src/Sessions.cpp
In each function juste after "sqlite3* pDb = DbConn::GetDb();" I added 
"sqlite3_busy_timeout(pDb,1000);"
"This routine sets a busy handler that sleeps for a specified amount of time 
when a table is locked."
(http://www.sqlite.org/c3ref/busy_timeout.html)

We used that in another project with 2 Apache modules using the same sqlite3 db 
at the same time.

Here are the new results.

Users ; Req/user ; Total ; %Errors ; 
20    ; 300  ; 6000  ; 0%      ; 165.7 req/sec (one time : 0.05% errors)
20    ; 3000     ; 60000 ; 0%      ; 153.7 req/s
10    ; 3000     ; 30000 ; 0.03%   ; 152.9 req/s

Now into the logs (when there are errors) => same problem but the "busy 
timeout" have reduced the number

Into staff logs:
src/tools.cpp[75] staff_security_calculate_access_by_session_id: Exception: 
src/Sessions.cpp[403] GetUserId: database is locked

Into Apache/axis2c logs:
[error] phase.c(233) Handler StaffSecurity invoke failed within phase 
PreDispatch
[error] engine.c(696) Invoking phase PreDispatch failed
[error] engine.c(255) Invoking pre-calculated phases failed

Apache prefork config used :
<IfModule prefork.c>
StartServers            1
MinSpareServers         1
MaxSpareServers         1
ServerLimit             256
MaxClients              256
MaxRequestsPerChild     4000
</IfModule>

== What version of the product are you using? On what operating system? ==
r710 and RHEL 5.2 x86_64

Original issue reported on code.google.com by julien.d...@gmail.com on 3 Oct 2012 at 11:08

[GoogleCodeExporter]

Thank you for your report.

What about adding sqlite3_busy_timeout directly into DbConn::GetDb() method?

Also I think one second can be too small delay in case of server under heavy 
loading.

Original comment by loentar on 3 Oct 2012 at 6:29

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Yes I didn't see it but you can add sqlite3_busy_timeout directly into 
DbConn::GetDb() method.

Concerning the delay I don't know what could be the best value. Few seconds 
seems ok. I think it should be interesting to have this value as an environment 
variable like STAFF_LOG_LEVEL, STAFF_LOG_FILE...

Original comment by julien.d...@gmail.com on 4 Oct 2012 at 5:56

[GoogleCodeExporter]

I've made some tests with sqlite3_busy_timeout = 5000 into DbConn::GetDb() 
method.
Everything works better.
I have no error even if I use 

StartServers            5
MinSpareServers         5
MaxSpareServers         5

(20% error at the beginning with only sqlite3_busy_timeout == 1000)

Today the results are:
Users ; Req/user ; Total ; %Errors ; 
20    ; 3000     ; 60000 ; 0%      ; from 190 to 200 req/sec
Min: 8 ms
Max: 2158 ms

Original comment by julien.d...@gmail.com on 4 Oct 2012 at 12:31

[GoogleCodeExporter]

you can use now STAFF_SQLITE_BUSY_TIMEOUT environment variable to set value of 
sqlite3_busy_timeout().

Original comment by loentar on 4 Oct 2012 at 6:49

• Changed state: Started
• Added labels: Component-Core, Milestone-2.0-b1

[GoogleCodeExporter]

This issue was closed by revision r714.

Original comment by loentar on 4 Oct 2012 at 6:49

• Changed state: Fixed