GIRI MANDALIKA's SCRATCHPAD

Monday Aug 17, 2009

Oracle Business Intelligence on Sun : Few Best Practices

(Updated on 10/16/09 with additional content and restructured the blog entry for clarity and easy navigation)

The following suggested best practices are applicable to all Oracle BI EE deployments on Sun hardware (CMT and M-class) running Solaris 10 or later. These recommendations are based on our observations from the 50,000 user benchmark on Sun SPARC Enterprise T5440. It is not the complete list, and your mileage may vary.

Hardware : Firmware

Ensure that the system's firmware is up-to-date.

Sun System Firmware Release Hub

Solaris Recommendations

Upgrade to the latest update release of Solaris 10.
Solaris runs in 64-bit mode by default on SPARC platform. Consider running 64-bit BI EE on Solaris.
Enable 256M large pages on all nodes. By default, the latest update of Solaris 10 will use a maximum of 4M pages even when 256M pages are a good fit.
Increase the file descriptor limits by adding the following lines to /etc/system on all BI nodes.
On larger systems with more CPUs or CPU cores, try not to deploy Oracle BI EE in the global zone.
If the BI presentation catalog is stored on a local file system, create a ZFS file system to hold the catalog.

Store the Presentation Catalog on a disk with faster I/O such as a Solid State Drive (SSD). For uniform reads and writes across different disk drives [ and of course for better performance ], we recommend creating ZFS file system on top of a zpool with multiple SSDs.

Here is an example that shows the ZFS file system creation steps for the BI Presentation Catalog.


# zpool create -f BIshare c1t2d0s6 c1t3d0s0 c1t4d0s6 c1t5d0s6

# zpool list
NAME      SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
BIshare   118G    97K   118G     0%  ONLINE  -

# zfs create BIshare/WebCat

# fstyp /dev/dsk/c1t2d0s6
zfs

# zpool status -v
  pool: BIshare
 state: ONLINE
 scrub: none requested
config:

        NAME        STATE     READ WRITE CKSUM
        BIshare     ONLINE       0     0     0
          c1t2d0s6  ONLINE       0     0     0
          c1t3d0s0  ONLINE       0     0     0
          c1t4d0s6  ONLINE       0     0     0
          c1t5d0s6  ONLINE       0     0     0

errors: No known data errors

Observe the I/O activity on ZFS file system by running zpool iostat -v command.

Solaris : ZFS Recommendations

If the file system is mainly used for storing the Presentation Catalog, consider setting the ZFS record size to 8K. This is because of the relatively small size (8K or less) reads/writes from/into the BI Catalog.
eg.,
```
        # zfs set recordsize=8K BIshare/WebCat
```
In the case of database, you may have to set the ZFS record size to the database block size.
Even though disabling ZFS Intent Log (ZIL) may improve the performance of synchronous write operations, it is not a recommended practice to disable ZIL. Doing so may compromise the data integrity.
When running CPU intensive workloads, consider disabling the ZFS' metadata compression to provide more CPU cycles to the application.

Solaris : NFS Recommendations

One of the requirements of OBIEE is that the BI Presentation Catalog must be shared across different BI nodes in the BI Cluster. (There will be only one copy of the presentation catalog). Unless the catalog has been replicated on different nodes, there is no other choice but to share it across different nodes. One way to do this is to create an NFS share with the top level directory of the catalog, and then to mount it over NFS at the BI nodes.

Version 4 is the default NFS version on Solaris 10. However it appears that as of this writing, NFS v4 is not as mature as v3. So we recommend experimenting with both versions to see which one fits well to the needs of the BI deployment.

To enable NFS v3 on both server and the client, edit /etc/default/nfs and make the changes as shown below.

Experiment with the following NFS tunables.

NFS Server


NFSD_SERVERS=<desired_number> <-- on CMT systems with large number of hardware threads you can go as high as 512
NFS_SERVER_DELEGATION=[ON|OFF] <-- ON is the default. Experiment with OFF
NFSMAPID_DOMAIN=<network_domain_where_BI_was_deployed>

NFS Client

