(Confluence)Which is faster: MSSql or Postgres?

[Robert Finley]

We use the reporting plugin to report pages and their approval status.

Obviously, setting the Confluence master timeout to 5 minutes is a horrible bandaid.  When this page is loading, we see two of four server cores hover around 50% utilization until the page finishes.

We just moved everything, WinServer 2008 with Postgres, to a dedicated box with an internal Raid-10 flash array and 24gb of physical memory.  I'm aware that moving the database to a separate host is a bad choice.

After reading MSFT's advertising blurbs, Is anyone seeing performance advantages of MS-SQL over Postgres?

[Mike Rathwell]

I've not run Confluence on SQL Server to compare it directly but, given how SQL Server locks records (by temporarily locking the entire table), I've seen massive performance hits on other applications where we've had to implement that (for a variety of reasons) over PostgreSQL or even Oracle. Case in point, Sharepoint requires SQL Server. Look how bad THAT is over Confluence.

Based on that experience, and seeing how it "doesn't" perform in other environments is why I'll only use SQL Server when I am forced to for a variety of reasons. 

Another thing you might do if you can is move to Server 2012. That is what I am running on, and while not as consistently quick as a Linux server, it is, on the whole very quick if a bit peaky.

[Nic Brough -Adaptavist-]

I'd tend to agree with Mike on this - I've had strings of problems with using MS-SQL for all sorts of applications, even Microsoft ones. Not just performance, although I've seen bottlenecks and intermittent slowdowns vanish by simply swapping to one of the other three supported databases. It's going to take you some time to migrate from PostGres to Microsoft, and I very much doubt it's going to improve your situation. I'd be investing in tracing and monitoring the SQL queries to see if you can identify why there's such an issue. My standard recommendation for database choice for Atlassian stuff is in two parts: 1. Given a free choice, PostGres first (because that's what Atlassian use), MySQL a close second, Oracle third, and MS-SQL in a very dstant last place 2. However, you should always go with your expertise. If you have a good MS-SQL team of DBAs who can help you with potential problems and set it all up in an optimal way, and no-one who really knows one of the other databases, then use MS-SQL, simply because it's the best supported in your organisation.

[Robert Finley]

I've been cleared to try Jason's JVM suggestions this weekend.  

Another possible upgrade:  we are running Postgre 9.3.  Upgrade to 9.4?

Discovered IT made the decision to assign 4 cores out of 12 and 8gb physical ram out of 24gb.  Is there such a thing as too much physical ram and too many cores?

Java:  running latest release of Java 7.  I have to wait until Java 8 is official as we're leery of any downtime on our Atlassian servers.

[Nic Brough -Adaptavist-]

