Tuning OSCAR for SPEED
OSCAR speed tips updated
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!
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.
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)
MariaDB [(none)]> 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 | +-------+ | 37 | +-------+ 1 row in set (1.84 sec)
So this particular long running OSCAR might have only 8 G of data but it also has 29 G of indices so is using 37 gigs of space. It can profit from up to one and a half times that for the buffer (60GB). You can check the existing setting by.
MariaDB [(none)]> SELECT @@innodb_buffer_pool_size/1000000000; +--------------------------------------+ | @@innodb_buffer_pool_size/1000000000 | +--------------------------------------+ | 12.8849 | +--------------------------------------+ 1 row in set (0.00 sec)
So you can see 12G are set for innodb_buffer_pool_size which is nowhere 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 32 GB total of which 6G in Tomcat/Java allow 2G for other server functions then you have 16G left. Lets allocate all of that or 16G. Edit /etc/mysql/my.cnf to ensure that your innodb_buffer_pool_size setting is where you want
innodb_buffer_pool_size = 16G
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
Java Memory allocation to Tomcat
The Deb allocates as a rule 60% of your physical memory to Tomcat visa 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 proportionately more and more memory. To run faster you can likely decrease your allocation to Tomcat to free it up for your database. If either element is starved for memory you will start using the hard drive to page memory 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/19 enable monitoring. It has lots of performance data (including how much Java memory is being used) right now and graphically to show the maximum. On the same well resourced box as above the data is currently as below.
Java memory used: 1,580 Mb / 5,897 Mb
So we are using 1.5 gig, the graph states we max at 2G but have 6 Allocated. Perhaps we could do with a bit less. Adjust this in/etc/default/tomcat6 (for OSCAR 19 its in /etc/default/tomcat8 or/etc/default/tomcat9). The amount of memory used by Tomcat depends on OSCAR and depends on the number of users and connections so expect the needs to increase during the day, and slowly over years, so don’t cut it too thin.
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 st0 0 215460 320852 607348 6405012 0 0 217 168 4 6 122 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.
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 asaccessio2_332_, hl7textinf0_.discipline as discipline332_,hl7textinf0_.filler_order_num as filler4_332_,hl7textinf0_.final_result_count as final5_332_, hl7textinf0_.first_nameas 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 asobr11_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 assex332_ from hl7TextInfo hl7textinf0_ where hl7textinf0_.accessionNumlike ?
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 addingthe following long_query_time=2log-slow-queries=/var/log/mysql/log-slow-queries.loglog-queries-not-using-indexes
Note that the slow-log itself can slow performance. Turn it off when you are not using it!
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 settings.
By default, the maxThreads attribute in Tomcat 9 is set to a meager 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"/>
for Tomcat 8 another production example
<Connector URIEncoding="UTF-8" server="Apache Tomcat" port="8443" executor="tomcatThreadPool" SSLEnabled="true" maxPostSize="-1" scheme="https" protocol="org.apache.coyote.http11.Http11NioProtocol" maxThreads="800" > <SSLHostConfig sslProtocol="TLS" protocols="TLSv1.2" honorCipherOrder="true" ciphers="TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384, TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256"> <Certificate certificateKeyFile="/etc/tomcat8/privkey.pem" certificateFile="/etc/tomcat8/cert.pem" certificateChainFile="/etc/tomcat8/chain.pem" type="RSA" /> </SSLHostConfig> </Connector>
Upgrade hardware to a Newer 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 OSCAR Devel list!