NFSMAPID_DOMAIN=<network_domain_where_BI_was_deployed>

Monitor the DNLC hit rate and tune the directory name look-up cache (DNLC).
Mounting NFS Share

Mount the NFS share that contains the Presentation Services Catalog on all the NFS clients (BI nodes in this context) using the following mount options:
```
        rw, forcedirectio, nocto
```

Oracle BI EE Cluster Deployment Recommendations

Ensure that all the BI components in the cluster are configured in a many-to-many fashion
For proper load balancing, configure all BI nodes to be identical in the BI Cluster
When planning to add an identically configured new node to the BI Cluster, simply clone an existing well-configured BI node running in a non-global zone.

Oracle BI Presentation Services Configuration Recommendations

Increase the file descriptors limit. Edit SAROOTDIR/setup/systunesrv.sh to increase the value from 1024 to any other value of your choice. In addition you must increase the shell limit using the ulimit -n command
eg.,
```
	ulimit -n 2048
```
Configure 256M large pages for the JVM heap of Chart server and OC4J web server (this recommendation is equally applicable to other web servers such as WebLogic or Sun Java system Web Server). Also use parallel GC, and restrict the number of parallel GC threads to 1/8th of the number of virtual cpus.
eg.,
```
	-XX:LargePageSizeInBytes=256M -XX:+UseParallelGC -XX:ParallelGCThreads=8
```
The Oracle BI Presentation Server keeps the access information of all the users in the Web Catalog. When there are large number of unique BI users, it can take a significant amount of time to look up a user if all the users reside in a single directory. To avoid this, hash the user directories. It can be achieved by having the following entry in SADATADIR/web/config/instanceconfig.xml
eg.,
```
	<Catalog>
		<HashUserHomeDirectories>2</HashUserHomeDirectories>
	</Catalog>
```
HashUserHomeDirectories specifies the number of characters to use to hash user names into sub directories. When this element is turned on, for example, the default name for user Steve's home directory would become /users/st/steve.
BI Server and BI Presentation Server processes create many temporary files while rendering reports and dashboards for a user. This can result in significant I/O activity on the system. The I/O waits can be minimized by pointing the temporary directories to a memory resident file system such as /tmp on Solaris OS. To achieve this, add the following line to the instanceconfig.xml configuration file.
eg.,
```
	<TempDir>/tmp/OracleBISAW</TempDir>
```
Similarly the Temporary directory (SATEMPDIR) can be pointed to a memory resident file system such as /tmp to minimize the I/O waits.
Consider tuning the value of CacheMaxEntries in instanceconfig.xml. A value of 20,000 was used in the 50,000 user OBIEE benchmark on T5440 servers. Be aware that the Presentation Services process (sawserver64) consumes more virtual memory when this parameter is set to a high value.
eg.,
```
	<CacheMaxEntries>20000</CacheMaxEntries>
```
If the presentation services log contains errors such as "The queue for the thread pool AsyncLogon is at it's maximum capacity of 50 jobs.", consider increasing the Presentation Services' asynchronous job queue. 50 is the default value.

The following example increases the job queue size to 200.
```
	<ThreadPoolDefaults>
		<AsyncLogon>
			<MaxQueue>200</MaxQueue>
		</AsyncLogon>
	</ThreadPoolDefaults>
```
Increase the query cache expiry time especially when the BI deployment is supposed to handle large number of concurrent users. The default is 60 minutes. However under very high loads, a cache entry may be removed before one hour if many queries are being run. Hence it is necessary to tune the parameter CacheMaxExpireMinutes in Presentation Services' instanceconfig.xml.

The following example increases the query cache expiry time to 3 hours.
```
	<CacheMaxExpireMinutes>180</CacheMaxExpireMinutes>
```

Consider increasing the Presentation Services' cache timeout values to keep the cached data intact for longer periods.

The following example increases the cache timeout values to 5 hours in instanceconfig.xml configuration file.


	<AccountIndexRefreshSecs>18000</AccountIndexRefreshSecs>
	<AccountCacheTimeoutSecs>18000</AccountCacheTimeoutSecs>
	<CacheTimeoutSecs>18000</CacheTimeoutSecs>
	<CacheCleanupSecs>18000</CacheCleanupSecs>
	<PrivilegeCacheTimeoutSecs>18000</PrivilegeCacheTimeoutSecs>

