I currently have some ~4.5M posts, ~50K users and ~1000-1500 concurrent sessions depending on time of the day and have been experiencing significant performance problems with using MySQL on my forums.

Most if not all of the performance problems were caused by long queries that would lock table updates that in turn will lock all the subsequent selects. This is a known problem with table level locking in mysql. I have moved search to Sphinx, but this only addressed some of the problems.

One of the recommended ways on forums was to use InnoDB to avoid table locks, but it has got its own issues and also, you cannot do a hotbackup with free open source tools (you can backup from replication, but this is not the best choice in all scenarios). Another proposed solution was to redirect all long queries to replication slave, but this looked ugly to me.

So, I have decided to use beta-posgresql drivers from IPB 2.x to try to migrate to PostgreSQL. I have submitted a number of driver patches to IPS, made a number of modifications to the code, added few new indexes to tables, managed to complete a database conversion without losing any data, but after all I am now a happy user running IPB 2.x on PostgreSQL 8.3.

So far, so good. No more locks, no more long running queries. I still have some glitches to fix, mainly due to IPB code relying on some Mysql gotchas like implicit conversion of chars to integer etc., but by and large this was a successful upgrade, and this topic is to share the success and may be convince IPS to keep their work on PostgreSQL drivers :)

I was also wondering if anybody else has managed to migrate to PostgreSQL as well?

I've been hearing a lot of good things about PostGreSQL myself, but I hear a lot of conflicting information, particularly over which RDBMS is the best solution, since both PostGreSQL and MySQL are open-source and freely available, unlike MS-SQL and Oracle.

One person told me, MySQL is closer to Standard-Compliant SQL, that PostGreSQL has a lot more "isms" that break's compatibility.
Another person has told me, that PostGreSQL is closer to the standard, and scales better.
Another, the project manager for a very popular bittorrent client, finds that MySQL scales badly and isnt multithreaded.
I have noticed that on any site that gains a reasonably large amount of traffic, MySQL buckles under the load on a single server, though that could easily be a result of running Apache with prefork, which is rather inefficient, especially on servers that have multiple CPU's.

Realistically in my experience, the open source database engines just aren't up to the same level as your paid solutions. Oracle, while enterprise level, is intended more-so for a single always-open connection and really really dislikes many connections being opened and closed frequently. Apparently there is a lot more overhead in doing this with Oracle, and it prefers something akin to persistent connections in MySQL (though I'm not sure if that's possible or not via PHP). MSSQL scales very well and handles searching very well even on larger sites, but of course there's a bit of a cost going with a proper MSSQL setup.

There's gonna be trade-offs no matter which way you go.

I currently have some ~4.5M posts, ~50K users and ~1000-1500 concurrent sessions depending on time of the day and have been experiencing significant performance problems with using MySQL on my forums.

Most if not all of the performance problems were caused by long queries that would lock table updates that in turn will lock all the subsequent selects. This is a known problem with table level locking in mysql. I have moved search to Sphinx, but this only addressed some of the problems.

One of the recommended ways on forums was to use InnoDB to avoid table locks, but it has got its own issues and also, you cannot do a hotbackup with free open source tools (you can backup from replication, but this is not the best choice in all scenarios). Another proposed solution was to redirect all long queries to replication slave, but this looked ugly to me.

So, I have decided to use beta-posgresql drivers from IPB 2.x to try to migrate to PostgreSQL. I have submitted a number of driver patches to IPS, made a number of modifications to the code, added few new indexes to tables, managed to complete a database conversion without losing any data, but after all I am now a happy user running IPB 2.x on PostgreSQL 8.3.

So far, so good. No more locks, no more long running queries. I still have some glitches to fix, mainly due to IPB code relying on some Mysql gotchas like implicit conversion of chars to integer etc., but by and large this was a successful upgrade, and this topic is to share the success and may be convince IPS to keep their work on PostgreSQL drivers :)

I was also wondering if anybody else has managed to migrate to PostgreSQL as well?

No we have not migrated to PostgreSQL although my system administrator is advising me to do so, as we experience the same problems as you did. Unfortunately, IPB stopped supporting PostgreSQL.

Did you - basically - add a PostgreSQL library to the database libraries of IPB?

Do you intend to share this PostgreSQL library, whether or not for free?

And will you update the library - to the extent necessary - for IPB 3.0?

Lots of questions so far... :)

I was hoping to see some interest here, but I can now see why IPS stopped supporting PostgreSQL - almost no demand.

I had to make a number of tweaks to the code to make it efficient and fix mysql-specific queries and remove reliance on mysql gotchas. However, most of the functionality is covered by the driver, which was provided to me by IPS in beta-form and which I also had to update to make it work, since it was quite an early beta.