When looking at versions, always check the "supported platforms" page. For the latest version of Confluence, it's https://confluence.atlassian.com/display/DOC/Supported+Platforms As I was scribbling this answer, that doc was for Confluence 5.7 and said "Use Java 7, latest release, and PostGreSQL 9.2 or 9.3". So I'd strongly recommend sticking with Java 7 and PostGreSQL 9.3 (For what it's worth, a small one I have here works fine with Java 8 and PostGreSQL 9.4, but I can't recommend that because it's not officially supported)

[Jason Hensler]

The above guide shows Java 1.7 and 1.8 are supported, except the versions with known issues.The upgrade guide (https://confluence.atlassian.com/display/DOC/Confluence+5.7+Upgrade+Notes) also shows 5.7 officially supports Java 7 and Java 8. It's worth noting that Java 7 support will be dropped in Confluence 5.8 and this is the last month for public Java 7 updates from Oracle as well. That being said, I wouldn't recommend switching the JDK out over the weekend and just assuming it will work because the guide says it will. There is a possibility things could break in the JDK 8 so, I would recommend mimicking your production environment on another vm/machine using your dev license to validate your config before switching. In addition to tuning I would strongly recommend monitoring what the jvm is doing. This requires some additional java options on confluence. You can use either jvisualvm or jconsole, they're in the JDK bin folder. You would run them from your desktop and connect to the confluence server via the jmx port. Either will give you cpu usage, memory usage, thread activity, etc, I prefer to use visualvm as it shows GC activity on the cpu graph, there is also a tracer plugin that will show gc runs. You will need to add the following options to the setenv.sh to enable a jmx console connection: export CATALINA_OPTS="$CATALINA_OPTS -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=19999 -Dcom.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote.authenticate=false". I would strongly suggest that you set a password on the jmx console, Oracles guide is here for that: http://docs.oracle.com/javase/7/docs/technotes/guides/management/agent.html. Atlassian's guide on jmx monitoring is here: https://confluence.atlassian.com/display/DOC/Live+Monitoring+Using+the+JMX+Interface. Another option to analyze the GC activity is by enabling garbage collection logging, see https://confluence.atlassian.com/display/CONFKB/Enable+Garbage+Collection+Logging. Red Hat has a nice tool to parse the log and give you some suggestions on additional tuning options https://access.redhat.com/labs/garbagedog/. Note that the log files can get pretty big pretty quickly.

[Jason Hensler]

We use mssql here in house for our internal confluence instance. We are running the confluence instance on it's own Redhat Enterprise Linux VM. The Vm is built with 4cpu, 8Gb ram. On the Vm itself we have a apache httpd instance in-front of tomcat to handle the ssl termination. We are connecting from Apache to the confluence tomcat using mod_jk/ajp.

The Mssql database is on a separate dedicated machine. When I started with company we were running confluence version 3.0.1, mssql support back then was not as good as it is today. We would have slow downs that were db related. The most important thing for mssql databases and atlassian apps is enabling read snapshots (see DB setup guide), this fixed alot of the issue we had early on. I am also lucky that we have a very proficient DBA team so, I do not having to worry about the database server itself.

Our instances Max heap size is 3GB, Max permgen is 1GB. A smaller heap size will not necessarily  prevent full gc's and it may even cause them to happen more often. For heap you want to have some breathing room, if you don't you'll get close to the max heap and java will force full gcs frequently. I also recommend tuning your gc, I used to have some gc issues until I did. I currently use -XX:+UseParNewGC -XX:+UseConcMarkSweepGC -XX:+UseTLAB. This will cause the gc to use more than one cpu and the concmarksweep will do smaller garbage collations, instead a massive full gc. Also we're a java application house and have seem some good results from using Java 8. If you want to investigate the Garbage collector issues, hook up a jconsole or jvisualvm console and watch what it's doing.

[Mike Rathwell]

Robert,

The official Atlassian position is to keep the heap as small as you can without getting heap memory errors. This ostensibly solves the "stop the world" garbage collection issues.

Fine. Makes sense.

But, I found that while it would be alright almost all the time, edge cases that really hammered the heap (like re-indexing and the like) would error out. SO... I set my heap to 2GB as you did AND set the MaxPermGen to 1GB. I also added 8m for threads. I did have some issues with the "stop the world" (interestingly enough, more on Windows Server 2012 than I did on Ubuntu 14.04 and yes, I had my reasons to go to Windows; some of them good ones). HOWEVER, with some tuning of the many caches in Confluence, I minimized that greatly.

Overall this did solve a lot of the issues you'll have been seeing as well and with tuning, makes it consistently quick.

Now... if I could just figure out why things that are so easy in bash are mindbubblingly hard to do in Windows PowerShell, I'd be laughing 

[Robert Finley]

Our ERP software uses MS-SQL, so IT has their preferences.  I suspect we do not have that kind of DBA expertise in-house.   I'll stick with Postgres.  Your advice was invaluable and saved me a lot of testing.

We are strongly motivated to stay inside a VM for now.  But, we have only activated 4 of 12 cores on the chip.  More == better?   

The first time we booted the VM on its private box, CPU meters hit about 95%, and Confluence using 95% of 1Gb Java Heap.  

So, we doubled the heap to 2Gb and rebooted.  Strangely, things settled down super quick, thanks to our new flash array (login prompt in less than 15 seconds of rebooting.)

What kind of performance issues will we see from allocating 2gb of java heap?