PerformanceWiki [the blog]

How to bind DB2 database manager to cores?

January 25, 2010 – 2:52 pm
Step 1. Set DB2 registry variable as follows:        DB2_RESOURCE_POLICY=/db2home/inst1/mydir/bindproc.txtStep 2: make sure the bindproc.txt has the following entry:        (the following example binds two cores to the process)<RESOURCE_POLICY>                <GLOBAL_RESOURCE_POLICY>                        <METHOD>CPU</METHOD>                        <RESOURCE_BINDING>                                <RESOURCE>1</RESOURCE>                        </RESOURCE_BINDING>                        <RESOURCE_BINDING>                                <RESOURCE>2</RESOURCE>                        </RESOURCE_BINDING>                </GLOBAL_RESOURCE_POLICY></RESOURCE_POLICY>.pw.

Posted in Uncategorized | No Comments »

DB2 Buffer Pool Utilization

January 21, 2010 – 11:09 am
Buffer pool utilizationDavid J. Kline, and Gabor Wieser from IBM describes, in very simple terms, how bufferpools in DB2 should be designed. In their article on developerWorks, they described the following:The most important reason to use more than one user table space is to manage buffer utilization. A table space can only be associated with one buffer pool, though one buffer pool can be used for more than one table space. The goal of buffer pool tuning is to help DB2 make the best possible use of the memory available for buffers. The overall buffer size has a great effect on DB2 performance, since a large number of pages can significantly reduce I/O, which is the most time consuming operation. However, if the total buffer size is too large, and there is not enough storage to allocate them, then a minimum buffer pool for each page size will be allocated, and performance will be sharply reduced. To calculate the maximum buffer size, ...

Posted in Uncategorized | No Comments »

Performance with IBM DB2 on NetApp FAS and IBM Storage with Linux

January 12, 2010 – 12:24 pm
IBM DB2 9.5 Performance and Scalability on RHEL5 with NFS and FCP Using NetApp FAS or IBM N series Storage Systemhttp://media.netapp.com/documents/tr-3775.pdfTopics include: HARDWARE AND SOFTWARE SETTINGS SERVER  STORAGE  LINUX KERNEL PARAMETERCONFIGURATION AND TUNING LINUX NFS CONFIGURATION AND TUNING LINUX FCP CONFIGURATION AND TUNING DB2 CONFIGURATIONDB2 PERFORMANCE AND SCALABILITY OVER NFS AND FCP  LINUX NFS AND FCP TUNING EFFECTS  LINUX NFS AND FCP PERFORMANCE COMPARISON SCALABILITY TEST .pw.

Posted in Uncategorized | No Comments »

Scale large OLTP workload with DB2 9 for System z on Linux

January 12, 2010 – 12:18 pm
Scale large OLTP workload with DB2 9 for System z on LinuxSimple and effective tuning paper (about 9 pages) by IBMhttp://download.boulder.ibm.com/ibmdl/pub/software/dw/linux390/perf/ZSW03028-USEN-00.pdfkey topics include:Environmental SetupServer softwareLinux Kernel tunablesI/O schedulerDatabase ImplementationAsynchronous I/ODirect I/OWorkload DescriptionResults.pw.

Posted in Uncategorized | No Comments »

Tuning and Monitoring Database System Performance UNIX Windows Linux

January 12, 2010 – 12:12 pm
Database System Performance Best Pract= ices on UNIX, Windows and LinuxThe paper published by IBM and found he= re http://www.ibm.com/developerworks/data/bestpractices/systemperformance/?S= _TACT=3D105AGX11&S_CMP=3DLP. The key area covered an= d are at the heart of performance tuning include the following:= The first step: configuring for good performanceHardware = configurationAIX configurationSolaris and HP-UX configura= tionLinux configurationDB2 Data Partitioning Feature= Choice of code page and collationPhysical database designInitial DB2 configuration settingsDB2 autonomics a= nd automatic parametersExplicit configuration settingsSta= tistics collectionConsiderations for SAP and other ISV environment= sThe next step: monitoring system performanceA g= ood 'starter set' of DB2 snapshot metricsOther important data to c= ollectCross-partition monitoring in partitioned database environme= nts=0A Performance Tuning and TroubleshootingTypes of problems that you might seeDisk bottlenecksCPU bottlenecksMemory bottlenecks'Lazy System' bot= tlenecksSystem bottlenecks =E2=80=94 The overall picture= Localized and system-wide troubleshootingBest practices.pw.=0A=0A

Posted in Uncategorized | No Comments »

Database Vendor Performance Benchmark Links

