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).
我们有一个使用MySql运行的applicationg。我们发现MySql不适合我们的应用程序，因为我们发现它不支持PostGIS所具有的一些GIS功能（注意：mysql只支持最小边界矩形GIS搜索）。
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.
所以我们将数据库更改为PostgreSQL。然后我们发现在Windows上运行的Postgresql 8.2与Mysql 5.1相比要慢得多。慢一点，我的意思是慢大约4-5倍。
Why is this? Is there something in the configuration that we need to change?
为什么是这样？我们需要更改配置中的某些内容吗？
I found some comments from other websites such as this:
我从其他网站上找到了一些评论，例如：
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?
更新：我们发现缓慢的原因是由于我们插入数据库的BLOB。我们需要能够以10-15 MB / s的持续速率插入BLOB。我们正在为我们插入/读取的每个BLOB使用libpq的lo_read和lo_write。这是最好的方式吗？以前有没有人用Pgsql以高速插入大BLOB？
EDIT: I heard that PgSql just recently got ported to Windows. Could this be one of the reasons?
编辑：我听说PgSql刚刚被移植到Windows。这可能是其中一个原因吗？
3 个解决方案
#1
21
There are cases where PostgreSQL on Windows pays an additional overhead compared to other solutions, due to tradeoffs made when we ported it.
有些情况下，与其他解决方案相比，Windows上的PostgreSQL会产生额外的开销，这是由于我们移植它时所做的权衡。
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.
例如，PostgreSQL每个连接使用一个进程，MySQL使用一个线程。在Unix上，这通常不是明显的性能差异，但在Windows上创建新进程非常昂贵（由于缺少fork（）系统调用）。因此，在使用PostgreSQL时，使用持久连接或连接池更为重要。
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.
我看到的另一个问题是Windows上的早期PostgreSQL默认情况下会确保它的写入通过写入缓存 - 即使它是电池备份的。 AFAIK，MySQL不会这样做，它会极大地影响写入性能。现在，如果你有一个不安全的硬件，比如便宜的驱动器，这实际上是必需的。但是，如果您有电池支持的写缓存，则需要将其更改为常规fsync。 PostgreSQL的现代版本（肯定是8.3）将默认为open_datasync，这应该消除这种差异。
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.
您还没有提及如何调整数据库的配置。默认情况下，PostgreSQL附带的配置文件非常保守。如果你没有改变任何东西，你肯定需要看看它。 PostgreSQL wiki上提供了一些调优建议。
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.
要提供更多详细信息，您必须提供有关运行缓慢的详细信息以及如何调整数据库的更多详细信息。我建议给pgsql-general邮件列表发一封电子邮件。
#2
7
While the Windows port of PostgreSQL is relatively recent, my understanding is that it performs about as well as the other versions. But it's definitely a port; almost all developers work primarily or exclusively on Unix/Linux/BSD.
虽然PostgreSQL的Windows端口是相对较新的，但我的理解是它的性能与其他版本一样好。但它绝对是一个港口;几乎所有开发人员主要或完全在Unix / Linux / BSD上工作。
You really shouldn't be running 8.2 on Windows. In my opinion, 8.3 was the first Windows release that was truly production-ready; 8.4 is better yet. 8.2 is rather out of date anyway, and you'll reap several benefits if you can manage to upgrade.
你真的不应该在Windows上运行8.2。在我看来，8.3是第一个真正适合生产的Windows版本; 8.4还好。 8.2无论如何都已经过时了，如果你能设法升级，你将获得好处。
Another thing to consider is tuning. PostgreSQL requires more tuning than MySQL to achieve optimal performance. You may want to consider posting to one of the mailing lists for help with more than basic tweaking.
另一件需要考虑的事情是调整。 PostgreSQL需要比MySQL更多的调优才能获得最佳性能。您可能需要考虑发布到其中一个邮件列表以获取更多基本调整的帮助。
#3
0
PostgreSQL is already slower than MySQL up to a certain point (it is actually faster when you have a ridiculously large database). Just FYI, this isn't causing your problem but keep that in mind.
PostgreSQL已经比MySQL慢了一定程度（当你有一个可笑的大型数据库时它实际上更快）。仅供参考，这不会导致您的问题，但请记住这一点。
#1
21
There are cases where PostgreSQL on Windows pays an additional overhead compared to other solutions, due to tradeoffs made when we ported it.
有些情况下，与其他解决方案相比，Windows上的PostgreSQL会产生额外的开销，这是由于我们移植它时所做的权衡。
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.
例如，PostgreSQL每个连接使用一个进程，MySQL使用一个线程。在Unix上，这通常不是明显的性能差异，但在Windows上创建新进程非常昂贵（由于缺少fork（）系统调用）。因此，在使用PostgreSQL时，使用持久连接或连接池更为重要。
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.
我看到的另一个问题是Windows上的早期PostgreSQL默认情况下会确保它的写入通过写入缓存 - 即使它是电池备份的。 AFAIK，MySQL不会这样做，它会极大地影响写入性能。现在，如果你有一个不安全的硬件，比如便宜的驱动器，这实际上是必需的。但是，如果您有电池支持的写缓存，则需要将其更改为常规fsync。 PostgreSQL的现代版本（肯定是8.3）将默认为open_datasync，这应该消除这种差异。
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.
您还没有提及如何调整数据库的配置。默认情况下，PostgreSQL附带的配置文件非常保守。如果你没有改变任何东西，你肯定需要看看它。 PostgreSQL wiki上提供了一些调优建议。
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.
要提供更多详细信息，您必须提供有关运行缓慢的详细信息以及如何调整数据库的更多详细信息。我建议给pgsql-general邮件列表发一封电子邮件。
#2
7
While the Windows port of PostgreSQL is relatively recent, my understanding is that it performs about as well as the other versions. But it's definitely a port; almost all developers work primarily or exclusively on Unix/Linux/BSD.
虽然PostgreSQL的Windows端口是相对较新的，但我的理解是它的性能与其他版本一样好。但它绝对是一个港口;几乎所有开发人员主要或完全在Unix / Linux / BSD上工作。
You really shouldn't be running 8.2 on Windows. In my opinion, 8.3 was the first Windows release that was truly production-ready; 8.4 is better yet. 8.2 is rather out of date anyway, and you'll reap several benefits if you can manage to upgrade.
你真的不应该在Windows上运行8.2。在我看来，8.3是第一个真正适合生产的Windows版本; 8.4还好。 8.2无论如何都已经过时了，如果你能设法升级，你将获得好处。
Another thing to consider is tuning. PostgreSQL requires more tuning than MySQL to achieve optimal performance. You may want to consider posting to one of the mailing lists for help with more than basic tweaking.
另一件需要考虑的事情是调整。 PostgreSQL需要比MySQL更多的调优才能获得最佳性能。您可能需要考虑发布到其中一个邮件列表以获取更多基本调整的帮助。
#3
0
PostgreSQL is already slower than MySQL up to a certain point (it is actually faster when you have a ridiculously large database). Just FYI, this isn't causing your problem but keep that in mind.
PostgreSQL已经比MySQL慢了一定程度（当你有一个可笑的大型数据库时它实际上更快）。仅供参考，这不会导致您的问题，但请记住这一点。