Thanks for sharing your experience. I don't experience problem so I think I'll keep with the default params.<div>Could someone from spacewalk team confirm the optimization possibilities?</div><div>Another point about the autovacuum function: BZ 705935 address the fact that vacuum is useless. Is it still a valid BZ?</div> <div><pre class="bz_comment_text" style="font-size: medium; font-family: monospace; white-space: pre-wrap; width: 50em; "> Description of problem: Seems like Spacewalk is keeping database transactions open which leads to unremovable dead rows in tables.</pre></div><div> </div><div>Thanks,</div><div> </div><div>Pierre <div class="gmail_quote">2011/9/14 Scott Worthington <<a href="mailto:scott.c.worthington@gmail.com">scott.c.worthington@gmail.com</a>> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Hello, I'd like to share my experience with out-of-the-box installation of Spacewalk 1.5 on CentOS 6.0 x86_64. The out-of-the-box default configuration of postgresql is abysmal. You may be very familiar with MySQL, but postgresql is certainly not MySQL (install and forget). You _HAVE_ to make configuration changes and tune postgresql for optimum performance with Spacewalk. While reading this, please note that this is for the CentOS 6.0 x86_64 platform. I cannot speak for Ubuntu, Debian, or other CentOS releases, etc. In my installation, I am using 4GB of server RAM as a virtualized guest on VMware. I have NOT changed Java or Tomcat's memory heap settings (-Xms or -Xmx) and I don't believe it is necessary. Your slowness is due to postgresql. * Before making the following changes, shutdown Spacewalk and then Postgresql. Better yet, run Spacewalk on VMware and take a snapshot of your current setup for rollbacks (if you're like me, you've spent hours upon hours loading in Channels and their repo data). spacewalk-service stop service postgresql stop == Default postgresql configuration == Auto vacuum is NOT enabled. This causes the database server to crawl to a halt during the loading of thousands of RPMs via the "rhnpush" CLI and also through "spacewalk-repo-syncs --type yum". * Turn on autovacuum Edit your /var/lib/pgsql/data/postgresql.conf and find... #autovacuum=on ...and uncomment it: autovacuum=on In the same file, find... #track_counts = on ...and uncomment it: track_counts = on == Install pgtune == Next, tune postgresql using the pgtune package. I have found that the package pgtune is excellent for tuning your system. The following instructions have you download the .tar.gz file and create an installable RPM. I prefer install RPMs, not source. You can install source, that is your prerogative. * Download and compile pgtune (to install it as an RPM): * First, ensure you have rpmbuild installed yum install rpmbuild # next, download, put files in the correct directories, and build mkdir -p /usr/local/src/TARGZs/pgtune cd /usr/local/src/TARGZs/pgtune wget <a href="http://pgfoundry.org/frs/download.php/2449/pgtune-0.9.3.tar.gz" target="_blank">http://pgfoundry.org/frs/download.php/2449/pgtune-0.9.3.tar.gz</a> tar xvfz pgtune-0.9.3.tar.gz cp /usr/local/src/TARGZs/pgtune/pgtune-0.9.3.tar.gz /root/rpmbuild/SOURCES cp /usr/local/src/TARGZs/pgtune/pgtune-0.9.3/pgtune.spec /root/rpmbuild/SPECS cp /usr/local/src/TARGZs/pgtune/pgtune-0.9.3/pgtune-settingsdir.patch /root/rpmbuild/SOURCES cd /root/rpmbuild/SPECS #the spec file has an incorrect version number, the following command will fix it perl -npe 's/0\.9\.2/0.9.3/g' -i /root/rpmbuild/SPECS/pgtune.spec rpmbuild -ba pgtune.spec #If your rpmbuild fails, you may need to install dependencies, look at the output of the rpmbuild #install the RPM rpm -ivh /root/rpmbuild/RPMS/noarch/pgtune-0.9.3-1.el6.noarch.rpm After pgtune is installed, run it on your postgresql.conf file: cd /var/lib/pgsql/data pgtune -i postgresql.conf -o postgresql.conf.pgtune * Make a backup of your existing, "working" configuration (replace YYYYMMDD with today's date) cp -p postgresql.conf postgresql.conf-working-YYYYMMDD * Replace existing config cp postgresql.conf.pgtune postgresql.conf * I do not believe that the postgresql.conf file contains the "default" parameters in the lines that are commented out (unlike packages like postfix). I recommend that you do not trust the postgresql.conf default file as a source of "default" parameters. * I have also edited by hand and uncommented the entire AUTOVACUUM PARAMETERS section and increased the autovacuum_max_workers from 3 to 5: #------------------------------------------------------------------------------ # AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds. autovacuum_max_workers = 5 # max number of autovacuum subprocesses autovacuum_naptime = 1min # time between autovacuum runs autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum autovacuum_analyze_threshold = 50 # min number of row updates before # analyze autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit * After you have made the above changes, attempt to restart postgresql (it will most likely fail) service postgresql restart * Yes, it will FAIL. Why did it fail? Most likely postgresql needs more memory now and the default kernel memory setup is too low (Hopefully you didn't introduce a typo) * Examine the contents of postgresql's startup log: less /var/lib/pgsql/pgstartup.log * You may see something like this at the end: FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was shmget(key=5432001, size=1040752640, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 1040752640 bytes), reduce PostgreSQL's shared_buffers parameter (currently 122880) and/or its max_connections parameter (currently 85). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration. * You have to bump up your Linux kernel.shmmax to the number, in the example above, the number is "1040752640" * First, non-persistently: sysctl -w kernel.shmmax=1040752640 * Try to restart postgresql service postgresql restart * If it fails, look again inside of /var/lib/pgsql/pgstartup.log and see if you made a mistake with the shmmax number and fix your kernel.shmmax * Finally, make the number that you ended up using for kernel.shmmax and make it persistent between boots: cat >> /etc/sysctl.conf << 'END_OF_FILE' #YYYY-MM-DD HHMM (your-initials) kernel.shmmax=1040752640 END_OF_FILE I have found that making those changes above makes postgresql and Spacewalk usable and much much faster. I have not experienced the "500 server timeout" error since. If you are having problems with loading packages into your Spacewalk, running top will usually show postgresql is sitting on a "DELETE" or other process that is taking forever -- the above may help speed up your postgresql. I hope you have the best of luck with your Spacewalk & postgresql deployment on CentOS 6.0 x86_64, and I hope the above instructions may benefit you. _______________________________________________ Spacewalk-list mailing list <a href="mailto:Spacewalk-list@redhat.com">Spacewalk-list@redhat.com</a> <a href="https://www.redhat.com/mailman/listinfo/spacewalk-list" target="_blank">https://www.redhat.com/mailman/listinfo/spacewalk-list</a> </blockquote></div> </div>