RE: Concurrency test causes crash of MonetDB 5 server on Windows
Hello,
Guillaume: Thanks for the tip avoiding concurrency conflicts. We already considered that and changed to code to avoid such conflicts.
In the meanwhile I tested both on Windows and Linux machines and now it is clear that it is a problem specific to the Windows builds of MonetDB. The mserver5 on Windows starts with 2 threads serving requests as stated in the console after starting... # Serving database 'uniserv', using 2 threads
Then I found a statement about the thread implementation on Windows by Stefan Mangegold: https://www.monetdb.org/pipermail/developers-list/2010-July/003492.html
"However, we do not know how well the multi-threading works on Windows. We rely on pthreads, and their implementation on Windows might not be as efficient and/or stable as on Unix-like systems ..."
This is absolutely true! My tests show that mserver5 started with 2 threads always crashes after some time firing concurrent read requests. By the way, the windows machine has 2 physical and 2 logical cores. But if I do the same on a Linux machine (also with 2 threads) Mserver5 runs stable.
What is the solution to stabilize Mserver5 on Windows? Set the number of threads to 1 by setting the start option: --set gdk_nr_threads=1 In the M5server.bat script. After setting that, Mserver5 run stable on Windows, too.
Does anyone know better solutions?
Kind regards, Michael
-----Original Message----- From: users-list [mailto:users-list-bounces+michael.witt=uniserv.com@monetdb.org] On Behalf Of Guillaume Bassot Sent: Dienstag, 30. Juni 2015 10:26 To: Communication channel for MonetDB users Subject: Re: Concurrency test causes crash of MonetDB 5 server
Hello Michael,
From my experience, all "write" request executed concurrently always end with concurrency conflict. I'm talking of heavey request during several minutes, so it's easy to get concurrent access. Since i only use monetdb with a single process, stability was greatly improved.
Regards
Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot@inbox.fr mailto:gbassot@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
2015-06-30 10:09 GMT+02:00 <michael.witt@uniserv.com mailto:michael.witt@uniserv.com >:
Hi all,
It’s a pity that no one answered the question I asked a few weeks ago about crashes of MonetDB 5 server under windows 64 Bit.
So I think I offer more information about a scenario where the databases crashes. Hopefully someone could give me a hint how to improve stability of MonetDB under Windows 64 Bit with concurrent client connections.
We are using Monet DB for a couple of months and facing some graved issues with concurrent queries.
The problem is, when we query the database with two or more clients in a parallel, the database crashes!
These are the facts:
MonetDB server version: v11.19.9
================================
# MonetDB 5 server v11.19.9 "Oct2014-SP2"
# Serving database 'uniserv', using 2 threads
# Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
# Found 8.000 GiB available main-memory.
# Copyright (c) 1993-July 2008 CWI.
# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
# Visit http://www.monetdb.org/ http://www.monetdb.org/ for further information
# Listening for connection requests on mapi:monetdb://QADQSHWIN:50000/
# MonetDB/SQL module loaded
Server crashes also occur on versions v11.19.8 and v11.19.7.
Operation system:
=================
Microsoft Windows Server 2008 R2 Datacenter
Version 6.1.7601 Service Pack 1 Build 7601
hosted on VMWare Virtual Platform
x64-based PC
2x Processor Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz, 2800 Mhz, 1 Core(s), 1 Logical Processor(s)
Installed Physical Memory (RAM) 8,00 GB
Available Physical Memory 5,25 GB
Total Virtual Memory 16,0 GB
Server installation folder:
===========================
E:\Program Files\MonetDB\MonetDB5
Server startup script M5server.bat:
===================================
@REM Copyright August 2008-2014 MonetDB B.V.
@REM All Rights Reserved.
@echo off
setlocal
rem figure out the folder name
set MONETDB=%~dp0
rem remove the final backslash from the path
set MONETDB=%MONETDB:~0,-1%
rem extend the search path with our EXE and DLL folders
set PATH=%MONETDB%\bin;%MONETDB%\lib;%MONETDB%\lib\MonetDB5;%PATH%
rem prepare the arguments to mserver5 to tell it where to put the dbfarm
REM Uniserv: Don't use APPDATA, so set APPDATA to empty String.
set APPDATA=
if "%APPDATA%" == "" goto usevar
rem if the APPDATA variable does exist, put the database there
set MONETDBDIR=%APPDATA%\MonetDB5
set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\demo"
goto skipusevar
:usevar
rem if the APPDATA variable does not exist, put the database in the
rem installation folder (i.e. default location, so no command line argument)
REM Uniserv: Specify the DB installation folder
rem set MONETDBDIR=%MONETDB%\var\MonetDB5
set MONETDBDIR=%MONETDB%\db
set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\uniserv"
:skipusevar
rem the SQL log directory used to be in %MONETDBDIR%, but we now
rem prefer it inside the dbfarm, so move it there
if not exist "%MONETDBDIR%\sql_logs" goto skipmove
for /d %%i in ("%MONETDBDIR%"\sql_logs*) do move "%%i" "%MONETDBDIR%\dbfarm"%%~ni\sql_logs
rmdir "%MONETDBDIR%\sql_logs"
:skipmove
rem start the real server
"%MONETDB%\bin\mserver5.exe" --set "prefix=%MONETDB%" --set mapi_open=true --set mapi_port=50000 --set max_clients=1000 --set "exec_prefix=%MONETDB%" %MONETDBFARM% %*
if ERRORLEVEL 1 pause
endlocal
MonetDB JDBC Client:
================
We query the database via the latest MonetDB JDBC driver loaded from the central Maven repository:
<groupId>monetdb</groupId>
<artifactId>monetdb-jdbc</artifactId>
<version>2.14</version>
Database tables:
================
We create tables called "rule_result_<n>"
CREATE TABLE "dq_scorecard"."rule_results_1" ("id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, "record_id" VARCHAR(1024), "rule_1" INT, "rule_2" INT, ...);
Each table has about 50+ INT columns.
Query scenarios:
================
We have concurrent read access via JDBC driver and Spring Data Access Objects,
the query look like that:
*Client 1: 50 aggregate queries on 50 different columns on one database table like that:
SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_1" WHERE "rule_n" >= 0
*Client 2: 50 aggregate queries on 50 different columns on another database table like that:
SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_2" WHERE "rule_n" >= 0
*Client 3: 50 aggregate queries on 50 different columns on another database table like that:
SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_3" WHERE "rule_n" >= 0
All clients run concurrently, at the moment we don't use a connection pool and create a connection for every query.
During concurrent access the M5server crashes.
The server crashes when each table has 100 records! Our production tables can have up to 50. 000. 000 of records!
Kind regards,
Michael
From: users-list [mailto:users-list-bounces+michael.witt mailto:users-list-bounces%2Bmichael.witt =uniserv.com@monetdb.org mailto:uniserv.com@monetdb.org ] On Behalf Of michael.witt@uniserv.com mailto:michael.witt@uniserv.com Sent: Donnerstag, 28. Mai 2015 14:32 To: users-list@monetdb.org mailto:users-list@monetdb.org Subject: Concurrency test causes crash of MonetDB 5 server
Hi,
we experience a crash of MonetDB 5 server during tests of a multi-threaded client access via JDBC-Driver and MapiSocket:
The application is already optimized to avoid “concurrency conflicts” because of the “Optimistic Concurrency Control” concept.
The test simulates client access via two threads.
All update operations (INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE) are done in serial.
All read operations are done in parallel to other read operations or to update operations.
The test causes a crash of mserver5.exe after a few minutes.
If the test uses just one thread, all read and update operations are done in serial and the server does not crash.
What is the rule of thumb to avoid crashes when using several client threads?
Crash details:
Problem signature:
Problem Event Name: APPCRASH
Application Name: mserver5.exe
Application Version: 0.0.0.0
Application Timestamp: 55389f58
Fault Module Name: StackHash_8ec6
Fault Module Version: 6.1.7601.18247
Fault Module Timestamp: 521eaf24
Exception Code: c0000374
Exception Offset: 00000000000c4102
OS Version: 6.1.7601.2.1.0.144.8
Locale ID: 1031
Additional Information 1: 8ec6
Additional Information 2: 8ec62499039d1d8234b025a62c725eb7
Additional Information 3: f8f3
Additional Information 4: f8f3e520847c0684b15d4080171779ef
# MonetDB 5 server v11.19.9 "Oct2014-SP2"
# Serving database 'uniserv', using 4 threads
# Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
# Found 10.000 GiB available main-memory.
..
# MonetDB/SQL module loaded
Michael Witt
_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On Jul 06, 2015, at 16:29, michael.witt@uniserv.com wrote:
Hello,
Guillaume: Thanks for the tip avoiding concurrency conflicts. We already considered that and changed to code to avoid such conflicts.
In the meanwhile I tested both on Windows and Linux machines and now it is clear that it is a problem specific to the Windows builds of MonetDB. The mserver5 on Windows starts with 2 threads serving requests as stated in the console after starting... # Serving database 'uniserv', using 2 threads
Then I found a statement about the thread implementation on Windows by Stefan Mangegold: https://www.monetdb.org/pipermail/developers-list/2010-July/003492.html
"However, we do not know how well the multi-threading works on Windows. We rely on pthreads, and their implementation on Windows might not be as efficient and/or stable as on Unix-like systems ..."
This is absolutely true! My tests show that mserver5 started with 2 threads always crashes after some time firing concurrent read requests. By the way, the windows machine has 2 physical and 2 logical cores. But if I do the same on a Linux machine (also with 2 threads) Mserver5 runs stable.
Hai Michael,
Ok, this is both good news and bad news then. Good to know MonetDB is stable on Unix-like systems, but a Windows specific problem can be very difficult to debug. Anyway, I hope we’ll be able to look deeper into this next week.
Regards,
Jennie
What is the solution to stabilize Mserver5 on Windows? Set the number of threads to 1 by setting the start option: --set gdk_nr_threads=1 In the M5server.bat script. After setting that, Mserver5 run stable on Windows, too.
Does anyone know better solutions?
Kind regards, Michael
-----Original Message----- From: users-list [mailto:users-list-bounces+michael.witt=uniserv.com@monetdb.org] On Behalf Of Guillaume Bassot Sent: Dienstag, 30. Juni 2015 10:26 To: Communication channel for MonetDB users Subject: Re: Concurrency test causes crash of MonetDB 5 server
Hello Michael,
From my experience, all "write" request executed concurrently always end with concurrency conflict. I'm talking of heavey request during several minutes, so it's easy to get concurrent access. Since i only use monetdb with a single process, stability was greatly improved.
Regards
Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot@inbox.fr mailto:gbassot@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
2015-06-30 10:09 GMT+02:00 <michael.witt@uniserv.com mailto:michael.witt@uniserv.com >:
Hi all,
It’s a pity that no one answered the question I asked a few weeks ago about crashes of MonetDB 5 server under windows 64 Bit.
So I think I offer more information about a scenario where the databases crashes. Hopefully someone could give me a hint how to improve stability of MonetDB under Windows 64 Bit with concurrent client connections.
We are using Monet DB for a couple of months and facing some graved issues with concurrent queries.
The problem is, when we query the database with two or more clients in a parallel, the database crashes!
These are the facts:
MonetDB server version: v11.19.9
================================
# MonetDB 5 server v11.19.9 "Oct2014-SP2"
# Serving database 'uniserv', using 2 threads
# Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
# Found 8.000 GiB available main-memory.
# Copyright (c) 1993-July 2008 CWI.
# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
# Visit http://www.monetdb.org/ http://www.monetdb.org/ for further information
# Listening for connection requests on mapi:monetdb://QADQSHWIN:50000/
# MonetDB/SQL module loaded
Server crashes also occur on versions v11.19.8 and v11.19.7.
Operation system:
=================
Microsoft Windows Server 2008 R2 Datacenter
Version 6.1.7601 Service Pack 1 Build 7601
hosted on VMWare Virtual Platform
x64-based PC
2x Processor Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz, 2800 Mhz, 1 Core(s), 1 Logical Processor(s)
Installed Physical Memory (RAM) 8,00 GB
Available Physical Memory 5,25 GB
Total Virtual Memory 16,0 GB
Server installation folder:
===========================
E:\Program Files\MonetDB\MonetDB5
Server startup script M5server.bat:
===================================
@REM Copyright August 2008-2014 MonetDB B.V.
@REM All Rights Reserved.
@echo off
setlocal
rem figure out the folder name
set MONETDB=%~dp0
rem remove the final backslash from the path
set MONETDB=%MONETDB:~0,-1%
rem extend the search path with our EXE and DLL folders
set PATH=%MONETDB%\bin;%MONETDB%\lib;%MONETDB%\lib\MonetDB5;%PATH%
rem prepare the arguments to mserver5 to tell it where to put the dbfarm
REM Uniserv: Don't use APPDATA, so set APPDATA to empty String.
set APPDATA=
if "%APPDATA%" == "" goto usevar
rem if the APPDATA variable does exist, put the database there
set MONETDBDIR=%APPDATA%\MonetDB5
set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\demo"
goto skipusevar
:usevar
rem if the APPDATA variable does not exist, put the database in the
rem installation folder (i.e. default location, so no command line argument)
REM Uniserv: Specify the DB installation folder
rem set MONETDBDIR=%MONETDB%\var\MonetDB5
set MONETDBDIR=%MONETDB%\db
set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\uniserv"
:skipusevar
rem the SQL log directory used to be in %MONETDBDIR%, but we now
rem prefer it inside the dbfarm, so move it there
if not exist "%MONETDBDIR%\sql_logs" goto skipmove
for /d %%i in ("%MONETDBDIR%"\sql_logs*) do move "%%i" "%MONETDBDIR%\dbfarm"%%~ni\sql_logs
rmdir "%MONETDBDIR%\sql_logs"
:skipmove
rem start the real server
"%MONETDB%\bin\mserver5.exe" --set "prefix=%MONETDB%" --set mapi_open=true --set mapi_port=50000 --set max_clients=1000 --set "exec_prefix=%MONETDB%" %MONETDBFARM% %*
if ERRORLEVEL 1 pause
endlocal
MonetDB JDBC Client:
================
We query the database via the latest MonetDB JDBC driver loaded from the central Maven repository:
<groupId>monetdb</groupId> <artifactId>monetdb-jdbc</artifactId> <version>2.14</version>Database tables:
================
We create tables called "rule_result_<n>"
CREATE TABLE "dq_scorecard"."rule_results_1" ("id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, "record_id" VARCHAR(1024), "rule_1" INT, "rule_2" INT, ...);
Each table has about 50+ INT columns.
Query scenarios:
================
We have concurrent read access via JDBC driver and Spring Data Access Objects,
the query look like that:
*Client 1: 50 aggregate queries on 50 different columns on one database table like that:
SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_1" WHERE "rule_n" >= 0
*Client 2: 50 aggregate queries on 50 different columns on another database table like that:
SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_2" WHERE "rule_n" >= 0
*Client 3: 50 aggregate queries on 50 different columns on another database table like that:
SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_3" WHERE "rule_n" >= 0
All clients run concurrently, at the moment we don't use a connection pool and create a connection for every query.
During concurrent access the M5server crashes.
The server crashes when each table has 100 records! Our production tables can have up to 50. 000. 000 of records!
Kind regards,
Michael
From: users-list [mailto:users-list-bounces+michael.witt mailto:users-list-bounces%2Bmichael.witt =uniserv.com@monetdb.org mailto:uniserv.com@monetdb.org ] On Behalf Of michael.witt@uniserv.com mailto:michael.witt@uniserv.com Sent: Donnerstag, 28. Mai 2015 14:32 To: users-list@monetdb.org mailto:users-list@monetdb.org Subject: Concurrency test causes crash of MonetDB 5 server
Hi,
we experience a crash of MonetDB 5 server during tests of a multi-threaded client access via JDBC-Driver and MapiSocket:
The application is already optimized to avoid “concurrency conflicts” because of the “Optimistic Concurrency Control” concept.
The test simulates client access via two threads.
All update operations (INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE) are done in serial.
All read operations are done in parallel to other read operations or to update operations.
The test causes a crash of mserver5.exe after a few minutes.
If the test uses just one thread, all read and update operations are done in serial and the server does not crash.
What is the rule of thumb to avoid crashes when using several client threads?
Crash details:
Problem signature:
Problem Event Name: APPCRASH Application Name: mserver5.exe Application Version: 0.0.0.0 Application Timestamp: 55389f58 Fault Module Name: StackHash_8ec6 Fault Module Version: 6.1.7601.18247 Fault Module Timestamp: 521eaf24 Exception Code: c0000374 Exception Offset: 00000000000c4102 OS Version: 6.1.7601.2.1.0.144.8 Locale ID: 1031 Additional Information 1: 8ec6 Additional Information 2: 8ec62499039d1d8234b025a62c725eb7 Additional Information 3: f8f3 Additional Information 4: f8f3e520847c0684b15d4080171779ef# MonetDB 5 server v11.19.9 "Oct2014-SP2"
# Serving database 'uniserv', using 4 threads
# Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
# Found 10.000 GiB available main-memory.
..
# MonetDB/SQL module loaded
Michael Witt
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
michael.witt＠uniserv.com -
Ying Zhang