Database related general application performance tips

Most of the serious applications nowadays use some kind of relational database engine. Database becomes main data source and data processing module in application architecture. That's the reason why it's common application performance bottleneck and main scalability factor.

Having design and data flow requirements its possible to prepare database system at the system planning and implementation phase. Don't get to much into performance details at that stage because "premature optimization is the root of the evil". Sometimes system requirements and design meets reality and final expectations, but changes in requirements that could change application data flow are more common. The most accurate results are coming from real data and users usage patterns, so getting system responsiveness metrics is crucial even at the early prototype stages.

Having such data about performance "weak points" is a start point to optimize and improve overall system scalability. It's good to begin from top more abstract layers in application architecture before rushing to lower level database storage tweaking.

There are some tips divided by subsystem scopes and ordered by top to bottom abstraction level:

Application scope

• discuss with project stakeholders responsiveness requirements for various application functionalities
• analyze data usage patterns like writes vs reads, common data usage vs casual reporting, written once or changed often etc.- it gives image what could be improved, and what kind of underlying database mechanism you will need
• remove worst bottleneck (having biggest performance impact) first to get best results
• use cache for most used data (reads and writes if its possible)
• design transactions smart- long transactions cause performance problems
• at first you should use normalized data schema, but there are situations where little denormalization is crucial for good performance (f.e. copying some data to most read table to eliminate need for joining other big tables)

Database system scope

• use indexes where it works best (every index adds performance penalty for data writes)
• use vertical and horizontal data partitioning - move less used data into other tables or use database engine specific features
• configure database and use its features like special table types, special indexes for your best

Operating system scope:

• use database dedicated host or performance cluster - for large scale systems
• check network latency and throughput for large data transmissions
• tune underlying disks structure and file system- separate partitions or disks for database files, use low overhead file system or custom database "raw" partitions (like in Oracle DB)

Improve PHP application performance using opcode cacher and optimizer

One of university underpowered server cought "hiccup problems" during students rush at local Learning Management System (leading PHP LMS platform) during the end of semester. Because there was no chance for quick hardware upgrade I've checked server tuning check list again. Many basic settings was optimized (caches, all additional modules turned off etc.), but there was still room for small improvements. The big problem was memory limit under high load.

I've installed XCache PHP opcode cacher (they say it's production ready), and set it to cache most of common used code in RAM. Results was better than I suspected, maybe because of that application is utilizing a lot of PHP code. Under heavy load memory usage dropped about 3 times, the same gain factor was for CPU usage. I've checked if everything works ok and left it working with XCache then.

The next bottlenecks were database and CPU usage for some operations. It's hard to get further performance improvements of that scale without tweaking application code.

Firefox2 and css caching issue - part 2

I have tried out to change settings for expire headers, and figured it that application adds its own headers to dynamic generated css files. Then apache2 mod-expire tries to add own settings.
After tweaking application settings there are resulting headers:

Date: Mon, 1 Mar 2008 06:46:02 GMT
Server: Apache/2
Cache-Control: max-age=600, max-age=604800
Expires: Mon, 6 Mar 2008 06:46:02 GMT
Vary: Accept-Encoding
Content-Encoding: gzip
Content-Length: 18128
Content-Type: text/css


Expire times:

• max-age=600 - given by apache2 global settings


• max-age=604800 - given by applications itself

It's ugly - but surprisingly caching works under Firefox with these headers. I haven't checked yet which expiration time is taken by Firefox. I need to clean it and remove one of extra headers anyway.

Below are old headers for the same resource fort comparison - when Firefox caching was not working:


Date: Sat, 01 Mar 2008 07:28:31 GMT
Last-Modified: Sat, 01 Mar 2008 07:26:56 GMT
Expires: Sat, 01 Mar 2008 07:36:56 GMT
Cache-Control: max-age=600
Vary: Accept-Encoding
Content-Encoding: gzip
Content-Length: 18128
Keep-Alive: timeout=10, max=98
Connection: Keep-Alive
Content-Type: text/css


There are removed last-modified and keep-alive headers, but it's hard to understand how it can change Firefox caching.
I have to check difference between 600 and 604800 expire age, that may be solution.

Probably all headers should look like those added by apache mod-expire for static files:


Date: Mon, 1 Mar 2008 06:46:04 GMT
Server: Apache/2
Last-Modified: Thu, 6 May 2007 02:52:18 GMT
Accept-Ranges: bytes
Cache-Control: max-age=604800
Expires: Mon, 6 Mar 2008 06:46:04 GMT
Vary: Accept-Encoding
Content-Encoding: gzip
Content-Length: 3588
Content-Type: application/x-javascript


Firefox 2 and css caching issue

Last week I had strange issue with Firefox cache. One of web application needed tweaking to be more responsive. After playing with apache configuration I tested page load speed using YSlow.

All files have expiry headers and all text files are deflated. Results are still mediocre. Some of dynamically generated css files aren't cached by Firefox 2. IE works as it supposed to do - any subsequent request is handled blazingly fast. I tried it on different installations of Firefox to exclude my browser optimizations side effects. Still the same.

Below are HTTP response headers of css that isn't going to FF cache:

Date: Sat, 01 Mar 2008 07:28:31 GMT
Last-Modified: Sat, 01 Mar 2008 07:26:56 GMT
Expires: Sat, 01 Mar 2008 07:36:56 GMT
Cache-Control: max-age=600
Vary: Accept-Encoding
Content-Encoding: gzip
Content-Length: 18128
Keep-Alive: timeout=10, max=98
Connection: Keep-Alive
Content-Type: text/css


I can't see anything suspicious. Content type is proper, all expire headers are set. Requested file name is named as css.php - does FF makes some assumptions on file extension? I tried turning off Cache-Control header and leave only Expires, but it doesn't work too.

It must be simple detail. Anybody has any clues?