Postgresql Queries not returning from server when running on a VM and DB server has high CPU load

Postgresql Queries not returning from server when running on a VM and DB server has high CPU load

Postby alfrye » Fri 12 Apr 2013 16:53

When running my application inside a VM and connecting a database server with high CPU my application hangs because the query does not timeout properly. I have my datacontexts command timeout set to 60 seconds. The same applicatuion works properly when running on my desktop. Both the desktop and the VM are running Windows 7 64-bit.

I am running the following versions of devart libraries.

Devart.data.dll 5.0.581.0
Devart.data.Linq.dll 4.1.127
Devart.Data.PostgreSql.dll 6.2.122.0
Devart.Data.PostgreSql.Linq.dll 4.1.127.0

When this behavior happens I am getting the following errors in the database server logs.

2013-04-12 12:40:31.353 SDT 3530 Jarom 23008: LOG: could not send data to client: Broken pipe
2013-04-12 12:40:31.354 SDT 3530 Jarom 23008: STATEMENT: SELECT t1.connection_id, t1.tag_id, t1.enable, t1.point_type_id, t1.deadband, t1.zero_deadband, t1.real_range_min, t1.real_range_max, t1.real_range_l
FROM list_gvl_tags_for_hmi() t1
2013-04-12 12:40:31.366 SDT 3530 Jarom 23008: LOG: duration: 273578.100 ms
2013-04-12 12:40:31.366 SDT 3530 Jarom 23008: LOG: SSL error: bad write retry
2013-04-12 12:40:31.367 SDT 3530 Jarom 23008: LOG: could not send data to client: Connection reset by peer
2013-04-12 12:40:31.549 SDT 3530 Jarom 23008: LOG: SSL error: bad write retry

However if switch out my drivers and use Npgsql driver the queries do time out properly and the postgresql log has the following error.

2013-04-12 11:30:39.671 SDT 3530 Jarom 16755: ERROR: canceling statement due to user request
2013-04-12 11:30:39.672 SDT 3530 Jarom 16755: CONTEXT: SQL statement "SELECT schema_12.raw_tag_name(t.connection_id, r.name, p.protocol_name, rc.instance, t.partial_raw_tag) FROM schema_12.tags t LEFT JOIN
PL/pgSQL function "raw_tag_name" line 2 at SQL statement
PL/pgSQL function "list_gvl_tags_for_hmi" line 7 at FOR over SELECT rows
2013-04-12 11:30:39.672 SDT 3530 Jarom 16755: STATEMENT: select * from schema_12.list_gvl_tags_for_hmi()


Any suggestion would be great. I really want to be able to use linq queries of this.
alfrye
 
Posts: 16
Joined: Wed 05 May 2010 19:39

Re: Postgresql Queries not returning from server when running on a VM and DB server has high CPU load

Postby MariiaI » Wed 17 Apr 2013 10:45

Sorry for the delay. Could you please specify the following:
1) which connection authentication method is used to connect with VM (it is specified in the configuration file of the server connections - pg_hba.conf);
2) how do you define the command timeout.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Postgresql Queries not returning from server when running on a VM and DB server has high CPU load

Postby alfrye » Wed 17 Apr 2013 13:32

Just to clarify the Postgresql database is not running on a VM. The database server is running on Ubuntu server and the my client application is running inside a Windows 7 virtual machine. I am connected to database with sslmode = require. I am setting the command timeout via the Datacontext.
Code: Select all
context.CommandTimeout = 60;
I have also tried to set the Command Timeout via the connection string.
Code: Select all
  string connectionString =
                string.Format(
                    "User Id={0};Password={1};Host={2};Database={3};Port={4};Persist Security Info=True;Schema={5};unicode=true;SSLMode={6};Default Commmand Timeout{7}",
                    userName,
                    password,
                    hostNameIP,
                    dbName,
                    tcpPort,
                    schema,
                    sslMode,
                    "60");
alfrye
 
Posts: 16
Joined: Wed 05 May 2010 19:39

Re: Postgresql Queries not returning from server when running on a VM and DB server has high CPU load

Postby MariiaI » Thu 18 Apr 2013 11:55

Thank you for the additional information. We are sending you a simple test project to the e-mail address you provided in your forum profile. Please check that the letter is not blocked by your mail filter. Please make changes to it so that the issue could be reproduced and send it back to us, or send us your project.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Postgresql Queries not returning from server when running on a VM and DB server has high CPU load

Postby alfrye » Thu 18 Apr 2013 13:38

I did not receive the email you sent out. I have also check our mail filter and nothing from devart has been blocked.
alfrye
 
Posts: 16
Joined: Wed 05 May 2010 19:39

Re: Postgresql Queries not returning from server when running on a VM and DB server has high CPU load

Postby MariiaI » Fri 19 Apr 2013 07:18

We have sent you a sample project again, please check that the letter is not blocked by your mail filter.
Please confirm whether you received it or not.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)