Hi,
I would like to share my experience with using postgresql as a
database server for SOGo...
Any suggestions for tunig database or doing things better are welcome.
Regards
Milos--
users@sogo.nu
https://inverse.ca/sogo/lists
I have 17,560 active users on the production SOGo server,
it means 166,894 tables and 5.3 GB in the sogo postgresql database.
I'm running SOGo-1.3.9 on the dedicated machine and I will do upgrade
to 1.3.12 version which will be running on the new machine (modern server,
2x Xeon E5645 @ 2.40GHz, 32 GB RAM and 15K SAS disks).
So I need to move sogo dates to new machine and migrate them to 1.3.12
and I'm finding the best way how to do it.
Let me note I'm not database expert...
Way A - using "sogo-tool backup & restore"
=====================================================
For daily backup I'm using "sogo-tool backup ALL" way
(below is an explanation why).
I transfered all "sogo-tool backup" data to new machine and
I restored them through "sogo-tool restore" command there.
This procedure needs about 5.5 hours and it doesn't need
to use migration script to 1.3.12
(converting c_content from VARCHAR to TEXT in table sogo_folder_info),
because dates are inserted into new tables with correct type of the column.
When I run "sogo-tool restore" in 4 parallel threads, it needs 2 hours.
Way B - moving and migrating database dates
====================================================
I cannot use simple command
pg_dump sogo > outfile
on the production server. It worked fine when I had a few thousands of tables.
But when I have 166 thousands tables, this command writes:
---
pg_dump: WARNING: out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
pg_dump: The command was: LOCK TABLE public.sogoXXX00148966195 IN ACCESS SHARE
MODE
---
I tried to tune some DB parameters, but without success.
So I stopped postgresql and created tar file of all /var/lib/postgresql/
directory.
Then I moved tar archive to new machine, I stopped postgresql and untared
archive there.
I checked all is fine after postgresql restart.
After that migration script sql-update-1.3.11_to_1.3.12.sh is needed to run.
Let's see what it does:
1) Acquisition of a list of tables
-------------------------------------------------------------------------------------
This command:
psql -t -U sogo -h localhost sogo -c "select split_part(c_location, '/', 5)
from sogo_folder_info;"
writes 55615 names of the tables in my case and it needs 2 sec.
2a) Acquisition of database commands into shell variable "sqlscript":
-------------------------------------------------------------------------------------
function convVCtoText() {
oldIFS="$IFS"
IFS=" "
part="`echo -e \"ALTER TABLE $table ALTER COLUMN c_content TYPE
TEXT;\\n\"`";
sqlscript="$sqlscript$part"
IFS="$oldIFS"
}
tables=`psql -t -U $username -h $hostname $database -c "select
split_part(c_location, '/', 5) from $indextable;"`
for table in $tables;
do
convVCtoText
done
This is original procedure and it needs more as 7 hours in my case. Oh. Really
:-(
2b) Acquisition of the database commands into file:
-------------------------------------------------------------------------------------
When I modified original script which do the same thing but it writes commands
into external file
instead of shell variable:
function convVCtoText() {
echo "ALTER TABLE $table ALTER COLUMN c_content TYPE TEXT;" >>
/tmp/commands
}
tables=`psql -t -U $username -h $hostname $database -c "select
split_part(c_location, '/', 5) from $indextable;"`
for table in $tables;
do
convVCtoText
done
it needs 3 sec only. Much better ;-)
3a) Applying of database commands:
-------------------------------------------------------------------------------------
psql -U sogo sogo -f /tmp/commands
it needs 214 min.
3b) Parallel applying of database commands:
-------------------------------------------------------------------------------------
Because PostgreSQL is process-based, not thread-based
(and I have lot of CPU cores and threads on the new machine)
I split commands from /tmp/commands into 4 files
and I ran them parallel in 4 processes:
psql -U sogo sogo -f /tmp/commands[1-4]
it needs 40 min. Nice ;-)
Disks utilization is about 90% in this case.
