Re: [Bacula-users] postgres tuning?

[Jon S. <jps...@mt...>]

Which filesystem are you on too? I've found that ext3 is significantly
faster than ext4 and xfs.

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
>>>>         
>>>
>>>
>>>       
>>     
>
>