On 02/07/2011 09:17 PM, felix wrote:
Well.... it said "Failed to shutdown ..............." and then returned control. and then proceeded to run for about an hour. I'm not sure how graceful that is.
Ah, but that was just the control script that sends the database the command to shut down. The 'graceful' part, is that the database is being nice to everyone trying to do things with the data inside.
The control script has a timeout. So it'll send the command, wait a few seconds to see if the database responds, and then gives up. At that point, you can use a fast shutdown to tell the database not to be so nice, and it'll force disconnect all users and shut down as quickly as possible while maintaining data integrity.
The easiest way to see this in action is to take a look at the postgres log files. In most default installs, this is in /your/pg/dir/pg_log and the files follow a postgresql-YYYY-MM-DD_HHMMSS.log format and generally auto-rotate. If not, set redirect_stderr to on, and make sure log_directory and log_filename are both set. Those are in your postgresql.conf, by the way. :)
I've only been using postgres since we migrated in May
Aha. Yeah... relatively new installs tend to have the worst growing pains. Once you shake this stuff out, you'll be much better off.
its only conjecture that the issue is file space bloat or free map problems. those are overall issues that I will get to as soon as I can. but this is table specific.
With 300k rows, count(*) isn't a good test, really. That's just on the edge of big-enough that it could be > 1-second to fetch from the disk controller, even if the table is fully vacuumed. And in your case, that table really will likely come from the disk controller, as your shared_buffers are set way too low. The default settings are not going to cut it for a database of your size, with the volume you say it's getting.
But you need to put in those kernel parameters I suggested. And I know this sucks, but you also have to raise your shared_buffers and possibly your work_mem and then restart the DB. But this time, pg_ctl to invoke a fast stop, and then use the init script in /etc/init.d to restart it.
I am not a DBA,
You are now. :) You're administering a database, either as part of your job description, or because you have no choice because your company doesn't have an official DBA. Either way, you'll need to know this stuff. Which is why we're helping out.
-- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
