On 05/06/10 03:24, Jon Schewe wrote:
> Which filesystem are you on too? I've found that ext3 is significantly
> faster than ext4 and xfs.
>
Please don't top post, it makes threading harder to read.
I'm using Ext4 - on a 48Gb machine with high performance raid controller
(battery backup and 500Mb cache) with Raid10 Diskset. The database file
area is over 60Gb and there are around 250million files in the database
Speed of filesystems is highly application _and_ tuning dependant. It's
worthwhile reading the man pages.
Pgdump takes about 15 minutes and that's mostly limited by disk read
speeds. Mysqldump was about the same size.
Note that Pgdump and mysqldump are NOT constrained by indexes, those
only come into play for normal queries.
Postgres is only slightly faster for me than mysql, but it uses a lot
less memory. If you hit swap then that will translate into significantly
faster operation.
PG version is postgresql-8.4.4 EL5 - downloaded from the postgresql.org
website. It's a significant improvement on 8.1, but a bit more stringent
about posix compliance if you've rolled your own queries.
Having said all that, the single biggest performance improvement I've
made so far is to allow postgres to lock more memory than the default.
I suspect the OP's problem is lack of memory as he's got around 1.4
billion entries in his database and only 8Gb ram. The dump time sounds
about right for the number of files he has.
I'd suggest adding more ram in the first instance and considering using
SSDs
> On 06/04/2010 04:01 PM, Stephen Thompson wrote:
>
>> Thanks, yes it is Linux. I will look at those limits settings.
>> And yes, I've built indexes and analyze (nothing to vacuum yet since
>> it's a fresh import).
>>
>> Stephen
>>
>> On 06/04/2010 12:16 PM, Alan Brown wrote:
>>
>>
>>> On Fri, 4 Jun 2010, Stephen Thompson wrote:
>>>
>>>
>>>
>>>> Correction:
>>>> I didn't notice the 8k per unit settings at first with postgres 8.1.
>>>> Should read:
>>>> effective_cache_size = 786432 # 6Gb
>>>>
>>>>
>>> Assuming this is linux, you need to tweak /etc/sysctl/limits.conf a
>>> little:
>>>
>>> postgres soft memlock unlimited
>>> postgres hard memlock unlimited
>>> @postgres hard memlock unlimited
>>> @postgres soft memlock unlimited
>>> bacula soft memlock unlimited
>>> bacula hard memlock unlimited
>>> @bacula soft memlock unlimited
>>> @bacula hard memlock unlimited
>>>
>>> postgres soft rss unlimited
>>> postgres hard rss unlimited
>>>
>>>
>>> Don't forget to build the indexes and run analyse/vacuum commands.
>>>
>>> So far I'm finding Postgres is far more forgiving than MySQL and has far
>>> fewer parts to tune...
>>>
>>>
>>>
>>>
>>>
>>>> On 06/04/2010 10:58 AM, Stephen Thompson wrote:
>>>>
>>>>
>>>>> Hello everyone,
>>>>>
>>>>> We recently attempted a mysql to postgresql migration for our bacula
>>>>> 5.0.2 server. The data migration itself was successful, however we are
>>>>> disappointly either getting the same or significantly worse performance
>>>>> out of the postgres db.
>>>>>
>>>>> I was hoping that someone might have some insight into this.
>>>>>
>>>>> Here is some background:
>>>>>
>>>>> software:
>>>>> centos 5.5 (64bit)
>>>>> bacula 5.0.2 (64bit)
>>>>> postgresql 8.1.21 (64bit)
>>>>> (previously... mysql-5.0.77 (64bit) MyISAM)
>>>>>
>>>>> database:
>>>>> select count(*) from File --> 1,439,626,558
>>>>> du -sk /var/lib/pgsql/data --> 346,236,136 /var/lib/pgsql/data
>>>>>
>>>>> hardware:
>>>>> 1Tb EXT3 external fibre-RAID storage
>>>>> 8Gb RAM
>>>>> 2Gb SWAP
>>>>> 2 dual-core [AMD Opteron(tm) Processor 2220] CPUs
>>>>>
>>>>>
>>>>> Some of the postgres tuning that I've attempted thus far (comments are
>>>>> either default or alternatively settings I've tried without effect):
>>>>>
>>>>> #shared_buffers = 1000 # min 16 or max_connections*2, 8KB each
>>>>> shared_buffers = 262144 # 2Gb
>>>>> #work_mem = 1024 # min 64, size in KB
>>>>> work_mem = 524288 # 512Mb
>>>>> #maintenance_work_mem = 16384 # min 1024, size in KB
>>>>> maintenance_work_mem = 2097152 # 2Gb
>>>>> #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
>>>>> checkpoint_segments = 16
>>>>> #checkpoint_warning = 30 # in seconds, 0 is off
>>>>> checkpoint_warning = 16
>>>>> #effective_cache_size = 1000 # typically 8KB each
>>>>> #effective_cache_size = 262144 # 256Mb
>>>>> effective_cache_size = 6291456 # 6Gb
>>>>> #random_page_cost = 4 # units are one sequential page fetch cost
>>>>> random_page_cost = 2
>>>>>
>>>>> Now, as to what I'm 'seeing'. Building restore trees are on par with my
>>>>> previous mysql db, but what I'm seeing as significantly worse are:
>>>>>
>>>>> mysql postgresql
>>>>> Within Bat:
>>>>> 1) Version Browser (large sample job) 3min 9min
>>>>> 2) Restore Tree (average sample job) 40sec 25sec
>>>>> 3) Restore Tree (large sample job) 10min 8.5min
>>>>> 2) Jobs Run (1000 Records) 10sec 2min
>>>>>
>>>>> Within psql/mysql:
>>>>> 1) select count(*) from File; 1sec 30min
>>>>>
>>>>> Catalog dump:
>>>>> 1) mysqldump/pgdump 2hrs 3hrs
>>>>>
>>>>>
>>>>> I get a win on building Restore trees, but everywhere else, it's
>>>>> painfully slow. It makes the bat utility virtually unusable as an
>>>>> interface. Why the win (albeit moderate) in some cases but terrible
>>>>> responses in others?
>>>>>
>>>>> I admit that I am not familiar with postgres at all, but I tried to walk
>>>>> through some of the postgres tuning documents, including the notes in
>>>>> the bacula manual to arrive at the above settings. Also note that I've
>>>>> tried several variants on the configuration above (including the
>>>>> postgres defaults), don't have a detailed play by play of the results,
>>>>> but the time results above seemed typical regardless of what settings I
>>>>> tweaked.
>>>>>
>>>>> Any help would be greatly appreciated!
>>>>> Stephen
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
> ------------------------------------------------------------------------------
> ThinkGeek and WIRED's GeekDad team up for the Ultimate
> GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
> lucky parental unit. See the prize list and enter to win:
> http://p.sf.net/sfu/thinkgeek-promo
> _______________________________________________
> Bacula-users mailing list
> Bacula-users AT lists.sourceforge DOT net
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>
>
------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
lucky parental unit. See the prize list and enter to win:
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
