1. MySQL has unbuffered queries. It's for when you want to send an INSERT, UPDATE, or DELETE, and at the given moment you don't intend to care about the result in your website and it can show up a couple seconds later, async rather than sync. What's the equivalent with PostgreSQL on PHP and how do you execute it?
2. I'm trying to find a good resource on the web on how to tweak a postgresql.conf file. Unfortunately all I find are bloggers with no proof to back up their advice, or they are incomplete in what they are saying. For instance one site said to tweak parameter x by RAM / y. Okay, well, I'd like to know the proof about why, but okay, I can sort of work with this. But then later on he might say to tweak parameter x, but then not tell me the rest of it -- like how to tweak parameter x. Tweak x up or down? Tweak it by RAM / y? How much should y be?
3. Which is faster? PDO or pg_* queries?
4. I'm using pg_query for my INSERT, UPDATE, and DELETE statements, and then ignoring the results returned back from pg_query because there are none. So, is that the fastest approach, or is there a faster API I should be using for those sorts of statements?
Got PostgreSQL Advice?
Moderator: General Moderators
-
jack_indigo
- Forum Contributor
- Posts: 186
- Joined: Sun Jun 08, 2008 11:25 pm
Re: Got PostgreSQL Advice?
Err... that's not what unbuffered queries are for. They're for queries that would normally return a massive set of results ... mysql doesn't need to do the entire lot and cache it, instead it does the query 'on the fly' while you're pulling out records.jack_indigo wrote:1. MySQL has unbuffered queries. It's for when you want to send an INSERT, UPDATE, or DELETE, and at the given moment you don't intend to care about the result in your website and it can show up a couple seconds later, async rather than sync.
In fact, you're actually a bit back-to-front. Using an unbuffered query means that MySQL has to hold that query until it's released rather than pushing it out to a named buffer. For than reason it can't perform any other queries until that one is done and has been released. If you do any query (including insert, update and delete queries) as unbuffered you need to make sure you've finished with the result (with mysql_free_result() ) before you do a new query because the next one will drop the current result set, or wait if it's still executing and then drop it. I think if you used unbuffered for a very slow delete for example you'd be in danger of it not being completed when you do the next query.
If you're not concerned about checking an insert worked properly then use the DELAYED keyword, and similarly use the LOW_PRIORITY keyword for a delete or an update to make them wait until the table is clear of connections before executing. You can also use LOW_PRIORITY on inserts but I think DELAYED is better.
-
jack_indigo
- Forum Contributor
- Posts: 186
- Joined: Sun Jun 08, 2008 11:25 pm
Re: Got PostgreSQL Advice?
Wow. Didn't know that, onion2k. Thanks for the tip!
EDIT: Oh, I see in the PostgreSQL docs that PostgreSQL doesn't have DELAYED in its syntax.
EDIT: Oh, I see in the PostgreSQL docs that PostgreSQL doesn't have DELAYED in its syntax.
-
jack_indigo
- Forum Contributor
- Posts: 186
- Joined: Sun Jun 08, 2008 11:25 pm
Re: Got PostgreSQL Advice?
Okay, I think I have my other questions answered. So all I need is just this last one:
I'm trying to find a good resource on the web on how to tweak a postgresql.conf file. Unfortunately all I find are bloggers with no proof to back up their advice, or they are incomplete in what they are saying. For instance one site said to tweak parameter x by RAM / y. Okay, well, I'd like to know the proof about why, but okay, I can sort of work with this. But then later on he might say to tweak parameter x, but then not tell me the rest of it -- like how to tweak parameter x. Tweak x up or down? Tweak it by RAM / y? How much should y be?
Anyone got a bookmark to share on what they use for tweaking this conf file?
I'm trying to find a good resource on the web on how to tweak a postgresql.conf file. Unfortunately all I find are bloggers with no proof to back up their advice, or they are incomplete in what they are saying. For instance one site said to tweak parameter x by RAM / y. Okay, well, I'd like to know the proof about why, but okay, I can sort of work with this. But then later on he might say to tweak parameter x, but then not tell me the rest of it -- like how to tweak parameter x. Tweak x up or down? Tweak it by RAM / y? How much should y be?
Anyone got a bookmark to share on what they use for tweaking this conf file?
Re: Got PostgreSQL Advice?
Why do you want to tweak the conf file in the first place? Make changes when they're needed, not just for the sake of changing things.
-
jack_indigo
- Forum Contributor
- Posts: 186
- Joined: Sun Jun 08, 2008 11:25 pm
Re: Got PostgreSQL Advice?
Oh, with PostgreSQL, and unlike MySQL, I was told it was different. By default when you install PostgreSQL, the conf file is tuned way back so that it works on most systems and accounts for a small memory footprint. That's not good. You really want to tweak your postgresql.conf file such that you utilize a healthy dose of RAM on the server for various memory pools, and this requires a little computation on how much RAM you have.onion2k wrote:Why do you want to tweak the conf file in the first place? Make changes when they're needed, not just for the sake of changing things.
Also, I was told very strongly that fsync on Postgres should be set to ON because if you don't and the server is powered off while the server is running down a list of transactions, it will lose those transactions and will not try to recover them on server reboot. The problem is a slight performance hit, but it's worth it because you don't want data integrity issues of any sort.