为什么PostgreSQL在Windows上这么慢? [英] Why is PostgreSQL so slow on Windows?

问题描述

我们有一个使用MySql运行的应用程序.在发现MySql不支持PostGIS具有的某些GIS功能后，我们发现MySql不适合我们的应用程序(请注意:mysql仅支持最小边界矩形GIS搜索).

We had an applicationg running using MySql. We found MySql was not suitable for our app after we found that it didnt support some of the GIS capability that PostGIS has (note: mysql only supports minimum-bounding rectangle GIS search).

因此，我们将数据库更改为PostgreSQL.然后我们发现与Mysql 5.1相比，Windows上运行的Postgresql 8.2慢得多.慢一点，我的意思是慢大约4-5倍.

So we changed our DB to PostgreSQL. We then found out that Postgresql 8.2 running on Windows is so much slower compared to Mysql 5.1. By slower, I mean at roughly 4-5 times slower.

这是为什么?配置中是否需要更改?

Why is this? Is there something in the configuration that we need to change?

我发现了来自其他网站的一些评论，例如此 :

I found some comments from other websites such as this:

更新:我们发现运行缓慢的原因是由于要插入数据库的BLOB引起的.我们需要能够以10-15 MB/s的持续速率插入BLOB.我们为每个要插入/读取的BLOB使用libpq的lo_read和lo_write.那是最好的方法吗?以前有人使用Pgsql来以高速率插入大型BLOB吗?

UPDATE: We found that the cause of the slowness is due to the BLOB that we are inserting into the DB. We need to be able to insert BLOB at a sustained rate of 10-15 MB/s. We are using libpq's lo_read and lo_write for each BLOB we are inserting/reading. Is that the best way? Has anyone used Pgsql for inserting large BLOB at a high rate before?

听说最近才将PgSql移植到Windows.这可能是原因之一吗?

I heard that PgSql just recently got ported to Windows. Could this be one of the reasons?

推荐答案

在某些情况下，由于在移植时需要权衡取舍，因此Windows上的PostgreSQL与其他解决方案相比要付出额外的开销.

There are cases where PostgreSQL on Windows pays an additional overhead compared to other solutions, due to tradeoffs made when we ported it.

例如，PostgreSQL使用每个连接一个进程，MySQL使用一个线程.在Unix上，这通常没有明显的性能差异，但是在Windows上创建新进程非常昂贵(由于缺少fork()系统调用).因此，在Windows上使用PostgreSQL时，使用持久性连接或连接缓冲池在Windows中更为重要.

For example, PostgreSQL uses a process per connection, MySQL uses a thread. On Unix, this is usually not a noticeable performance difference, but on Windows creating new processes is very expensive (due to the lack of the fork() system call). For this reason, using persistent connections or a connection pooler is much more important on Windows when using PostgreSQL.

我看到的另一个问题是，Windows上的早期PostgreSQL默认情况下会确保其写操作正在通过写缓存-即使它由电池供电也是如此. AFAIK，MySQL不会这样做，它将极大地影响写入性能.现在，如果您拥有不安全的硬件(例如便宜的驱动器)，则实际上需要这样做.但是，如果您有电池供电的写缓存，则希望将其更改为常规fsync. PostgreSQL的现代版本(一定是8.3)将默认改为使用open_datasync，它将消除这种差异.

Another issue I've seen is that early PostgreSQL on Windows will by default make sure that it's writes are going through the write cache - even if it's battery backed. AFAIK, MySQL does not do this, and it will greatly affect write performance. Now, this is actually required if you have a non-safe hardware, such as a cheap drive. But if you have a battery-backed write cache, you want to change this to regular fsync. Modern versions of PostgreSQL (certainly 8.3) will default to open_datasync instead, which should remove this difference.

您也没有提到有关如何调整数据库配置的信息.默认情况下，PostgreSQL附带的配置文件非常保守.如果您在那里没有进行任何更改，那么您肯定需要对其进行查看. PostgreSQL Wiki 中有一些调整建议.

You also mention nothing about how you have tuned the configuration of the database. By default, the configuration file shipped with PostgreSQL is very conservative. If you haven't changed anything there, you definitely need to take a look at it. There is some tuning advice available on the PostgreSQL wiki.

要提供更多详细信息，您将必须提供有关运行速度慢以及如何调整数据库的更多详细信息.我建议给pgsql-general邮件列表发送电子邮件.

To give any more details, you will have to provide a lot more details about exactly what runs slow, and how you have tuned your database. I'd suggest an email to the pgsql-general mailinglist.

这篇关于为什么PostgreSQL在Windows上这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！