Sorry my bad observation. The server does crash at points where the clients dumped empty results. Regards, Tapomay. ________________________________ From: Tapomay Dey To: "monetdb-users@lists.sourceforge.net" Sent: Thursday, July 5, 2012 2:40 PM Subject: Re: mserver crash under load -16G data, 10 queries each from 2 remote clients An update: Tested firing 50 of the same failing query from a single machine. mserver didn't crash. However some of the clients ended without dumping the results. Rest of the clients completed successfully taking time from 8 to 200 seconds. Why does it crash with 2 machines for just 20 queries? Regards, Tapomay. ________________________________ From: Tapomay Dey To: "monetdb-users@lists.sourceforge.net" Sent: Thursday, July 5, 2012 12:58 PM Subject: mserver crash under load -16G data, 10 queries each from 2 remote clients I have a database with Single table. 15 million rows. 16G of data. 58 columns.  MonetDB stores it consuming 32G of data. mserver Machine specs: Main memory: 4G Processor: Intel(R) Xeon(R) CPU           E5645  @ 2.40GHz. Single core. I am performing a test to see how much time it takes if I fire this query multiple times from different machines. Case 1: I fire an aggregation query 10 times from a remote machine with 5sec interval between each firing.  The result hs 17000 rows with around 4 MB of data. Memory usage of mserver process reahes a peak of 73% mclients finish within 43 to 48 seconds. Case 2: I do the same as above but now from 2 remote machines simultaneously. Memory usage of mserver process reahes a peak of 73% again. It keeps fluctuating between 70 and 73%. But after some time mserver restarts and the clients terminate with all the 10 clients on each of both machines saying: ERROR = !Connection terminated MAPI  = tapomay@54.251.11.181:3306 ACTION= read_line QUERY = select id1, id2, id3, id4, id5, count(*), sum(metric1),         avg(metric2), sum(metric3), sum(metric4), sum(metric5), sum(metric6),         sum(metric7), sum(metric8), sum(metric9), sum(metric9),         sum(metric10), sum(metric11), sum(metric12), sum(metric13)         from tablename group by id1, id2, id3, id4, id5; Each query terminates within 5 to 50 seconds with the above error. In both cases intial state of mserver consumes around 0.5% memory. Also tested the same under other competing columnar DBs. They complete the case 2 in around 350 or more seconds average. They complete case 1 in 200 seconds average. Following is a last few lines of tail of merovingian.log 2012-07-05 06:30:42 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45476 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:42 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:45 MSG merovingian[31153]: proxying client 2.amazonaws.com:46225 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:45 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:47 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45477 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:47 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:50 MSG merovingian[31153]: proxying client 2.amazonaws.com:46226 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:50 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:52 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45478 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:52 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:55 MSG merovingian[31153]: proxying client 2.amazonaws.com:46241 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:55 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:59 MSG merovingian[31153]: database 'tablename' (1048) was killed by signal SIGKILL Is there a config that would ask mserver to not kill itself and keep processing at the cost of query execution time. A few more tens of seconds won't harm much. Thanks and Regards, Tapomay.

