首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >npgsql / PostgreSQL在局域网上的性能非常低

问npgsql / PostgreSQL在局域网上的性能非常低
EN

Stack Overflow用户
提问于 2019-05-06 14:56:29
回答 2查看 610关注 0票数 0

我正在用npgsql检查Postgresql准备好的语句。它在安装Postgresql服务器的同一台机器上运行完美。在服务器(同一台机器)上使用简单的100000个随机值INSERT语句

它在大约12.16秒的时间内插入100k条记录。8224条记录/秒(如果未准备)。它在大约7.76秒内插入100k条记录。12886条记录/秒if预准备语句

在客户端(同一LAN中的另一台机器,具有相同的IP范围和相同的配置,8 8Gb )

它在大约89.36秒内插入100k条记录。1119条记录/秒,如果没有准备的话。它在大约87.95秒内插入100k条记录。1137条记录/秒。if准备好的语句。

这种在LAN上的性能是非常不可接受的。请帮帮忙。

我已经使用https://www.pgconfig.org/#/tuning配置了我的postgresql服务器

还禁用了两台计算机上的防病毒+防火墙。

该表已未记录,并且没有任何约束或索引。

Postgresql版本9.6.12

代码语言:javascript
复制
        private void button1_Click(object sender, EventArgs e)
        {
            button1.Enabled=false; 
            button2.Enabled=false;
            button3.Enabled = false;
            lblUnprepared.Text = "Executing... Please Wait...!";
            lblUnprepared.Refresh();
            npgCmd.CommandText = "TRUNCATE TABLE TBL_ETCBLACKLIST_TEST";
            npgCmd.ExecuteNonQuery();
            procInsertData();
        }

        private void procInsertData()
        {
            string strFileDownloadId = string.Empty;
            string strValues_0 = string.Empty;
            string strValues_1 = string.Empty;
            string strValues_2 = string.Empty;
            string strValues_3 = string.Empty;
            string strBlacklistFileNameWithoutExtension = string.Empty;

            DateTime strDateTime = DateTime.Now;

            DateTime strstarttime= DateTime.Now;


            objLog.Log(0, "Start Executing SQL");
            npgCmd.CommandType = CommandType.Text;
            npgCmd.Connection = npgConn;


            for (int i = 0; i <= lRows; i++)
            {
                //npgCmd = new NpgsqlCommand();
                //NpgsqlDataReader npgRD = null;

                strFileDownloadId = i.ToString();
                strValues_0 = RandomString(10, true) +  i;
                strValues_1 = RandomString(1, true);
                strValues_2 = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss");
                strValues_3 = RandomString(10, true);
                strBlacklistFileNameWithoutExtension = RandomString(10, true);

                strSQL = "INSERT INTO TBL_ETCBLACKLIST_TEST(ETCFileDownload_ID, ETC_Tag_ID, Tag_Status, Effective_Date, "
                   + " Reason_Code, CCHFile_ID, Create_DateTime, Last_Update_Date, CCHBank_ID) VALUES("
                   + strFileDownloadId + ", '" + strValues_0 + "', '" + strValues_1 + "', TO_TIMESTAMP('" + strValues_2 + "','YYYY/MM/DD HH24:MI:SS') "
                   + ", '" + strValues_3 + "', '" + strBlacklistFileNameWithoutExtension + "','" + DateTime.Now +"','" + DateTime.Now + "', 1 )";

                npgCmd.CommandText = strSQL;
              npgCmd.ExecuteNonQuery();
            }
            DateTime strEndTime = DateTime.Now;
            var diffInSeconds = (strEndTime - strstarttime).TotalSeconds;
            var lRecPerSec = Math.Round(lRows / diffInSeconds);
            lblUnprepared.Text = string.Format("UnPrepared SQL Inserted {0} Records In {1} Seconds \n@{2} Records Per Second.", lRows, diffInSeconds, lRecPerSec);

            objLog.Log(0, "UnPrepeared SQL Processed in: " + diffInSeconds); 
            //npgCmd.Dispose();
            //MessageBox.Show(string.Format("UnPreapared Statement Processed in {0} Seconds.", diffInSeconds));
            button1.Enabled = true;
            button2.Enabled = true;
            button3.Enabled = true;

            //npgRD.Dispose();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string strFileDownloadId = string.Empty;
            string strValues_0 = string.Empty;
            string strValues_1 = string.Empty;
            string strValues_2 = string.Empty;
            string strValues_3 = string.Empty;
            string strBlacklistFileNameWithoutExtension = string.Empty;

            DateTime strDateTime = DateTime.Now;
            DateTime strstarttime = DateTime.Now;

            button1.Enabled = false;
            button2.Enabled = false;
            button3.Enabled = false;

    npgCmd.CommandText = "TRUNCATE TABLE TBL_ETCBLACKLIST_TEST";
            npgCmd.ExecuteNonQuery();

            objLog.Log(0, "Start Executing SQL");
            lblPrepared.Text = "Executing... Please Wait...!";
            lblPrepared.Refresh();

            strSQL = "INSERT INTO TBL_ETCBLACKLIST_TEST(ETCFileDownload_ID, ETC_Tag_ID, Tag_Status, Effective_Date, "
               + " Reason_Code, CCHFile_ID, Create_DateTime, Last_Update_Date, CCHBank_ID) VALUES("
               + "@Param1 , @Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9)";

            npgCmd.CommandText = strSQL;
            npgCmd.CommandType = CommandType.Text;
            npgCmd.Connection = npgConn;

            npgCmd.Parameters.Add("@Param1", NpgsqlDbType.Numeric);

            npgCmd.Parameters.Add("@Param2", NpgsqlDbType.Text);
            npgCmd.Parameters.Add("@Param3", NpgsqlDbType.Text);
            npgCmd.Parameters.Add("@Param4", NpgsqlDbType.Date);
            npgCmd.Parameters.Add("@Param5", NpgsqlDbType.Text);
            npgCmd.Parameters.Add("@Param6", NpgsqlDbType.Text);
            npgCmd.Parameters.Add("@Param7", NpgsqlDbType.Date);
            npgCmd.Parameters.Add("@Param8", NpgsqlDbType.Date);
            npgCmd.Parameters.Add("@Param9", NpgsqlDbType.Numeric);
            npgCmd.Prepare();

            for (int i = 0; i <= lRows; i++)
            {
//                npgCmd = new NpgsqlCommand();

                strFileDownloadId = i.ToString();
                strValues_0 = RandomString(10, true) + i;
                strValues_1 = RandomString(1, true);
                strValues_2 = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss");
                strValues_3 = RandomString(10, true);
                strBlacklistFileNameWithoutExtension = RandomString(10, true);
                npgCmd.Parameters[0].Value = strFileDownloadId;
                npgCmd.Parameters[1].Value = strValues_0;
                npgCmd.Parameters[2].Value = strValues_1;
                npgCmd.Parameters[3].Value = strValues_2;
                npgCmd.Parameters[4].Value = strValues_3;
                npgCmd.Parameters[5].Value = strBlacklistFileNameWithoutExtension;
                npgCmd.Parameters[6].Value = DateTime.Now;
                npgCmd.Parameters[7].Value = DateTime.Now;
                npgCmd.Parameters[8].Value = 1;

                npgCmd.ExecuteNonQuery();
            }
            DateTime strEndTime = DateTime.Now;

            var diffInSeconds = (strEndTime - strstarttime).TotalSeconds;
            var lRecPerSec = Math.Round(lRows / diffInSeconds);

            lblPrepared.Text = string.Format("Prepared SQL Inserted {0} Records In {1} Seconds \n@ {2} Records Per Second.", lRows, diffInSeconds, lRecPerSec);
            //MessageBox.Show(string.Format("Preapared SQL Processed in {0} Seconds.", diffInSeconds));
            objLog.Log(0, "Prepeared SQL Processed in: " + diffInSeconds);

            lblPrepared.Text = string.Format("Prepared SQL Inserted {0} Records In {1} Seconds \n@ {2} Records Per Second.", lRows, diffInSeconds, lRecPerSec);
            //MessageBox.Show(string.Format("Preapared SQL Processed in {0} Seconds.", diffInSeconds));
            objLog.Log(0, "Prepeared SQL Processed in: " + diffInSeconds);

            button1.Enabled = true;
            button2.Enabled = true;
            button3.Enabled = true;

            //Cant find npgsql.Unprepare();
            //npgCmd.Dispose();
            //npgRD.Dispose();
        }