January 12, 2010 – 11:31 am
The following are performance benchmark Web pages from major DBMS and hardware vendors:Microsoft SQL Server 2008. Example of performance highlight and resource page http://www.microsoft.com/sqlserver/2008/en/us/performance-scale.aspx   (includes overview, plus links to benchmark results, papers, and more)Oracle. Benchmark result pageshttp://www.oracle.com/solutions/performance_scalability/index.htmlMicrosoft SQL Server 2008. Benchmark result pages http://www.microsoft.com/sqlserver/2008/en/us/benchmarks.aspxMicrosoft SQL Server 2005. Benchmark result pages http://www.microsoft.com/sqlserver/2005/en/us/benchmarks.aspxMicrosoft SQL Server 2003. Benchmark result pages http://www.microsoft.com/windowsserver2003/evaluation/performance/benchmarks/default.mspxHardware vendors IBM xSeries Benchmark Newshttp://www-03.ibm.com/systems/x/resources/benchmarks/intel/news/index.htmlIBM Power Systems performance benchmarkhttp://www-03.ibm.com/systems/power/hardware/benchmarks/index.htmlHP - http://h71028.www7.hp.com/integrity/cache/393900-0-0-0-121.html.pw.

Posted in Uncategorized | No Comments »

SQL Server 2008 Tunings

December 10, 2009 – 12:44 pm
SQL Server 2008 TuningFor an OLTP workload, start with the following advanced SQL Server 2008tuning recommendations. Some are from Microsoft, others are just good practices. Run these as queries in the Microsoft SQL Server Management Studio UI:USE masterEXEC sp_configure 'show advanced options', 1RECONFIGURE WITH OVERRIDE(the above must be run first before any advanced tuning parameters can be set)sp_configure 'awe enabled', 1goRECONFIGUREgosp_configure 'cost threshold for parallelism', 5goRECONFIGUREgosp_configure 'cost threshold for parallelism', 5goRECONFIGUREgosp_configure 'fill factor', 80goRECONFIGUREgosp_configure 'index create memory', 1000goRECONFIGUREgosp_configure 'lightweight pooling', 1goRECONFIGUREgosp_configure 'locks', 10000000 /* 10 million */goRECONFIGUREgosp_configure 'max degree of parallelism', 16 /* we have 16 lcpu */goRECONFIGUREgosp_configure 'min server memory', 1000goRECONFIGUREgosp_configure 'max worker threads', 704goRECONFIGUREgosp_configure 'min memory per query', 2048goRECONFIGUREgosp_configure 'network packet size', 4096 /* to match jdbc settings */goRECONFIGUREgosp_configure 'priority boost', 1goRECONFIGUREgosp_configure 'user connections', 1000goRECONFIGUREgo.pw.

Posted in Uncategorized | No Comments »

DB2 SQL5043N Error on start

December 9, 2009 – 2:00 pm
bash-3.00# db2start05/15/2007 15:01:59     0   0   SQL5043N  Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.SQL1063N  DB2START processing was successful.look into db2diag.log:2007-05-15-15.01.58.846394-420 E92034A447         LEVEL: ErrorPID     : 405708               TID  : 1           PROC : db2sysc 0INSTANCE: db2inst1             NODE : 000FUNCTION: DB2 UDB, common communication, sqlcctcpconnmgr, probe:50MESSAGE : ADM7006E  The SVCENAME DBM configuration parameter was not          configured.  Update the SVCENAME configuration parameter using the          service name defined in the TCP/IP services file.2007-05-15-15.01.59.901248-420 E92482A1028        LEVEL: EventPID     : 397514               TID  : 1           PROC : db2star2INSTANCE: db2inst1             NODE : 000FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:911MESSAGE : ADM7513W  Database manager has started.START   : DB2 DBMDATA #1 : Build Level, 152 bytesInstance "db2inst1" uses "64" bits and DB2 code release "SQL09012"with level identifier "01030107".Informational tokens are "DB2 v9.1.0.2", "s070210", "U810940", Fix Pack "2".DATA #2 : System Info, 224 bytesSystem: AIX fnl70 3 5 00CE3A8A4C00The error is caused by ...

Posted in Uncategorized | No Comments »

Boost performance by 60% with WebSphere Servlet Caching

December 7, 2009 – 11:55 am
An excellent tuning paper on IBM's developerWo= rks on the topic of Servlet Caching:Servlet caching=0A=0A WebSphere Application Server=E2=80=99s DynaCache pro= vides a general in-memory=0A caching service for objects and= page fragments generated by the server.=0A The DistributedMap and DistributedObjectCache interfaces can be used=0A = within an application to cache and share Java objects by storing=0A = references to these objects in the cache for later use. Servl= et caching,=0A ...

Posted in Uncategorized | No Comments »

High performance message-driven beans

December 2, 2009 – 1:21 pm
An article on IBM's developerWorks web sites shows some tips on tuningmessage-driven beans. In particular, a simple math model was given as follows:"We can build a simple mathematical model of the behaviour of concurrent MDB usage: If we expect a transaction under normal circumstances to take t seconds to complete, and we have a target throughput requirement of n transactions per second, then we will need nt concurrent threads to achieve our target. For example, if the transaction will take 0.1s, and we need 100 transactions per second, then we need 0.1 * 100 = 10 concurrent threads. This suggests a value for the Maximum Sessions setting and, in turn, the size of the session pool associated with the listener port's connection."Which would suggest to tune the max connection found under "Resources > JMS > Queue connection factories > queue_connection_factory > [Additional Properties] Connection pool" to be the above calculated value.Of course, a lot of times, ...

Posted in Uncategorized | No Comments »