Tuesday, March 1, 2011

Stress Testing Oracle XE 10g

Oracle Express Edition (XE) is Oracle's free entry-level database product, currently available only in a 10g version. XE is usually pitched as suitable for personal work or (very) small departmental applications, but I was curious as to what kind of load it can support.

Oracle XE 10g has the following limitations:

  • Up to 1 instance per server
  • Up to 1 CPU (will not use more even if available)
  • Up to 1 GB RAM (will not use more even if available)
  • Up to 4 GB datafiles (not including XE system data)
  • Free to develop, distribute and deploy in production


By the way, several things seem to indicate that Oracle Express Edition (XE) 11g is just around the corner. It's rumored that XE 11g will raise the datafile limit from 4 GB to 11 GB, but the other limits will remain as far as I know (and I don't really know anything about it...!). So these performance tests will probably be representative both for XE 10g and 11g, although I can't know for sure until 11g is available.

Here is the test environment I set up:

Hardware

Note that the "hardware" in this case actually runs virtualized in a VMWare environment.
Here are the resources allocated to the server:

  • 1 Intel Xeon X7350 2,9 GHz CPU
  • 4 GB RAM (but remember that XE won't use more than 1 GB anyway)
  • 30 GB disk space


Software

The software setup was as follows:

  • Windows Server 2003 R2 Standard Edition Service Pack 2
  • Microsoft Internet Information Server (IIS) 6.0
  • Thoth Gateway
  • Oracle Express Edition (XE) 10g
  • Oracle Application Express (Apex) 4.0


Note that I am not using the Embedded PL/SQL Gateway (DBMS_EPG) as the web server, but rather IIS in combination with the Thoth Gateway, a free ASP.NET replacement for mod_plsql and DBMS_EPG that I wrote in C# using ODP.NET.

Everything (database, web server, Apex) was installed using default settings.
The Apex images folder (/i/) was set up with "Expires"-headers (7 days) to allow browsers to cache images.

Both the database and the web server run on the same machine (server), in order to keep the setup as simple as possible (and show what is possible with just one box).

Test page

I set up an Apex test page with a mix of regions, including a report region, a couple of PL/SQL regions and an HTML region. For each page view, a random number is generated (to simulate different users looking at different things). The report query selects up to 200 rows based on this random number (using bind variables, of course), out of a system table/view (DBA_OBJECTS) with around 17,000 total rows.



There is also an after footer page process that inserts a row in a log table, so this is not just a read-only page.

The application was set up with an authorization scheme of "No application authorization scheme required" to allow it to be accessed from the online stress test tool.

Stress test tool

LoadImpact is a user-friendly, web-based stress testing service that allows you to run free tests that simulate up to 50 concurrent clients (you can pay to test with more clients). A key point here is that the load is actually generated from their test servers on the public internet, so this gives a more realistic test than simply running a stress testing tool on your local network.

You don't even need to sign up, just enter the URL of a website to test it (apparently, there are safeguards to prevent this from being used as a denial-of-service attack tool).

A note regarding image caching: The LoadImpact service does not cache anything. This means that all the Apex images, Javascript and stylesheets will be loaded on every page hit, even though in reality they would be cached in the user's browser after the first page view. Since I wanted to test the database's ability to handle page views, rather than IIS's ability to serve up static files, I changed the Apex page so that the standard Javascript and CSS files were not included. I then manually added the core stylesheets (core Apex + theme) back on the page via the page template, just to preserve the look and layout of the page. But even these could have been left out in order to simulate a scenario where most users have the files cached in their browser.

Test results

The free test at LoadImpact runs five different subtests, starting with 10 concurrent clients and ending with 50 concurrent clients. The full test takes 10 minutes, so that is in five 2-minute parts with increasing load.

While this was going on, I watched the CPU usage in the Windows Task Manager and grabbed the following screenshots:





While there are some peaks at the start of each subtest, it seems like the database "warms up" and actually does less work at the middle and end of the subtest compared to the start. Even at 40 and 50 clients, the CPU is (on average) not working at more than half capacity.

Here is the summary from LoadImpact at the end of the test:



First of all, note the nice and almost flat curve, with response times of under 300 ms even with 50 clients (and remember that the load generator is actually located in a different country, accessing the Apex page via the public Internet).

Actually, while the test was running, I was also clicking through a separate Apex application (an actual application, not just a test page) on the same server, and I couldn't really notice that the server was under any kind of stress; the response times were excellent.

The total number of requests for the whole test was 4500, but these requests include not only the actual page (the call to the "f" procedure) but also 2 stylesheets and 3 images. So the database was hit 1 in 6 times, in other words 16,7% (of 4500) or 750 times. Each hit inserted a row into a log table. If we query that log table and group by down to the second, we can get the number of database requests per second:



This shows that the XE database was handling 10 or 11 page views per second. If this can be sustained over time, that is 36,000 page views per hour, or 864,000 page views per day.

By comparison, apex.oracle.com is reported to have 4,800,000 page views per week or 685,000 page views per day. And according to this, Reddit has 10 pageviews per second per server (with 80 servers), while Facebook has just 2 pageviews per second per server (with 30,000 servers!).

I won't read too much into these numbers (as obviously a "page view" can vary wildly between different websites, and only actual use can tell whether your site is performing well or not), but clearly the free XE database is more than a toy.

I'm even tempted to buy a Basic or Professional test at LoadImpact to test with more than 50 clients and see where that curve leads with 60, 80 or 100 concurrent clients...

10 comments:

Roel said...

Very nice! But IMHO this stress test says more about APEX than 10g itself: APEX is very very scalable - even with modest HW and the cheapest DB possible!

dmcghan said...

Great post! I would love to see XE with a Java engine. It only makes sense now that Oracle owns it.

jnowl said...

Fascinating.

Would be very interesting to see if there are differences between Thoth Gateway, PL/SQL Gateway (DBMS_EPG), and mod_plsql vis a vis performance.

Morten Braten said...

@Roel: Yes, I agree, it's a good showcase for the Apex framework too.

@Dan: Why Java? PL/SQL is king! But seriously, I really don't see Java as essential to the XE database. It would only bloat up the installation size and require more memory to run. I'd rather see Oracle spend time on improving PL/SQL.

@Jnowl: I don't think you can run mod_plsql with XE without paying for a database or app server license, which kind of defeats the point of using XE. The Embedded PL/SQL Gateway would probably suffer in terms of performance because all images must be served from the database (via XDB). But you could use Tomcat and the Apex Listener with XE, that should probably be comparable to IIS + Thoth Gateway + XE.

- Morten

Gary Myers said...

Great material. It is good to see some firm numbers on performance and scaling. thanks

Algis said...

Morten,

WOW! Great post!
I always wonder, what would be the concurrent connection limit for: Oracle 11R2 SE 4CPU RAC? Sounds interesting but I don't have the servers to test it.

oleg said...

Hi, I also performed some kind of stress testings of different web sites months ago with that Load Impact web site and found out that apex.oracle.com (i.e. apex itself) has too much jquery/js/css files to load even on the first page that makes it behave not in a good manner comparing to some industry standard web sites.

My article:
http://dbswh.webhop.net//dbswh/f?p=BLOG:READ:0::::ARTICLE:96400346724530

Regards,
Oleg

Morten Braten said...

@Oleg: Regarding your comparison of Facebook and Apex (at apex.oracle.com), I ran both through the YSlow plugin for Firefox.

For the Facebook main page (http://www.facebook.com), you actually get a lot of Javascript and stylesheets as well:

This page has 4 external Javascript scripts. Try combining them into one.
This page has 4 external stylesheets. Try combining them into one.

For the Apex login page (http://apex.oracle.com/pls/apex/f?p=4550):

This page has 8 external Javascript scripts. Try combining them into one.
This page has 3 external stylesheets. Try combining them into one.
This page has 19 external background images. Try combining them with CSS sprites.

But additionally, you get this:

Grade F on Add Expires headers

There are 34 static components without a far-future expiration date.

Grade F on Compress components with gzip

There are 11 plain text components that should be sent compressed


I don't know why the folks at Oracle haven't configured Expires headers and Gzip compression, it would save them a lot of bandwidth (but I see that Larry is #5 on the Forbes list again, so maybe they don't care :-).

Personally, I always set up Expires-headers for static content, as it vastly improves the perceived and actual speed of a site (and on IIS it takes 10 seconds to configure).


- Morten

Anonymous said...

Thanks dude, useful :)

Anonymous said...

Great piece of information,

Thanks for this.