Oracle BI Server Configuration Recommendations

Enable caching at the BI server and control/tune the cache expiry time for each of the table based on your organizations' needs.
Unless the repository needs to be edited online frequently, consider setting up the "read only" mode for the repository. It may ease lock contention up to some extent.

Increase the session limit and the number of requests per session limit especially when the BI deployment is expected to handle large number of concurrent users. Also increase the number of BI server threads.

The following configuration was used in 50,000 user OBIEE benchmark on T5440 servers.


(Source configuration file: NQSConfig,.INI)

[ CACHE ]
ENABLE = YES;
DATA_STORAGE_PATHS = "/export/oracle/OracleBIData/cache" 500 MB;
MAX_ROWS_PER_CACHE_ENTRY = 0;
MAX_CACHE_ENTRY_SIZE = 10 MB;
MAX_CACHE_ENTRIES = 5000;
POPULATE_AGGREGATE_ROLLUP_HITS = NO;
USE_ADVANCED_HIT_DETECTION = NO;

// Cluster-aware cache
GLOBAL_CACHE_STORAGE_PATH = "/export/oracle/OracleBIsharedRepository/GlobalCacheDirectory" 2048 MB;
MAX_GLOBAL_CACHE_ENTRIES = 10000;
CACHE_POLL_SECONDS = 300;
CLUSTER_AWARE_CACHE_LOGGING = NO;

[ SERVER ]
READ_ONLY_MODE = YES;
MAX_SESSION_LIMIT = 20000 ;
MAX_REQUEST_PER_SESSION_LIMIT = 1500 ;
SERVER_THREAD_RANGE = 512-2048;
SERVER_THREAD_STACK_SIZE = 0;
DB_GATEWAY_THREAD_RANGE = 512-512;

#SERVER_HOSTNAME_OR_IP_ADDRESSES = "ALLNICS";
CLUSTER_PARTICIPANT = YES;

Related Blog Posts

Posted at 04:35PM Aug 17, 2009 by Giri Mandalika in Performance | Comments[0]

Tuesday Jan 27, 2009

PHP: Memory savings with mysqlnd

mysqlnd may save memory. In the best cases, it may consume only 50% memory as that of libmysql esp. when the client application does not modify the data in the result set after executing a query. Keep in mind that the client must use ext/mysqli and treat the data returned by the query as read-only in order to fully realize mysqlnd's memory gains. If the client application modifies any of the data, mysqlnd behaves just like libmysql.

Let's have a quick look at the memory consumption in both the cases (mysqlnd and libmysql) with an example before delving into the internals. For easy comparison, the sample PHP script does not modify any of the arrays returned from the fetch method. The following example uses DTrace on Sun Solaris to monitor the calls to malloc() and prints the requested bytes of memory on the standard output.

Source code for the script: PHPmysqliClient.php. MySQL table structure and the sample data are shown in the other blog post: Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd.


bash# cat monitormalloc.d

#!/usr/sbin/dtrace -s

pid$1:libc:malloc:entry
{
 	printf("\t\tSize : %d Bytes", arg0);
	ustack();
	@malloc[probefunc] = quantize(arg0);
}

CASE 1: ext/mysqli with libmysql

In one terminal window:

