Please login or register.

Login with username, password and session length
Advanced search  

News:

Latest sNews - sNews 1.7 - with its own forums - for discussion and user mods.

Author Topic: sNews and PostgreSQL = Possible?  (Read 3487 times)

Armen

  • Sr. Member
  • ****
  • Karma: 41
  • Posts: 338
    • http://www.funnydays.ru
sNews and PostgreSQL = Possible?
« on: March 03, 2007, 10:26:10 am »

I've heard of great leap ahead of the PostgreSQL ODBC Database recently and of it's high scalability.

Also, I found some articles about migration from MySQL to PostgreSQL, which is not too complicated.

Has anyone of you guys trшув to adapt sNews to use PSQL unstead of MySQL for your projects, or does anyone have any experience in PHP + PSQL development?
Logged
Now ogres, oh, they're much worse. They'll make a suit from your freshly peeled skin. They'll shave your liver, squeeze the jelly from your eyes... Actually, it's quite good on toast.

codetwist

  • Hero Member
  • *****
  • Karma: 50
  • Posts: 940
sNews and PostgreSQL = Possible?
« Reply #1 on: March 03, 2007, 12:08:50 pm »

There is nothing in PostgreSQL that would add something new to sNews functionality as it is.

Usually, some DB abstraction layer is introduced when DB independence/switching is desired. That means a lot of changes through whole sNews. If want to try go for some ready made DB access package like adodb lite or adodb.

But key gains were described in first sentence.
« Last Edit: September 24, 2007, 12:24:56 pm by codetwist »
Logged

Armen

  • Sr. Member
  • ****
  • Karma: 41
  • Posts: 338
    • http://www.funnydays.ru
sNews and PostgreSQL = Possible?
« Reply #2 on: March 03, 2007, 12:26:21 pm »

I didn't mean "add new functionality". It's just when there are 20-25 simultaneous quieries, PostgreSQL works fine (thanks to it's features MySQL doesn't have) when MySQL fails (and you know why).

Of course a lot of changes would be nessesary, that's not a question.

I'd like to know if someone has done any real benchmarks or has PSQL experience.
Logged
Now ogres, oh, they're much worse. They'll make a suit from your freshly peeled skin. They'll shave your liver, squeeze the jelly from your eyes... Actually, it's quite good on toast.

codetwist

  • Hero Member
  • *****
  • Karma: 50
  • Posts: 940
sNews and PostgreSQL = Possible?
« Reply #3 on: March 03, 2007, 12:56:32 pm »

Ahem ... mysql fails ... all I know that on queries MySQL beats PostgreSQL. And most of sites with heavy loads, etc. are run on MySQL not PostgreSQL which until recently was notorious for problems despite the fact that it had earlier such features as transaction processing, etc.

And 20-25 queries - that's insignificantly small number to talk about scalability ... unless box on which DB server is run is complete crap or severely limited on resources. If that's the case - PostgreSQL will not help.

Anyway have worked with both and there is no such a thing as silver bullet out of the blue.

« Last Edit: September 24, 2007, 12:25:16 pm by codetwist »
Logged

Armen

  • Sr. Member
  • ****
  • Karma: 41
  • Posts: 338
    • http://www.funnydays.ru
sNews and PostgreSQL = Possible?
« Reply #4 on: March 03, 2007, 01:11:33 pm »

But MySQL is not standart-compliant. Will it make "MySQL -> AnotherDB" conversion extremely difficult?
Logged
Now ogres, oh, they're much worse. They'll make a suit from your freshly peeled skin. They'll shave your liver, squeeze the jelly from your eyes... Actually, it's quite good on toast.

codetwist

  • Hero Member
  • *****
  • Karma: 50
  • Posts: 940
sNews and PostgreSQL = Possible?
« Reply #5 on: March 03, 2007, 02:06:52 pm »

Errr ... if You want db server that is 100% standard compliant You'll have to wait, and wait and wait ... My estimate - forever.  AFAIK there is no such a thing as 100% standards compliant DB server and there is whole truckload of standards in that field anyway.

Better question to discuss would be this - what is practical problem that drives Your interest in this direction?

« Last Edit: September 24, 2007, 12:25:34 pm by codetwist »
Logged

Keyrocks

  • Doug
  • ULTIMATE member
  • ******
  • Karma: 449
  • Posts: 6020
  • Semantically Challenged
    • snews.ca
sNews and PostgreSQL = Possible?
« Reply #6 on: March 03, 2007, 02:32:58 pm »

Over the last couple of years... I've asked several server hosting techies why their firm uses Apache & MySQL and they all say it's because these provide the most stable (overall) platform for web hosting on servers using Linux (usually Red Hat) operating systems. The use of Apache/MySQL has grown considerably in recent years... and I understand that close to 80% of all web host servers in the world are running that configuration these days. That in itself is quite a testament.
Logged
Do it now... later may not come.
-------------------------------------------------------------------------------------------------
sNews 1.6 MESU | sNews 1.6 MEMU

Armen

  • Sr. Member
  • ****
  • Karma: 41
  • Posts: 338
    • http://www.funnydays.ru
sNews and PostgreSQL = Possible?
« Reply #7 on: March 04, 2007, 08:05:55 am »

Quote from: codetwist
Better question to discuss would be this - what is practical problem that drives Your interest in this direction?
Scalability. Moving from one-core server to two-core one will result in productivity growth for PSQL and almost nothing for MySQL (at least, at the moment).

I've seen PSQL based server and know the man, who uses it on website (www.overclockers.ru). They've got enormous-size DB and use PSQL mostly because of its scalability. He also says, that lastest versions of PSQL are not slower than MySQL even a bit...

Well, here it is...
Logged
Now ogres, oh, they're much worse. They'll make a suit from your freshly peeled skin. They'll shave your liver, squeeze the jelly from your eyes... Actually, it's quite good on toast.

codetwist

  • Hero Member
  • *****
  • Karma: 50
  • Posts: 940
sNews and PostgreSQL = Possible?
« Reply #8 on: March 04, 2007, 11:58:38 am »

Scalability isn't problem, it's solution  AFAIK.

Well, I assume that You checked that db use scenarios under sNews are such that real gains will be comparable with benchmarks available on this topic.

Personally, I have my doubts because of this - most queries are small and simple, mostly for single, sometimes for few records. Will these benefit significantly after porting to PostgreSQL and being put on dual core ... I think not. For me it looks like currently DB queries for sNews do not require significant CPU resources.

As for PostgreSQL being used for serious stuff - I don't doubt or question that.

« Last Edit: September 24, 2007, 12:25:57 pm by codetwist »
Logged

Keyrocks

  • Doug
  • ULTIMATE member
  • ******
  • Karma: 449
  • Posts: 6020
  • Semantically Challenged
    • snews.ca
sNews and PostgreSQL = Possible?
« Reply #9 on: March 04, 2007, 03:07:31 pm »

I would say the real question to answer is... will running sNews with a PostgreSQL dbase result is measurable performance gains over running with a MySQL dbase?
I would say the question is moot (redundant). As codetwist points out, "most queries are small and simple...", and snews only uses 4 tables in its current form. As far as capacity is concerned, MySQL is only limited by the operating system's file-size limits.
Logged
Do it now... later may not come.
-------------------------------------------------------------------------------------------------
sNews 1.6 MESU | sNews 1.6 MEMU