I am willing to share the drivers, but this is not the only change needed which makes it a bit more complicated. I hope IPB3.0 code will be cleaner and it would be possible to only change the driver, so, yes, once source code for 3.0 is available, I will start working on PostgreSQL drivers.

That's the thing. While we've always thought it would be nice to have PostgreSQL driver, and with the abstraction we have it's entirely doable, there hasn't seemed to be any demand. It takes development time and support time to create/support such a thing, so if there isn't enough demand it's not really feasible.

Like you, I was hoping to see more people replying to this topic. ;)

Could the PostgreSQL driver perhaps be made into a community project for IP.Board 3? I'd gladly spend some time working on it with Nime, I have an interest in seeing an actively maintained version of it.

Hmmmmmmm.

bfarber, do you think it may be a chicken and egg problem? :-) may be once you have drivers available publicly demand for it will appear... may be I am wrong though.

Anyway, I think it would be useful to you if the code outside of driver is mysql-independent (which it is not 100% now). Having drivers that are maintained even by community would significantly help achieving that. With the way you have abstracted your code it is not such a big effort anyway.

Hmmmmmmm.

:o

I would consider switching to PostgreSQL if support for IPB matured and there were obvious performance benefits in doing so. :)

..Al

I would consider switching to PostgreSQL if support for IPB matured and there were obvious performance benefits in doing so. :)

..Al

So would I. I actually have thought about it and I think what may be holding me back is the lack of support by modification authors. I think it could very well be a chicken and egg problem...

So would I. I actually have thought about it and I think what may be holding me back is the lack of support by modification authors. I think it could very well be a chicken and egg problem...

If there is a database driver, and mod authors would stick to using the driver methods, then it shouldn't be an issue. I think nearly all are avoiding query() now, aren't they?

If there is a database driver, and mod authors would stick to using the driver methods, then it shouldn't be an issue. I think nearly all are avoiding query() now, aren't they?

I think so. I just remember that on IPB 2.x, the Universal Mod Installer was only supported for use on MySQL. Granted, the Universal Mod Install is now built in so that issue might be moot now.

Could the PostgreSQL driver perhaps be made into a community project for IP.Board 3? I'd gladly spend some time working on it with Nime, I have an interest in seeing an actively maintained version of it.

++

It was actually planned to be so, but after I have decided not to upgrade to 3.x in the nearest future (waaaay to much work), I wasn't able to commit my time on that... not sure about others who expressed initial intereset. I hope this will be made available at some stage, otherwise I will have to do it one day myself again ))

Funny coincidence. 10 days after my last post we have made a decision to upgrade to 3.x :)

Expect some news on PostgreSQL driver in the next few weeks. Anybody still wants to help?

OK, thanks to a good abstraction layer in 3.x I have managed to set my board up and running on alpha-version of the driver. All the basic functionality, like posting, PMing, reading, subscribing etc. works fine now. More work needed now on the custom stuff like reports.

I will send an invite to join, to those who expressed interest on this topic, but if there is anybody else who wants to contribute, please let me know here or via PM.

Driver for PostgreSQL is a very good idea. I talked earlier of the IPB, and claimed that they are in the process of developing driver - unfortunately, a zero specific information about the official support for PostgreSQL.

IPB: PostgreSQL support please!

Nime: when you have written a driver for PostgreSQL? I'm happy to buy it :)

I have actually migrated my board to postgresql 2 days ago. While I still have a number of issues, most of them are not directly related to the driver, and by and large migration was successful. I still have a handful of open bug reports with IPB though )

I will be setting up a plain new board on my new website some time this week, that will be running postgresql, which wouldn't have any migration issues and could serve as a first running example of a fresh postgresql installation.

Honestly, I did not have any plans to sell it (not sure if I am allowed to at all), but rather release this for community :) may be i should have :D

Which version of PostgreSQL do you have? Are search engine is based on the full-text TSearch2?

Yes, I see your tickets in the bug-tracker - most of them have the status of fixed in version 3.1 beta 1.
Maybe there will be official driver for PostgreSQL? :)

I use 8.3. Full-text is not based on tsearch2, since I use sphinx. I have done some work originally on using tsearch2 and it is not hard to add, but ended up using sphinx since it is much better optimized for quick searching than any other solutions.

We were hoping to do a public beta during next week or so. Not sure if IPS has plans to make driver official.

This is good news for me:)
I have several websites that operate based on PostgreSQL and I did not see the possibility of making the MySQL server specifically for IPB.

Sphinx is a good search engine, but is available only for *nix platform, which narrows its usefulness.

http://www.sphinxsearch.com/docs/current.html#installing-windows