5.3 Tuning for Speed

OSCAR at times runs slow, and this might be an issue of configuration. Here are some tips on how to tune OSCAR for speed

First of all are you slow?

Oscar naturally has several areas where it can be slow. The inbox, is often the area where this is seen, however chart opening can be slow as well.

There are lots of measures that you can use to gauge performance and to determine where the bottle neck is but ultimately we want the end user to have a quick response.

While every system can benefit from tuning, you need to know your current state to determine if the changes you are undertaking are effective.

My benchmarks for local access is 7 seconds for a chart open for a simple chart open (albeit a chart with 10+ years of OSCAR data filling all the boxes) with 5 second reload

3-6 seconds for Inbox to load the list of all new results and HRM reports

an additional 5 seconds for preview to load for the first time

and an additional 2 seconds for preview to load the next bunch of reports when you reach the end of preview

If you are already there, then there may not be any point to the following!

Tuning OSCAR for speed

More or less in order of decreasing return. Take with a grain of salt as you are getting this from a physician, and not a systems operator type.

1) MySQL itself

The primary tweak in OSCAR is the setting in MariaDB/MySQL for the innodb_buffer_pool_size setting. The buffer will load the tables and indices needed for queries into memory. Standard advice is that you should allocate 75% of the RAM that is left after Tomcat to the innodb_buffer_pool_size setting. How much is actually useful/needed is based on how big your schema and associated indices are. Log into MySQL and run the following query (it will take half a minute)

mysql> SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|    25 |
+-------+
1 row in set (15.49 sec)

So this particular oscar_15 might have a 3 G compressed database backup but expanded with indices is using 25 gigs of space. It can use up to one and a half times that for the buffer (40GB). You can check the existing setting by.

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                6442450944 |
+---------------------------+
1 row in set (0.00 sec)

So you can see 6G are set for innodb_buffer_pool_size which is no where near. Determine in conjunction with the Tomcat's JAVA_OPTS memory setting (see below) and server overhead that you have enough physical memory.

So lets say you have 19GB total 6 in Tomcat/Java allow 2G for other server functions then you have 11G left. To be safe we allocate 75% of that or 8G. Edit /etc/mysql/my.cnf to ensure that your innodb_buffer_pool_size setting is where you want

innodb_buffer_pool_size = 8G

Obviously adding more physical memory (or allocating it if you are running in a VM) and increasing it further will be of benefit. There are books on tuning MySQL's other settings but for most you can run a tool called mysql tuner. Install it by Simply:

apt-get install mysqltuner

The script will analyze your MySQL instance and suggest settings for /etc/mysql/my.cnf

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 20M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 600)
    innodb_buffer_pool_size (>= 25G)

2) Java Memory allocation to Tomcat

The Deb allocates as a rule 60% of your physical memory to Tomcat vis a Java setting. At the start OSCAR has minimal data and it might be run as a demo on old hardware. As your data grows your actual consumption in Tomcat remains similar and your database needs more and more memory. To run faster you can likely decrease your allocation to Tomcat to free it up for your database as set in 1) above. Remember if either element is starved for memory you will start using the hard drive with the same slow down symptoms.

To determine if you need to add memory to Tomcat or to the Database you must determine how much memory Java is using. In OSCAR 15 enable monitoring (see /15/5.0/monitoring ). It has lots of performance data (including how much Java memory is being used). On the same well resourced box as in 1) the data is currently as below.

Java memory used: 1,580 Mb / 5,897 Mb

If you look at the graph we run no less than 0.7G at idle. During the day under load we run up to 5G and after garbage collection we use as much as 2.5G. As we have 6G allocated perhaps we could do with a bit less. Adjust this in /etc/default/tomcat6 (for OSCAR 15 its in /etc/default/tomcat7 or /etc/default/tomcat8).

3) Physical Memory

If the server is running out of memory then the operating system will write memory to hard disk which substantially and abruptly slows the server. Look at Admin > System Reports > System status for the box called vmstat. Other methods at the command line include systat, free, htop, and top.

$ vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 215460 320852 607348 6405012    0    0   217   168    4    6 12  2 82  3  0

In the above example there is plenty free (for more details google vmstat) so this system is not currently running into an issue, you have not over allocated. However in the example above you could do with another 40G of memory to fully meet MySQL's ability to buffer tables.

4) Oscar indices

As in a library databases use indexes to quickly find the information you want. You can determine if the queries are running quickly by using the monitoring tool. Any query running more than half a second (500ms) is noticeable to the end user. Some time has been spent in OSCAR 15 to optimize indices but I still have the following SQL which takes about a second to run

