Restrictions on datafiles greater then 2 GB

[Former Member]

1) Is there any limitation on MaxDB data/log volume size ?

My doubt is ...... there are many limitation on unix commands to work with files with length greater than 2GB (ie.: cp). We use, like best practices, files until 2GB length.

2) My database will have 500 GB of data.

Is better to work with a few big data volumes (ie.: 20 x 25GB), or many little data volume (ie.: 250 x 2GB) ?

Best regards,

Ricardo

[Former Member]

Thanks for your help.

I was worry about datafiles with 10-20GB and everyone tells me to work with datafiles with 2GB. But I decided to create bigger datafiles. Your answer give me the basement that I need to make my decision.

[markus_doehr2]

> I was worry about datafiles with 10-20GB and everyone

> tells me to work with datafiles with 2GB. But I

> decided to create bigger datafiles. Your answer give

> me the basement that I need to make my decision.

Restrictions of those kinds are usually restrictions of the underlying filesystem used (e. g. a standard HP-UX installation with JFS/VxFS doesn't format automatically with 'large file' enabled, you need to specify that explicitly).

A good advise is usually using as many volumes as you have physical harddisks, so I/O is able to spread nicely without being queued up.

--

Markus

[Former Member]

I agree with Markus,

You should ideally create a maxdb volume for every physical HDD on your server/SAN. EG if your 500GB database has been allocated say approx 800GB of SAN space which is made up of 12 x 72GB drives, then ideally you want to have 12 volumes of 72GB defined in the database.

This allows MaxDBs automatic balancing of I/O features to work effictively and dynamically spread hot spots across different volumes for you.

Cheers

Shaun

[Former Member]

Hi Shaun,

I don't have the concept of physical disk on my environment, because I'm working with a storage. On the storage, I'll have two kinds of volumes: 80 GB and 40 GB.

For a 500GB database, I'm thinking to work (on MAXDB) whith data volumes with 10-20GB (25-50 volumes). Or is it better to work with less data volumes but bigger on size ?

Ricardo

[Former Member]

Hi Richardo,

We also use a storage array (SAN), and we allocate out 20GB LUNs to our volume groups. However the physical disks on the SAN are 72GB drives. With a bit of creative rounding we would say 4 x 20GB LUNs = approx 1 drive (give or take 8GB), and of course the allocated LUNs do not necessarily come from the same drive (which is a good thing).

Thus for a 500GB database I would create 7 volumes each at 80GB each. This gives MaxDB a nice distribution of volumes in which to creatively move the i/o load around, and also given that your 7 volumes are made up of lots of diffent drives on your SAN, your I/O performance should be pretty good.

For ease of management and given that you do reside on a SAN, I wouldn't necessarily allocate any more volumes after creating the "base" 7 ... rather just "replace" the ones you have with larger ones when you need to grow the database.

If you find that MaxDB is constantly moving I/O load away from one or two particular volumes you may wish to create additional volumes.

(This is a good indication of a hot spot on a specific drive/LUN on your SAN - and these days is pretty unlikely given that MaxDB will move the load to a volume with less I/O aka different disks)

If you using local disk, then one volume per drive is the general rule of thumb.

Cheers

Shaun

Message was edited by:

Shaun

[former_member229109]

Hello Ricardo,

Please go at :

http://dev.mysql.com/doc/maxdb/ -> MaxDB Library

-> Concepts of the Database System -> Creating and Configuring a Database Instance

-> Planning the Database Instance < & Technical Restrictions >

At the 'Planning the Database Instance' section in the paragraph 'Database Size and Configuration of Volumes' you will find answers on your questions.

Please also review the information at link - MaxDB FAQ (frequently asked questions) on the MySQL site (target audience: Open Source) :

http://dev.mysql.com/doc/maxdb/maxdb_faq.html

< '5.2. How do I configure the database files?' >

SAP customers can find more information in SAP Note 820824,

please review the answer in this note on the question :

"20. How large should I configure the data volumes of a MaxDB database? "

And if you are SAP customer please review the SAP notes for additional

information:

628131 SAP DB / MaxDB operating system parameters on Unix

767598 Available documentation

Thank you and best regards, Natalia Khlopina