C# - postgresql - memory leak over time

2019-08-23 10:21发布

问题:

Problem: The memory leaks (accumulates) over time, and reaches 99% capacity eventually.

I have the following C# code that constantly pushes data into PostgreSQL DB using while loop. I'm really struggling because I'm not a C# programmer. My main language is Python. I've been trying to look up C# references to solve my issue, but failed cuz I simply don't understand lots of syntax. The C# code is written by someone else in my company, but he's not available now.

Here is the code:

var connString = "Host=x.x.x.x;Port=5432;Username=postgres;Password=password;Database=database";

using (var conn = new Npgsql.NpgsqlConnection(connString)){

    conn.Open();

    int ctr = 0;

    // Insert some data
    while(@tag.TerminateTimeScaleLoop == 100)
    {
        @Info.Trace("Pushed Data: PostGre A " + ctr.ToString());
        using (var cmd = new Npgsql.NpgsqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "INSERT INTO TORQX VALUES (@r,@p)";
            cmd.Parameters.AddWithValue("r", System.DateTime.Now.ToUniversalTime());
            cmd.Parameters.AddWithValue("p", @Tag.RigData.Time.TORQX);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();


            cmd.CommandText = "INSERT INTO BLKPOS VALUES (@s,@t)";
            cmd.Parameters.AddWithValue("s", System.DateTime.Now.ToUniversalTime());
            cmd.Parameters.AddWithValue("t", @Tag.RigData.Time.BLKPOS);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();



           // @Info.Trace("Pushed Data: PostGre " + ctr.ToString());
        }

        ctr = ctr + 1;
    }
    @Info.Trace("Pushed Data: PostGre A Terminated");

The code successfully established a connection in the beginning, and uses only one connection the entire time. It correctly inserts data to DB. But after memory capacity reaches 99%, its not inserting very well. The source of issue I can think of is that this code is constantly creating new object, but does not clear that object after one iteration is done. Can anyone tell me where the source of problem is & provide possible solution to this?

++ please understand that I'm not a C# programmer... I'm not too familiar with the concept of memory handling. But I will try my best to understand

回答1:

Here is something you can try. Notice the instantiation of the command and parameters happens outside of the loop, not on every iteration.

I am recycling the parameters. As a result I am using Add(), not AddWithValue() and you must fill in the database type for the second parameter and consider using precision and scale parameters too as appropriate. This will only work if the two commands use the same parameter types. You might consider creating two commands, one for each query.

Know that variable names beginning with @ makes me cringe as a c# developer....

var connString = "Host=x.x.x.x;Port=5432;Username=postgres;Password=password;Database=database";

using (var conn = new Npgsql.NpgsqlConnection(connString))
{
    conn.Open();

    int ctr = 0;

    @Info.Trace("Pushed Data: PostGre A " + ctr.ToString());

    using (var cmd = new Npgsql.NpgsqlCommand())
    {
        cmd.Connection = conn;

        var par_1 = cmd.Parameters.Add("@p1", /*< appropriate datatype here >*/);
        var par_2 = cmd.Parameters.Add("@p2", /*< appropriate datatype here >*/);

        while(@tag.TerminateTimeScaleLoop == 100)
        {
            cmd.CommandText = "INSERT INTO TORQX VALUES (@p1,@p2)";
            par_1.Value = System.DateTime.Now.ToUniversalTime());
            par_2.Value = @Tag.RigData.Time.TORQX;

            cmd.ExecuteNonQuery();

            cmd.CommandText = "INSERT INTO BLKPOS VALUES (@p1,@p2)";

            par_1.Value = System.DateTime.Now.ToUniversalTime());
            par_2.Value = @Tag.RigData.Time.BLKPOS;

            cmd.ExecuteNonQuery();

            ctr = ctr + 1;
        }
    }
}
@Info.Trace("Pushed Data: PostGre A Terminated");