postgresql.auto.conf如下所示。

代码语言:javascript
复制
shared_buffers = '512MB'
effective_cache_size = '6GB'
work_mem = '82MB'
maintenance_work_mem = '512MB'
min_wal_size = '512MB'
max_wal_size = '2GB'
checkpoint_completion_target = '0.7'
wal_buffers = '16MB'
listen_addresses = '*'
max_connections = '100'
random_page_cost = '4.0'
logging_collector = 'on'
log_checkpoints = 'on'
log_connections = 'on'
log_disconnections = 'on'
log_lock_waits = 'on'
log_temp_files = '0'
lc_messages = 'C'
log_min_duration_statement = '10s'
log_autovacuum_min_duration = '0'
log_destination = 'stderr'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
EN

回答 2

Stack Overflow用户

发布于 2019-05-06 23:15:28

正如@felice-pollano上面所写的,你可能想看看Npgsql's bulk copy support,它允许你在一次往返中插入大量数据。它往往比简单的插入要优化得多。

如果您对每个SQL执行一次往返,那么与本地主机相比,LAN上的性能明显下降是有意义的。在后一种情况下,几乎没有延迟,但在前一种情况下,您会花费大部分时间等待协议消息到达PostgreSQL并返回。

票数 2
EN

Stack Overflow用户

发布于 2019-05-06 18:11:16

如果它在LAN上速度变慢,可能是因为LAN问题。顺便说一句,海量插入通常需要使用某种“批量”或“批处理”策略来完成,每次与数据库对话可能比实际执行db代码花费更多的时间。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56000163

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档