local protocol and multi-core processors

We found strange behavior of local protocol connection of the Firebird SuperServer 1.5 and Windows. Tests were made on AMD 2-core processor computer with command line backup like
gbak -b -g db.fdb db.fbk
When gbak is not "attached" to cores (uses all), or "attached" to another processor core where the Firebird SuperServer runs (for example, fbserver. exe at core 0, and gbak.exe at core 1), gbak nearly not loading used core, and fbserver.exe loads it's core only at 50%.
When we attach gbak.exe to the same core that uses fbserver.exe, backup speed raises nearly 2 times, and fbserver.exe loads core at 95%. Example results for gbak -b -g of 3.8 gb database:

• fbserver and gbak on the same core - 9 min 22 sec.
• fbserver and gbak on different cores - 15 min 41 sec.
  

This is an opposite to tcp protocol, when it doesn't matter on what core gbak.exe is run, and backup takes 4:10 minutes.

So, right now we do not suggest to use local protocol for Firebird 1.5, and use localhost instead. More tests on the way, stay tuned.

What is sort 2

Someone may make wrong conclusion from my previous post about sorting that "sorting ... mostly does writes". But, yes, I was speaking only about temporary sort files, and the sorting process itself. The whole picture of the query with PLAN SORT is the following

• server (Firebird, InterBase) reads portions of the data from the query, does sorting of this block and writes it to the temporary file. So here we have reads from the database and writes to the temporary file.
• after all data was read from the query (database) and sorted, server begin to send sorted data from the temporary file to user. Of course, this happens only if client application reads the resulting data, i.e. call "fetch". Here we have reads from the temporary file.

The number of writes and reads of the temporary file in this case is the same. But the number of database reads depends on the query itself and the amount of the processed records. I will speak about it later, because right now my computer is busy by some another interesting test.

What is sort?

Inspired by discussion about sorting (PLAN SORT), did some simple tests. Right now I do not have "an article" about this, but want to show you some discovered facts:

• sorting the temporary file (fb_sort_nnnn.tmp and ib_sort_nnnn.tmp) mostly does writes, not reads (excluding database reads and fetching data from temp file). Firebird 2.1 read/write ratio is 1:10. InterBase 2009 read/write ratio is different, and nearly 2/3.
• turning on Windows folder/files compression for TEMP lowers sorting temp file size up to 2-4 times (depends on sorting data, I've used repeating data, sorry)
  
• turning on Windows folder/files compression for TEMP increases processor load 2 times, and makes disk transfer ~4 times less.
• Firebird 2.1 show only small (invaluable) diffrerence when sorting at compressed and uncompressed TEMP (4 min 00 sec). Compressed TEMP produces more stable timings, when test is run several times.
  
• InterBase 2009 sorting speed is equal to Firebird 2.1 only on uncompressed TEMP. And it is slower at compressed TEMP (4 min 00 sec vs 5 min 00 sec). The cause is InterBase's higher read/write ratio (uncompression of blocks being read)
  
• on my computer sorting 31 million records (select varchar(20) from ...) produces temp file with the size of 4.28GB (uncompressed)
  
• InterBase 2009 uses bigger sorting blocks (chunks) than Firebird 2.1. By "sorting block" I mean set of records that are being sorted in memory and then written to sort file for future merge with other blocks
  
• Using uncompressed TEMP InterBase 2009 loads processor less than Firebird 2.1 (35% vs 40%), but writes to disk faster (30mb/sec vs 25mb/sec)

This was a "single-user" test. Running concurrent sorting queries may produce different result. What can be said now for sure, is that if you have lot of queries with PLAN SORT, you must (!) have TEMP pointing to the separate physical drive. And maybe RAID 0 will help.
So, questions? :-)