backup and page size clarification required

[Johan_sapbasis]

Hi guys,

Looking at the notes for backup and recovery.

1588316 - SYB: Configure automatic database and log backups

It states Create a job and a schedule for each database you want to back up, since you cannot have one job dumping 3 databases simultaneously for example master,SID at the same time is the recommendation to dump the small databases first and the big database SID last?

(I think this would be the logic to avoid overlapping jobs)

Secondly when creating sp_thresholdaction_logfreespace as per note 1801984 - SYB Automated management of long running transactions, I need to create the threshold and need to calculate the pages.

My transaction log is actually only 10GB in this example , the database is running 2k pages. Now I know how to convert the 2k page conversion to MB and reverse.

Converting from 2K page size to MB -> 1792000/512 = 3500MB

Converting from MB to 2K Page size->3500MB*512=1792000

But my question is when I run the following queries it seems my logsegment has a different pagesize!

use DBSID

go

sp_helpsegment logsegment

go

Then my return results are:

         syslogs                                      0

total_size        total_pages     free_pages      used_pages     reserved_pages

----------------- --------------- --------------- -------------     ---------------

10240.0MB         655360          651224          4136         0

If I select @@pagesize it is set to 2K.

1> select @@pagesize

2> go

-----------

        2048

(1 row affected)

I think my pagesize for my logsegment is a different size if I use 655360 as the size to calculate the MB conversion is 1280MB, which is oviously incorrect could you please clarify this for me, and how to determine logsegment page size?

Kind Regards,

Johan

[Johan_sapbasis]

Hi Mark,

For the purpose of my exercise I need to know how to calculate the page size at which to set my sp_threshold action therefore I need to be able to determine what page size my log segment is so I can do the calculations to configure my thresholds accurately.

Thanks for the pagesize answer, now just need to confirm the other answer related to the jobs.

Johan

[Mark_A_Parsons]

'page size' is a fixed value and can be determined by querying @@maxpagesize.  You don't 'calculate' any page sizes.

To define thresholds (via sp_addthreshold) you need to know the number of pages (not necessarily the size of the pages).

Thresholds are defined in terms of number of pages.

sysusages.size is the number of pages allocated on a disk fragment.

If you're defining thresholds as a percentage of the segment (eg, thresholds at 10%, 20% and 30% free), then you just need sum(sysusages.size) for the segment in question (this should equal the total_pages value you see in sp_helpsegment), multiply by your %, and you've got the number of pages needed to define the threshold. Net result is all calculations are done in terms of number of pages, ie, no need to convert to/from MB.  Since the calculation to determine thresholds is based solely on number of pages ... the page size never comes into question ... so you can use the same threshold calculations regardless of page size (@@maxpagesize).

Now, if you still want to work in terms of (mega)bytes, there's nothing to keep you from multiplying your number of pages by @@maxpagesize to get bytes (obviously divide by 1024*1024 to get MBs). Obviously you'll then need to divide by @@maxpagesize to get back to a number of pages to supply to sp_addthreshold.

[former_member89972]

Just a comment : (with a hope that someone from SAP is listening ! )

We wish SAP (earlier Sybase) folks followed a consistent KB/MB/GB/TB measures for any commands/stored procedures that dealt with storage. Counting bytes in 1024 chunks has been around for a long time

As of now it is a mixed bag with some commands/procedures following that and some not !

Cheers

Avinash

[former_member182259]

I think it would be best if you cited examples of commands where you found problems.   Most - including sp_configure, disk init - allow values in KB, MB or GB - your choice.   Others simply assume MB (e.g. create database).   All of this is documented and we "wish" () that folks would read the documentation.  However, in some cases - especially when working with activities where precise placement is desired - e.g. setting a threshold at 75% of the log - 75% could be a fractional MB....hence 'pages' are sometimes used as a bit more precise.    While the 2K @@pagesize is historical and present for backward compatibility in older systems - it actually still is the size of memory allocations by ASE.   You can specify the memory in KB, MB or GB via sp_configure or sp_cacheconfig.....or the age old way of MB*512=2KB pages.

BTW - OS's still allocate memory in 4KB 'pages' as well....and most have other page sizes (e.g. hugepages) too.....   the later can get to be a consideration when tuning the OS kernel to the proper size of hugepages.....and most shmmax kernel settings I have seen still use (horrors!) bytes.   Kinda like that whole 1 & 0 binary thing - been around so long, I think we are stuck with 'pages' ....at the OS and also in ASE....but as Mark demonstrated, sometimes it is useful to know how to convert.  One consideration is that "fractional" MB/KB/GB is really ugly - and unfortunately it can happen - so, usually when tuning the OS (e.g. hugepages) I suggest folks start in GB, stay with an even/full GB (to avoid issues with 256MB allocation chunks), then convert to number of hugepages to tune OS kernel.   The same technique is useful in ASE when you need (or want) to specify pages

Until then - tell me the commands you are forced to use bytes/pages....and I will look into it.   No promises of fixes, but .....at least we can discuss the rationale and you might get a better understanding of the problem....and we can file a FR to get it fixed.

[former_member89972]

I stand corrected

Only stored procedures related to thresholds need storage specified in pages as unit.

All other commands/stored procedures DO allow storage specified in more friendly units of KB/MB/GB/TB

Avinash