PostgreSQL 8.4 + is really taking a high load

Found a bug? Tell us!!
Post Reply
rootnl2k
Regular
Posts: 50
Joined: Tue Jul 17, 2012 7:15 am

PostgreSQL 8.4 + is really taking a high load

Post by rootnl2k »

I got Serendipity to work on Postgres8.1 m but cannot duplicate said success on 8.4+ .

Is anyone apart from me running PG and s9y on BSD box?
garvinhicking
Core Developer
Posts: 30022
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: PostgreSQL 8.4 + is really taking a high load

Post by garvinhicking »

Hi!

I remember PGSql at some point dropping some OID-related feature, which s9y requires for the autoincrements.

However I don't use pgsql myself, so this is just something that I vaguely remember...

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
rootnl2k
Regular
Posts: 50
Joined: Tue Jul 17, 2012 7:15 am

Re: PostgreSQL 8.4 + is really taking a high load

Post by rootnl2k »

Any way of fixing this? You can join the Postgres General Mailing list.

I also wonder if thiswhy MySQL was also going crazy on me as I have over 1700 posts in one blog.
Last edited by rootnl2k on Tue Feb 18, 2014 3:27 pm, edited 1 time in total.
garvinhicking
Core Developer
Posts: 30022
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: PostgreSQL 8.4 + is really taking a high load

Post by garvinhicking »

[quote="rootnl2k"]Any way of fixing this?[quote]

Possible, but I'm not a posgresql user... we would need a developer with postgresql knowledge.

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
rootnl2k
Regular
Posts: 50
Joined: Tue Jul 17, 2012 7:15 am

Re: PostgreSQL 8.4 + is really taking a high load

Post by rootnl2k »

Postgres DEvelopers? Start with pgsql-general@pgsql.org I think.

I can imagine some postgres developers wanting to help you as PostgresQL would love to support an alternative to Wordpress.
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: PostgreSQL 8.4 + is really taking a high load

Post by Timbalu »

rootnl2k wrote:Due to a huge amount of entires, MYSQL 5.1 was choking so I had to resort to migrating to Postgres
What was the main problem with your MySQL? This database is designed to work well with millions of entries, so 1700 entries can not make it choke around, if not being capsulated (limited) by your ISP, or is missing some needed tweaks which came with the S9y-upgrades over years. We had this here once with someone hitting an 1GB ISP sql limitation. There we recommended to ask for more resources and to start cleaning up the database from statistics and log tables (and other unneeded stuff).
I do not think that switching to Postgres would solve any limitation problems, if not needing load balancing and this sort of stuff, which is far beyond a simple blog web task. If you have full access to your BSD box, I assume you could make MySQL run more smoothly far more easy, than fixing PG increment and other issues.
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian
rootnl2k
Regular
Posts: 50
Joined: Tue Jul 17, 2012 7:15 am

Re: PostgreSQL 8.4 + is really taking a high load

Post by rootnl2k »

Ian, the main problem was blog spammers trying to spam every entry on the blog. The blog is http://www.nk.ca/blog/ and I tell all ;-) All right so we have identified a number of problems, can we work on solutions? Namely

1) PostgresQL beyond 8.1

2) MySQL is under a huge attack

.

The bad guys are not going to jail quickly and Wordpress is a complex hog. S9y is Way easier to work with!

Let's get ahead of the competition.

Call me an s9y evangelist becuas s9y has MYSQL and PostgreSQL options.

Forgot to mention I am the sysadmin at nk.ca .
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: PostgreSQL 8.4 + is really taking a high load

Post by Timbalu »

To say first, I cannot help with PostgreSQL issues and even on MySQL Garvin will possibly be the better talk.

Yes, you have a problem now, since nothing is really working. (And it is nothing which would vanish compared with WP.)
What I do not get is, why you think these huge attacks will stop if you change the underlaying database? You can not stop entry spammers by database change, IMO. There you need good anti spam measures by S9y, like spam bee, spamblock, spam bayes and on server side fail2ban/iptables. You may also need to tweak some htaccess redirects to stop ddos. If that all does not help you might need some caching.

I personally think you have way too much entries on a page, which slows down the system. An amount of 15 entries per page is not recommended, if you have some bigger entries. Then I recommend to stick underneath 10, like 6. This minors the resouces to fetch enormously. Then you are using quite some sidebar plugins too. Every Plugin takes an amount of used RAM and slows down performance too. Some of them even depend on outer services. Cut them down as much as you can! In short: Limit fetch and send resources!

Was that used MySQL a 5.x version database?

PS. Another thing I don't understand is, that you post full entries with mail spam as pure html, not capsulates as <pre> or <code> or similar. You don't need to wonder if that breaks the browsers html rendering at some point. That seems to be part of of your other frontend issues.
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian
rootnl2k
Regular
Posts: 50
Joined: Tue Jul 17, 2012 7:15 am

Re: PostgreSQL 8.4 + is really taking a high load

Post by rootnl2k »

For Postgres Help someone from the pgqsl-general@pgsql.org mailing list might be interested in helping out.

All right I did download all the spam event plugins; however
When I am at /path/to/serendipity_admin.php?serendipity[adminModule]=plugins

and click on

Event Plugins
Click here to install a new event plugin

All I see is

Event Plugins

Trying to open URL package_event_en.xml...
Fetched 467372 bytes from already existing file on your server. Saving file as /path/to/templates_c/package_event_en.xml...

And no menu . Hmm!

.htaccess redirects and caching a bit later.

I think you meant to say limit fetch and **save** resources! Done.

Also got repeats out.

MYSQL versions 5.1.73 moving to 5.5.36.
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: PostgreSQL 8.4 + is really taking a high load

Post by Timbalu »

Before you tried to install plugins, did you have back a full working state on your blog? Say "GREEN"?
rootnl2k wrote:I think you meant to say limit fetch and **save** resources! Done.
No. I meant "fetching" and "sending" resources. :)

I dont think an update 5.1 to 5.5 will mainly help.
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: PostgreSQL 8.4 + is really taking a high load

Post by Timbalu »

Just as an example:
What I had this morning, was a Serendipity blog (really being strictly plain by any extras) having massive connection problems with mysql too. It looked like the database was completely down.

I played around and did see phpmyadmin having access, so I looked to the tables and there were about 900 MB of data. I cleaned some of the tables, which do get very big over time, if you have them activated. This is commonly 'spamblocklog' and 'visitors' table. I purged about 500 MB of old content with some nice handmade SQL commands, optimized table afterwards and after that, all went smooth again.

Using these internal logtables is a performance crusher on busy sites. Better do analytics totally outside, like google analytics or an external piwik.

All that said, you still have too much entries per page / and still too much sidebar plugins (most of them failing).
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian
rootnl2k
Regular
Posts: 50
Joined: Tue Jul 17, 2012 7:15 am

Re: PostgreSQL 8.4 + is really taking a high load

Post by rootnl2k »

Timbalu wrote:Before you tried to install plugins, did you have back a full working state on your blog? Say "GREEN"?

rootnl2k wrote:I think you meant to say limit fetch and **save** resources! Done.
No. I meant "fetching" and "sending" resources. :)

I dont think an update 5.1 to 5.5 will mainly help.

GREEN
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: PostgreSQL 8.4 + is really taking a high load

Post by Timbalu »

All those sidebar errors and duplicates do not indicate a "GREEN" GO in my eyes! :wink:

You might even need to cleanup the database too. You need a full working installation back before you can search for unnormal issues in a working environment.
Else it is just a wild guess what might stop you from installing event plugins. (Have a deep look into your servers access and error logs).
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian
Post Reply