i met this problem too, my server machine specs:xeon 2*4 2.12MHz, 32G memory, 4*1T raid0, windows 2008R2 64bit, the table is simple with less than 10 columns and about 0.7 billions rows, the query is simple too, just select some rows from the table with "where" condition; it take an average of 200s to return results; but when i ran the query parallelly with 2 clients, sometimes the server crashed. how many parallel query can monetdb served? -------------------------------------------------------------------------------- ------------------ 原始邮件 ------------------ 发件人: Tapomay Dey ; 发送时间: 2012-07-05 17:35:52 收件人: monetdb-users@lists.sourceforge.net; 抄送: (无); 主题: Re: [MonetDB-users] mserver crash under load -16G data,10 queries each from 2 remote clients Sorry my bad again :( As Fabian pointed out http://sourceforge.net/mailarchive/forum.php?thread_name=20120705085653.GQ24104%40cwi.nl&forum_name=monetdb-users its unfair to say server crashed. It got killed by the OS. Regards, Tapomay. -------------------------------------------------------------------------------- From: Tapomay Dey To: "monetdb-users@lists.sourceforge.net" Sent: Thursday, July 5, 2012 2:59 PM Subject: Re: mserver crash under load -16G data, 10 queries each from 2 remote clients Sorry my bad observation. The server does crash at points where the clients dumped empty results. Regards, Tapomay. -------------------------------------------------------------------------------- From: Tapomay Dey To: "monetdb-users@lists.sourceforge.net" Sent: Thursday, July 5, 2012 2:40 PM Subject: Re: mserver crash under load -16G data, 10 queries each from 2 remote clients An update: Tested firing 50 of the same failing query from a single machine. mserver didn't crash. However some of the clients ended without dumping the results. Rest of the clients completed successfully taking time from 8 to 200 seconds. Why does it crash with 2 machines for just 20 queries? Regards, Tapomay. -------------------------------------------------------------------------------- From: Tapomay Dey To: "monetdb-users@lists.sourceforge.net" Sent: Thursday, July 5, 2012 12:58 PM Subject: mserver crash under load -16G data, 10 queries each from 2 remote clients I have a database with Single table. 15 million rows. 16G of data. 58 columns. MonetDB stores it consuming 32G of data. mserver Machine specs: Main memory: 4G Processor: Intel(R) Xeon(R) CPU E5645 @ 2.40GHz. Single core. I am performing a test to see how much time it takes if I fire this query multiple times from different machines. Case 1: I fire an aggregation query 10 times from a remote machine with 5sec interval between each firing. The result hs 17000 rows with around 4 MB of data. Memory usage of mserver process reahes a peak of 73% mclients finish within 43 to 48 seconds. Case 2: I do the same as above but now from 2 remote machines simultaneously. Memory usage of mserver process reahes a peak of 73% again. It keeps fluctuating between 70 and 73%. But after some time mserver restarts and the clients terminate with all the 10 clients on each of both machines saying: ERROR = !Connection terminated MAPI = tapomay@54.251.11.181:3306 ACTION= read_line QUERY = select id1, id2, id3, id4, id5, count(*), sum(metric1), avg(metric2), sum(metric3), sum(metric4), sum(metric5), sum(metric6), sum(metric7), sum(metric8), sum(metric9), sum(metric9), sum(metric10), sum(metric11), sum(metric12), sum(metric13) from tablename group by id1, id2, id3, id4, id5; Each query terminates within 5 to 50 seconds with the above error. In both cases intial state of mserver consumes around 0.5% memory. Also tested the same under other competing columnar DBs. They complete the case 2 in around 350 or more seconds average. They complete case 1 in 200 seconds average. Following is a last few lines of tail of merovingian.log 2012-07-05 06:30:42 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45476 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:42 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:45 MSG merovingian[31153]: proxying client 2.amazonaws.com:46225 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:45 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:47 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45477 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:47 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:50 MSG merovingian[31153]: proxying client 2.amazonaws.com:46226 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:50 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:52 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45478 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:52 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:55 MSG merovingian[31153]: proxying client 2.amazonaws.com:46241 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:55 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:59 MSG merovingian[31153]: database 'tablename' (1048) was killed by signal SIGKILL Is there a config that would ask mserver to not kill itself and keep processing at the cost of query execution time. A few more tens of seconds won't harm much. Thanks and Regards, Tapomay.

On 06-07-2012 06:12:34 -0700, Tapomay Dey wrote:

Hehe, goofup on my side. Spoiler alert:Needed swap.

But I think monet could do something smart about it.

What would you suggest?

any pointers as to if and how do I optimise when the data bulges into swap.

buy more memory ;) Seriously, not much you can do when you want to process more data than your memory can hold. You could try to put your swap on fast disks (e.g. raid-0 of SSDs), but also your dbfarm data could benefit from that, as when your data doesn't fit in memory, you'll be reading (and possibly writing) from disk frequently. Fabian

On 10-07-2012 06:21:19 -0700, Tapomay Dey wrote:

Thats true.

But isn't there a way we could figure out that OS is not gonna give us more RAM before being killed. That way it could deny further queries instead of dying out resulting in failure of all running queries. Just thinking out loud.

Typically, the OS tells the application it's not going to let it have more memory by making malloc calls fail. However, the OS in this case just evicts the application and kills it. There is no prior notice to the application, or anything. So nothing to be done here.

I stretched it further with 4GB Swap along with the 4G RAM. Firing 10 queries from 5 different machines. It ate up all the Swap and the RAM usage was at 95 % for a long time before being killed. Going ahead to set an 8G swap.

It feels to me like you're just overloading your relatively small machine tremendously. The more you get into swap, the more your performance is going down. You might be better off reducing the number of concurrent queries here instead. You could try and see if your application is benefitting from/working with the funnel included in monetdbd. Fabian