select hl7textinf0_.id as id332_, hl7textinf0_.accessionNum as 
accessio2_332_, hl7textinf0_.discipline as discipline332_, 
hl7textinf0_.filler_order_num as filler4_332_, 
hl7textinf0_.final_result_count as final5_332_, hl7textinf0_.first_name 
as first6_332_, hl7textinf0_.health_no as health7_332_, 
hl7textinf0_.lab_no as lab8_332_, hl7textinf0_.label as label332_, 
hl7textinf0_.last_name as last10_332_, hl7textinf0_.obr_date as 
obr11_332_, hl7textinf0_.priorSimply.ity as priority332_, 
hl7textinf0_.report_status as report13_332_, 
hl7textinf0_.requesting_client as requesting14_332_, 
hl7textinf0_.result_status as result15_332_, 
hl7textinf0_.sending_facility as sending16_332_, hl7textinf0_.sex as 
sex332_ from hl7TextInfo hl7textinf0_ where hl7textinf0_.accessionNum 
like ?

Java Melody Monitoring advises that its run in /lab/newLabUpload.do I don't care about a one second wait, and unless this is your problem its something I would ignore anyway as the index will be quite large and require even more memory. If you lack this monitoring tool you may activate the slow query log of mysql in /etc/mysql/my.cnf by adding the following

long_query_time=2

log-slow-queries=/var/log/mysql/log-slow-queries.log

log-queries-not-using-indexes

Note that the slow-log itself can slow performance. Turn it off when you are not using it!

5) Tomcat Settings

Tomcat is usually installed with the same settings used in a development box or a small clinic. That's fine until you notice that you are running slow, and its necessary to change the defaults to more appropriate settigns.

By default, the maxThreads attribute is set to a meagre 200. This is appropriate for a single core machine, but those have not been made for years. Simply multiply by the number of cores and processors that your server has by 200. On a server grade machine with two Xenon processors with 6 cores each, setting this value to anything between 1000 and 2000 will not cause a problem. This is not wasteful as the thread pool will naturally scale back from this number when the server load is low.

Compression is another easy gain. Moving data on the network is slow, even more so on the internet. Setting compression on will allow for all the text and html that you are sending to OSCAR be transferred up-to 8 times faster.

Putting it together your server.xml might have an entry that looks like the following (taken from a production tomcat 7 example)

<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<Connector
           protocol="HTTP/1.1"
           port="8443" 
           maxThreads="1000"
           scheme="https" 
           secure="true" 
           SSLEnabled="true"
           keystoreFile="/etc/tomcat7/.keystore" 
           keystorePass="changeit"
           clientAuth="false" 
           sslProtocol="TLS"
           maxPostSize="0"
           minSpareThreads="80"
           maxSpareThreads="160"
           compression="on"
           compressableMimeType="text/html,text/xml,text/plain" 
           ciphers="TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,TLS_DHE_RSA_WITH_AES_128_GCM_SHA256,TLS_DHE_DSS_WITH_AES_128_GCM_SHA256,TLS_ECDHE_RSA_WITH_AES_128_SHA256,TLS_ECDHE_ECDSA_WITH_AES_128_SHA256,TLS_ECDHE_RSA_WITH_AES_128_SHA,TLS_ECDHE_ECDSA_WITH_AES_128_SHA,TLS_ECDHE_RSA_WITH_AES_256_SHA384,TLS_ECDHE_ECDSA_WITH_AES_256_SHA384,TLS_ECDHE_RSA_WITH_AES_256_SHA,TLS_ECDHE_ECDSA_WITH_AES_256_SHA,TLS_DHE_RSA_WITH_AES_128_SHA256,TLS_DHE_RSA_WITH_AES_128_SHA,TLS_DHE_DSS_WITH_AES_128_SHA256,TLS_DHE_RSA_WITH_AES_256_SHA256,TLS_DHE_DSS_WITH_AES_256_SHA,TLS_DHE_RSA_WITH_AES_256_SHA"
/>

Detailed elsewhere is the Apache Portable Runtime (APR) based Native library for Tomcat. This connector is optimized for speed in part by using OpenSSL for encryption rather than Java Secure Socket Extension (JSSE). This is used for Tomcat production and uses slightly different SSL settings for the connector.

6) Upgrade hardware to a bigger faster box

This is so rarely the issue that I hesitate to suggest it. However if you are big enough (number of users say > 100) MySQL starts slowing down. The real symptom of this is that people start volunteering for evening shifts so that they can get through their inboxes quickly.

You should only consider a bigger box(s) after you have exhausted the above. In those cases there are ways to split off the OSCAR server(s) from multiple MySQL (or MariaDB) servers that load balance. This is hard core sys admin stuff where most OSCARs have not gone. Good luck and report back your challenges and success to the list!

Document Actions

Send this

Personal tools