Webi 4.1 Scaling-Out with HANA
Looking for a successful BI deployment with SAP BI WebIntelligence 4.1 running on SAP HANA ? Here is how to !
A recent load test proves Webi’s linear scalability on HANA as reporting data source. This paper describes the testing methods and the results. Tuning tips and best practice are summarized at the end.
- Testing Methods
- Applied Parameters
- JDBC or ODBC for HANA ?
- Best Practices
1. Testing Methods
Table 1. TPC-H DB
- Xeon Processor E5-2620 @2.00GHz 2 Processors x 6 Cores, 24 threads,
- Xeon Processor E7-4870 @ 2.40GHz 4 Processors x 10 Cores, 80 threads,
WebI on HANA scales in a linear manner when increasing number of users. On the first BI4 node we started from running 100 concurrent users, then we increased the concurrency gradually up to 800 users with which the response time started to slow down. Therefore we defined 700 concurrent users as optimal load on the first node when the CPU load was about 80% of the box. Then we added a second BI4 box by copying the parameters from the first one. Webi then succeeded in doubling in scalability by reaching 1400 concurrent users by always keeping the same performance tendency. CPU load was also doubled reaching about 160% of the two BI4 servers in total.
The figure2 shows Webi’s linear scalability : the more the concurrency increases the more the throughput increases in the same manner. Webi Processing Server process CPU is efficiently used naturally in the same way. This tendency should naturally be further extensible by adding hardware resources. That means Webi will support 2100 users on 3 BI4 boxes, 2800 users on 4 .. by keeping the same performance.
Figure 2. CPU load and Throughput
This linear scalability is proved by the constant response time from 100 concurrent users up to 1400 concurrent users. Webi offers good response time without degradation. Table 2. shows the average response time per step.
Table2. Respnse time (in second)
Below is the CPU and memory usage of each machine.700 users on one BI4 node, 1400 users on two nodes.The CPU usage of each layer is naturally doubled when Webi doubles the number of its supporting users.
See also section 2.Landscape. BI4 and CMSDB (24 cpu-cores, 64GB RAM), HANA server is larger (40 cpu-cores, 512GB RAM).
Figure 3. Average CPU usage in 100% scale. Som of all the instances
Memory usage in terms of Linux virtual size. Some are not x 2 between 700u and 1400u. Tomcat had 13GB max heap for 700u while 8GB (x2 instances) for 1400u. Webi 1400u tripled the size of 700u while both tests had same thresholds definition. Sybase ASE is in Windows Working set. HANA is the Index Server heap used memory size.
Figure 4. Memory usage
4. Applied Parameters
A. Sybase ASE
- DB options
- Page Size 4KB (need defining at first DB creation)
- Procedure Cache size 27680
- Data Cache (buffer pool) max memory 500 MB
- Lock 100000
- Number of engines* 16 < 700 VUs, 20 for 1400 VUs (on 24 (cpu-cores) max available)
- Number of connections 100 each (remote connections/logins/user connections)
- Number of tablespaces ‘number of devices’ = 25
- Number of open objects/indexes/partitions = 2000, 4000, 3000
Setting 24 let Sybase load CPU at 100% permanently and was less efficient for the resulted performance. Monitor well to identify tuning point. See also Sybase ASE product manual.
Max heap 8GBn MaxThread 1800, -Xmn3GB
While Sybase was not well tuned CMS opened larger number of connections to Sybase therefore we increased the number of CMSDB connections to 50 (i.e. max allowed) to work this around. Once Sybase is well tuned CMS did not need higher number than the default one
Create a dedicated APS process to DSLBridge service with least necessary services. Max heap 6GB.
Figure 5. List of services for DSLBridge-dedicated APS
MaxConnections 700 (default 200). Memory threadhold 8/9/10GB (Lower / Upper / Max).
F. HANA via JDBC driver
When the connectivity of HANA data source is jdbc, you may need to increase Connection Server component's max heap size (1GB by default). Edit JAVA VM section of the cs.cfg file located below :
[on Windows] %BI4Install_Dir%SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer
[on unix] $BI4Install_Dir$sap_bobj/enterprise_xi40/dataAccess/connectionServer
Once modified, save the file and restart Webi servers to make the change takes effect.
If the max heap is not high enough JVM will throw 'out of memory' error (like whatever Java programs) and can provoke Webi Server crash. In our test, for 1400 concurrent users we increased it to 2GB. Make sure to allocate Webi memory threshold high enough to cover this heap inside Webi process.
Let's do not mix Connection Server here with the ConnectionServer or ConnectionServer32 processes. This Connection Server here is a part of the Webi Server components thus running inside Webi Processing Server process.
Figure 6. cs.cfg file
Default on a HANA-compliant hardware.
HANA Index server resultcache_enabled (Yes or No) parameter did not make visible difference in response time. Data volume might not be high enough
- Auditing disabled (Set Events Off in CMC Auditing page)
- Platform Search indexing disabled (in CMC Application page - Platform Search Application)
- In this test CVOM service in APS was not deployed because Webi reports do not have charts that require it
5. JDBC or ODBC for HANA ?
A supplementary test shows an advantage with ODBC in terms of response time with the Refresh document action. Both Webi and HANA are C++ programs. JDBC driver between Webi and HANA involves a java – C++ transformation that adds slight overhead. The gain is more eminent with 100 concurrent users (max 12% gain) than a single user (5% to 8% gain) because Webi server is a multi-threaded program. When it is running on a big server like ours (24-core) the processing efficiency may be more demonstrative with multi users (to be further studied).
Figure 7. ODBC vs JDBC response time comparison by 4 Webi reports of different data size (in second)
6. Best Practices
Up to now is a specific story of our particular test case. Now let us summarize what we learned into more generic Best Practice. But first thing we remind you
is the importance of ‘monitoring’. Monitoring of all the components and all the machines’ system resource usage. Without knowing how each component is behaving you won’t find what to tune.
A. Fine tune CMS database for facilitating smooth CMS operation
If BI Platform is not in a healthy state no frontend-level application servers like Webi will run fine on it, so this is essential.
Sybase ASE requires tuning with a number of parameters to optimize CMS. It is obvious but make sure to get a good license that allows utilizing all available CPU
that you have. Like whatever products. The one we initially got allowed only 1 CPU !
Sybase Anywhere shipped in the BI4 package may not be suitable for a large-size deployment because its administrative tools are not in the package. One of the advantages of Sybase Anywhere is its out-of-the box, quick BI4 deployment for a first use as well as testing before going into a production environment. Needless to say Sybase Anwhere standard version is destinated for an enterprise deployement.
Each database has its own characteristics. Each customer has its own data volume and usage so it is not easy to define a single tip applicable to everyone.
Moreover when Auditing is enabled on the same DB the load must be much heavier. You may often need a DB administrator's experties to fine tune your DB.
In general the healthy state of CMS is when its Currently used system DB connections metric is quite small such as between 0 and 3 (out of 14 by default) on CMC because the healthy quering is too fast so that the open-state connections to be visible. If you see more connections there the quering should be slower and some may be queueing on DB. It is not a good sign if all the connections are opened and need to be increased. In such cases check the DB side for more tuning. Another typical sign is a slower response time of BI Platform-related steps such as login, logout, navigation to Coporate folders. If that is the case you can also doubt a bottleneck around there. Lastly we recommend also to monitor the number of objects as well as number of objects in cache you can monitor CMS health on CMC metrics.
B. Fine tune Tomcat (or whatever WebiApp Server) JVM heap size, maxThreads, GC options
Monitoring tool : e.g. JConsole, GCViewer
C. Fine tune Webi
Thanks to a 64-bit Webi Server, Webi's scalability has been significantly extended on BI4 from XIR3. The most efficient way is to scale Webi is to drive a single Webi instance's capacity to a maximum optimal size and run smaller number of instances rather than multiplying a default Webi blindly. An old-time recommendation was one Webi instance per CPU but forget it, this is no longer the case .
You can size Webi mainly from 2 aspects : number of connections and memory thresholds. For the former one, you can start with roughly the number of active concurrent users by supposing one user opens one Webi document. Note that Webi needs 2 connections for a particular user opening 2 documents. Monitor the actual usage by the Current number of active sessions metric on CMC so that the actual level is below the value you set. Although the highest limit of the parameter is 65535 we would recommend it between 300 and 400 per instance. For the latter one, start the thresholds by default and monitor the actual memory level by the Virtual Memory size metric on CMC to see if you need to increase. Make sure that the sum of the Max Threshold of all the instances (not only Webi but all the running processes) wont go over than the machine's phisycal memory capacity.
As far as Webi scales fine without degrading in reponse time with the above setting the tuning is done. Otherwise adjust the parameters to see if the response time improves. If your load is far over the capacity of one instance then reduce the concurrent users per instance by dispatching the load onto two, then three.. instances. Whichever the boxes, either on a single node or on separate ones, Webi scales in the same manner, i.e. when 1 Webi = 100 users 4 Webis = 400 users. While increasing the concurrency of Webi you may need to go back to tune others such as WebApp Server, DSLBridge, CMS/CMSDB or OS level.
D. Fine tune BI4 Java programs (such as DSL Bridge, CVOM, Webi’s JDBC connectivity layer ...) JVM Heap size, maxThreads, GC options
Monitoring tool : e.g. JConsole, GCViewer
The role of DSLBridge for a BI4 universe (.UNX) is simply to generate SQL except when the data source is SAP BW. For such a usage DSLBridge does not require as large heap size as for SAP BW. The best way to identify an optimum heap size is to monitor the heap usage
E. When HANA connectivity is JDBC fine tune Webi Server's Connection Server component JVM
See also the section 4-F above for how to configure.
To monitor Connection Server running in Webi process use PID of Webi. As of BI4.1 SP3 HANA JDBC is the only jdbc connectivities that Webi supports
F. Version of HANA and BI4 <= newer is better
Follow HANA revision to the corresponding BI4 version.
G. Adding instances does not always mean multiplying scalability
In order to extend scalability you may think of adding more and more server instances such as CMS or Webi. It is basically true but keep in mind that adding instances increases overheads in around the interactions between CMS and CMSDB that multiply load on the CMSDB (multiplying querie by the number of CMS instances, synchronization among instances, increasing number of objects to handle..), in around Webi’s disk I/O by its temporary files, increasing inter-process communications etc.. that it may not gain as much efficiency as you expect. The less the instances serving for higher concurrency per instance the more efficient.
Try to identify optimal scalability per each process by fine tuning before multiplying instances
That's all. Enjoy scaling Webi with HANA !
Amandine Lafargue, Nai Minh Quach, Yumiko Hata