Jump to content


Photo
- - - - -

Migrated IPB to PostgreSQL and happy


  • Please log in to reply
42 replies to this topic

#1 Nime

Nime

    IPB Member

  • +Clients
  • 53 posts

Posted 09 February 2009 - 01:50 PM

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?

#2 OpenWare

OpenWare

    Advanced Member

  • +Clients
  • 231 posts

Posted 14 February 2009 - 05:06 PM

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.

#3 bfarber

bfarber

    RBT-KS

  • IPS Management
  • 28,608 posts

Posted 16 February 2009 - 09:45 AM

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.

Brandon Farber
Development Manager / Senior Support

If it sounds like fun, it's not allowed on the bus!

php5_zce_logo_new.gif     

Invision Power Services, Inc.


#4 Donkerrood

Donkerrood

    Advanced Member

  • Members
  • PipPipPipPip
  • 362 posts

Posted 24 February 2009 - 06:22 PM

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... :)

#5 Nime

Nime

    IPB Member

  • +Clients
  • 53 posts

Posted 25 February 2009 - 01:10 AM

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.

#6 bfarber

bfarber

    RBT-KS

  • IPS Management
  • 28,608 posts

Posted 25 February 2009 - 10:08 AM

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. ;)
  • ZackL likes this

Brandon Farber
Development Manager / Senior Support

If it sounds like fun, it's not allowed on the bus!

php5_zce_logo_new.gif     

Invision Power Services, Inc.


#7 Dan

Dan

    the same

  • +Clients
  • 4,293 posts

Posted 25 February 2009 - 10:33 AM

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.

#8 Matt

Matt

    Chief Software Architect

  • IPS Management
  • 26,142 posts

Posted 25 February 2009 - 12:13 PM

Hmmmmmmm.

Matt Mecham
Invision Power Services, Inc.
"I love deadlines. I especially like the whooshing sound they make as they go flying by."
-- Douglas Adams (1952 - 2001)


#9 Nime

Nime

    IPB Member

  • +Clients
  • 53 posts

Posted 25 February 2009 - 02:02 PM

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.

#10 W1lz0r

W1lz0r

    That SQL guy

  • +Clients
  • 5,411 posts

Posted 25 February 2009 - 02:57 PM

Hmmmmmmm.

:o
IPS DB Drivers / Community Blog Support and Development

[ Invision Community Blog ] [ Vuboys ]

#11 AtariAge

AtariAge

    Needs Serious Help

  • +Clients
  • 1,263 posts

Posted 25 February 2009 - 03:51 PM

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

..Al

#12 CynicalFrost

CynicalFrost

    IPB Full Member

  • Members
  • PipPipPip
  • 133 posts

Posted 25 February 2009 - 04:12 PM

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...

Almost every site has that one rule... We are the masters of the universe and can edit/delete, etc. any post, account, etc. that we want for whatever reason.


#13 Michael

Michael

    Meet Jay

  • +Clients
  • 19,587 posts

Posted 25 February 2009 - 04:25 PM

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?
  • ZackL and Marcher Technologies like this

Contact Me: Email · Facebook · Twitter · Google+


#14 CynicalFrost

CynicalFrost

    IPB Full Member

  • Members
  • PipPipPip
  • 133 posts

Posted 26 February 2009 - 02:15 PM

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.

Almost every site has that one rule... We are the masters of the universe and can edit/delete, etc. any post, account, etc. that we want for whatever reason.


#15 Purple Turnip

Purple Turnip

    IPB Newbie

  • Members
  • Pip
  • 38 posts

Posted 18 November 2009 - 05:23 PM

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.


++
  • ZackL likes this

#16 Nime

Nime

    IPB Member

  • +Clients
  • 53 posts

Posted 18 November 2009 - 05:43 PM

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 ))

#17 Nime

Nime

    IPB Member

  • +Clients
  • 53 posts

Posted 27 November 2009 - 04:05 PM

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?

#18 Nime

Nime

    IPB Member

  • +Clients
  • 53 posts

Posted 01 December 2009 - 01:44 PM

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.

#19 Tomick

Tomick

    IPB Newbie

  • +Clients
  • 10 posts

Posted 14 December 2009 - 06:13 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 :)

#20 Nime

Nime

    IPB Member

  • +Clients
  • 53 posts

Posted 14 December 2009 - 06:18 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users