bash# /opt/coolstack/php5/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU	ID			FUNCTION:NAME
  0  80920			malloc:entry		Size : 964 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23bb47
              mysqli.so`0xce11d292

  0  80920			malloc:entry		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23da60
              mysqli.so`0xce11dc72

  0  80920			malloc:entry		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21d991
              libmysqlclient.so.16.0.0`0xce21d9ce
              libmysqlclient.so.16.0.0`0xce23da72
              mysqli.so`0xce11dc72

  0  80920			malloc:entry		Size : 17 Bytes
		... elided stack traces for brevity ...
  0  80920			malloc:entry		Size : 152 Bytes
  0  80920			malloc:entry		Size : 16384 Bytes
  0  80920			malloc:entry		Size : 8199 Bytes
  0  80920			malloc:entry		Size : 7 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 18261 Bytes
  0  80920			malloc:entry		Size : 58 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 120 Bytes
  0  80920			malloc:entry		Size : 5 Bytes
  0  80920			malloc:entry		Size : 6 Bytes
  0  80920			malloc:entry		Size : 5 Bytes
  0  80920			malloc:entry		Size : 56 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
  0  80920			malloc:entry		Size : 92 Bytes
  0  80920			malloc:entry		Size : 56 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21a27b
              libmysqlclient.so.16.0.0`0xce23b8a4
              libmysqlclient.so.16.0.0`0xce23d4fa
              mysqli.so`0xce11fe56

  0  80920			malloc:entry		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x856fb98

^C

  malloc		
           value  ------------- Distribution ------------- count
               2 |                                         0
               4 |@@@@@                                    4
               8 |                                         0
              16 |@@@@                                     3
              32 |@@@@                                     3
              64 |@@@                                      2
             128 |@                                        1
             256 |                                         0
             512 |@                                        1
            1024 |                                         0
            2048 |@@@@@@@@@@@@@@			   11
            4096 |@@@@                                     3
            8192 |@                                        1
           16384 |@@@                                      2
           32768 |                                         0
           65536 |                                         0
          131072 |                                         0
          262144 |@                                        1
          524288 |                                         0

CASE 2: ext/mysqli with mysqlnd

In one terminal window:

bash# /export/home/php53/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU	ID			FUNCTION:NAME
  0  80920			malloc:entry		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x82f702b
              php`0x82f80ab
              php`0x82f841f
              php`0x82f98c4
              php`0x82c7668
              php`0x83c30ae
              php`0x80c059c

^C

  malloc		
           value  ------------- Distribution ------------- count
          131072 |                                         0
          262144 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1
          524288 |                                         0

In the case of ext/mysqli with libmysql, there are more than 25 calls to malloc() accounting to a total size around 367KB, where as in the case of ext/mysqli with mysqlnd, there is only one call to malloc() with a size of 256KB. In other words, mysqlnd is consuming 30% less memory relative to libmysql to do similar database operations (in reality, it is incorrect to treat every byte allocated as the memory consumed unless there exists a corresponding memory mapping -- however for the sake of this discussion, let's just assume that all the allocated bytes are eventually consumed).

The memory savings in the above example are the result of mysqlnd's ability to hold the results only once in the memory. On the other hand, as libmysql is not a part of PHP, some of the results fetched by libmysql will be copied into memory twice. When libmysql fetches the data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into respective ext/mysqli data structures (often referred as zvals) before it is made available to the PHP clients to consume. So with ext/mysqli and libmysql, there might be two copies of the data in the main memory - one copy inside the libmysql buffers and the other inside zvals. With mysqlnd, there might be only one copy of the data in the memory. mysqlnd also uses buffers but links the zval structures directly to the read buffers, wherever possible. Therefore in majority of the instances, mysqlnd consumes less memory relative to libmysql. In the worst case, it may consume as much memory as that of libmysql. The total memory savings depend on the size of the buffered result set.

Shown below is the simplified behind-the-scenes actions of ext/mysqli with libmysql and ext/mysqli with mysqlnd when mysqli sends a query:

ext/mysqli with libmysql

mysqli sends a query
result set gets fetched into libmysql buffers
mysqli allocates zvals, then new buffers
mysqli copies data from libmysql to its own buffers
mysqli calls mysql_free_result() and deallocates libmysql buffers

ext/mysqli with mysqlnd

mysqli sends a query
result set gets fetched row by row -- every row is a different buffer
mysqlnd creates a result set of zvals pointing to the buffers
mysqli calls mysqlnd_free_result() and deallocates the row buffers

In short, ext/mysqli with libmysql does:

one extra allocation for mysqli buffers
one extra data copy
one extra zval allocation (which can be saved with the zval cache)

when compared to ext/mysqli with mysqlnd.