 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / June 2005How large of a bufferpool can you create?
Folks, I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create? I tried to create a 4GB bufferpool db2 complained that is cannot allocate enogth memory. I have 16GB on this system. # db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096 SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory. SQLSTATE=01657 when I re-start database I get following error: # db2start 06/28/2005 16:50:18 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. [[dbap3@lidp4]]/g6/dbap3> # db2 connect to mydb Database Connection Information Database server = DB2/6000 8.2.1 SQL authorization ID = DBAP3 Local database alias = MYDB SQL1478W The defined buffer pools could not be started. Instead, one small buffer pool for each page size supported by DB2 has been started. SQLSTATE=01626 I tried to to create 2GB bufferpool and that failed too. # db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096  SignatureHemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah@xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own. > Folks, > [quoted text clipped - 28 lines] > > # db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096 Is this a 32bit or 64bit version of DB2? Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > > Folks, > > [quoted text clipped - 6 lines] > > SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until > > the next database startup due to insufficient memory. SQLSTATE=01657 This warning occurs only when the the difference between the old/new bufferpool sizes is too large, and there is not enough continguous memory in the system to expand some control structures. A restart is required in this case. > > when I re-start database I get following error: > > [quoted text clipped - 19 lines] > > > Is this a 32bit or 64bit version of DB2? The 32-bit version of DB2 can only access a maximum of 2GB of memory. This limits your bufferpools to around 1.7GB due to the overhead of other memory sets and limitations in the shared memory infrastructure on 32-bit AIX. The 64-bit version of DB2 can access large amounts of memory without problems -- IBM has published benchmarks in the past year that used close to 1TB of memory. -- Matt Emmerton > The 32-bit version of DB2 can only access a maximum of 2GB of memory. This > limits your bufferpools to around 1.7GB due to the overhead of other memory > sets and limitations in the shared memory infrastructure on 32-bit AIX. Really? I thought the max for 32bit was 4GB and due to those same limitations in AIX you would be able to address about 3.2GB on this system. Or am I in error here? I'm not really an AIX man, I use X86, but the difference between those systems isn't 2GB of adressable memory, is it? > The 64-bit version of DB2 can access large amounts of memory without > problems -- IBM has published benchmarks in the past year that used close to > 1TB of memory. What was it, 24GB per Bufferpool on a 64bit system? > -- > Matt Emmerton -R- > Really? I thought the max for 32bit was 4GB and due to those same > limitations in AIX you would be able to address about 3.2GB on this > system. Or am I in error here? I'm not really an AIX man, I use X86, but > the difference between those systems isn't 2GB of adressable memory, is > it? It is a DB2 limitation, combined with an OS limitation, that limites the amount of usable DB2 memory per instance to about 1.75 GB. One could always create multiple instances if you had multiple databases, to get around this limitation to some degree. > What was it, 24GB per Bufferpool on a 64bit system? > > -R- If DB2 was using close to 1TB of memory total on a 64 bit machine, the amount of bufferpool memory used would need to be close to 1TB, since bufferpools will using the vast majority of the memory in DB2 with such a configuration. > If DB2 was using close to 1TB of memory total on a 64 bit machine, the > amount of bufferpool memory used would need to be close to 1TB, since > bufferpools will using the vast majority of the memory in DB2 with such a > configuration. Aha, so a single bufferpool can grow up to 1TB on a 64bit system? During the performance tuning course I took at IBM NL, I raised the question about bufferpool sizes on 64bit systems, and I was told the maximum size per bufferpool was 24GB. Was I misinformed? -R- >> If DB2 was using close to 1TB of memory total on a 64 bit machine, the >> amount of bufferpool memory used would need to be close to 1TB, since [quoted text clipped - 5 lines] > question about bufferpool sizes on 64bit systems, and I was told the > maximum size per bufferpool was 24GB. Was I misinformed? Have a look at the SQL limits in the manual: a buffer pool can have up to 2.14 billion pages in a 64bit instance, which is about 8TB for 4K page size. SignatureKnut Stolze Information Integration Development IBM Germany / University of Jena >> Folks, >> [quoted text clipped - 30 lines] >> > Is this a 32bit or 64bit version of DB2? The AIX is 64-bit # lslpp -h bos.64bit Fileset Level Action Status Date Time ---------------------------------------------------------------------------- Path: /usr/lib/objrepos bos.64bit 5.1.0.35 COMMIT COMPLETE 12/12/02 04:43:14 5.1.0.37 COMMIT COMPLETE 12/12/02 04:47:22 5.1.0.52 COMMIT COMPLETE 02/05/04 17:48:09 Path: /etc/objrepos bos.64bit 5.1.0.35 COMMIT COMPLETE 12/12/02 04:43:15 How do I find out if db2 is 64-bit? I cannot figure out based on lslpp output. > Cheers > Serge SignatureHemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah@xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own. > The AIX is 64-bit > > # lslpp -h bos.64bit This just tells you if the 64-bit kernel is installed on your server, but not if it is actually running. You need to run the command 'bootinfo -K' to determine if the 64-bit kernel is running (and this command requires root privileges). Unless you AIX admin specifically enabled the 64-bit kernel, chances are you are running the 32-bit kernel (i.e. that's the default out of the box). >> The AIX is 64-bit >> [quoted text clipped - 9 lines] > you are running the 32-bit kernel (i.e. that's the default out of the > box). Thanks for the info. It is running in 32-bit mode. I will have to change it to make it run in 64-bit mode. If my applications have problems with 64-bit, can I switch it back to 32-bit? SignatureHemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah@xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own. > Folks, > [quoted text clipped - 33 lines] > # db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096 > Hemant Shah /"\ ASCII ribbon campaign If you created a 32 bit instance, then the total DB2 memory allocation for the instance can be no more than about 1.75GB under AIX (for all DB2 memory requirements including the bufferpools). So the amount available for bufferpools is probably closer to 1.25 GB, depending on the parms you configured in the db and dbm configuration parms. This amount varies by OS (more for Solaris and Windows, less for HP/UX and Linux). If you have a 64 bit instance, the maximum amount of DB2 memory allocation for the instance is far beyond the amount of memory in any computer that I know about. However, make sure you have enough real memory on the server for the total amount you specify in the bufferpools, also allowing for other DB2 memory requirements. In order to create a 64-bit DB2 instance (or convert a 32 bit instance to 64 bit) you must have a 64 bit OS running on 64 bit hardware. Check with your AIX system administrator about 64 bit AIX. > In order to create a 64-bit DB2 instance (or convert a 32 bit instance to 64 > bit) you must have a 64 bit OS running on 64 bit hardware. Check with your > AIX system administrator about 64 bit AIX. Minor nit: you can create a 64-bit instance on a 32-bit aix kernel as long as the hardware supports 64-bit applications. Ideally, you'd have the 64-bit kernal installed, but the performance impact is supposed to be small. buck >> In order to create a 64-bit DB2 instance (or convert a 32 bit instance to 64 >> bit) you must have a 64 bit OS running on 64 bit hardware. Check with your [quoted text clipped - 4 lines] > the 64-bit kernal installed, but the performance impact is supposed to > be small. It is a 64-bit system it a P-630. How do I find out if the kernel, and db2 install is 64-bit. > buck SignatureHemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah@xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own. im not familiar with aix...check this command to verify if the DB2 is 32 or 64-bit $db2level you see a 64-bit in the message... for the memory...configure the shared memory (SHMMAX) for the AIX kernel -,look for the the documentation ^_^...check if its greater your requested memory... Cheers, Cheers > im not familiar with aix...check this command to verify if the DB2 is > 32 or 64-bit [quoted text clipped - 8 lines] > > Cheers, Just to clarify the above, logon as the instance owner, and type db2level. That will tell you how many bits that instance has. If you have other instances, repeat for each instance owner. You don't need to re-install DB2 to create a 64 instance, or to upgrade an existing 32 bit instance to 64 bit. >> im not familiar with aix...check this command to verify if the DB2 is >> 32 or 64-bit [quoted text clipped - 15 lines] > You don't need to re-install DB2 to create a 64 instance, or to upgrade an > existing 32 bit instance to 64 bit. It is 32-bit install of DB2. How do I upgrade it to 64-bit? I have a CD with both versions. SignatureHemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah@xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own. i haven't tried updating instances from 32-64...but try to read this site... http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.ud b.doc/core/r0002060.htm check for this commands that are helpful for migration - db2icrt, db2level, db2iupdt when creating instance using db2icrt u need to specify the -w option (width in bits e.g. 31, 32, 64)... as far as i can remember to a create 64bit instance a 64 bit code of db2 must be installed (please verify this to the mentioned site)... Since I haven't done this procedure (converting 32-64) i am not sure if this procedure works...you may try this procedure if agree with it and make sure to backup your databases and configuration...i strongly suggest you test it on different aix machine(if you have)... 1. Install 64bit DB2 (if not yet installed) 2. Issue db2ipudt on the instance specifying the -w 64 option to make it 64 bit stop db2 instances before doing the procedure 1. Install 64bit DB2 (if not yet installed) 2. Issue db2ipudt on the instance specifying the -w 64 option to make it 64 bit then start db2 instances > stop db2 instances before doing the procedure > > 1. Install 64bit DB2 (if not yet installed) > 2. Issue db2ipudt on the instance specifying the -w 64 option to make > it 64 bit I just did this with a few of our instances on an AIX 5.2 box and it was really easy. SignatureKnut Stolze Information Integration Development IBM Germany / University of Jena >> stop db2 instances before doing the procedure >> [quoted text clipped - 4 lines] > I just did this with a few of our instances on an AIX 5.2 box and it was > really easy. Thanks (to all) for the info, I will give it a try. SignatureHemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah@xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own. "DB2 Guy" <manny_er@hotmail.com> wrote in message > as far as i can remember to a create 64bit instance a 64 bit code of > db2 must be installed (please verify this to the mentioned site)... Some distributions of DB2 have one code base for both 32-bit and 64-bit installations. This includes DB2 for AIX 5L. Other versions of DB2 which have one distribution for both 32-bit and 64-bit include: - DB2 for HP/UX for 11i PA RISC, HP 9000 Servers - DB2 for Linux on AMD64 and Intel EM64T - DB2 for Linux iSeries and pSeries - DB2 for Solaris |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.