| Subject | In memory sorting consumes alot of space |
|---|---|
| Author | haris_p_sw |
| Post date | 2013-05-16T16:35:49Z |
Hi,
I 'd like to share my recent testing experiences with you hoping to help in Firebird development.
I 've been using Firebird (classic server > 70 connections for web serving) for 13 years now.
Recently, I decided to test certain suspicious select queries more carefully. Here are my conclusions and I hope you prove me wrong:
1. If the query plan uses indexing, everything works OK and very fast.
2. If the query plan goes the "NATURAL" way then sorting happens which is totally expected. However, if the tuning variable TempCacheLimit has a value that is not big enough for the dataset the sorting process happens on hard disk and is extremely slow.
it depends on the machine and the dataset but it has to be a very powerful machine to manage it under 10 secs. On my new 64-bit laptop, Firebird 2.5.2 for a dataset around 30-40 MB I got 35-40 seconds.
The NATURAL plan was unavoidable since there was a need of ordering by two columns from two different tables.
I started tuning TempCacheLimit, LockMemSize, DefaultDbCachePages and FileSystemCacheThreshold. I discovered that caching in memory does not speed up things so I gave FileSystemCacheThreshold a big value. I managed to get an execution time around 2 seconds(4 for the first run) when TempCacheLimit was: 536870912(classic super server. With plain classic server you need more memory).
I know that the above value is only a limit. The problem is that during query execution I noticed that 200 up to 480 MB were actually occupied(depending on the run).
I don't know if things are better with Super Server. It is not an option for me because I am using FreeBSD.
As a conclusion, I was disappointed when I tested postgres 9.2 with the same query. It finishes under a second without using extra memory. It is obvious that postgres also does extra work for my query but in a much more efficient way.
Why Firebird sorts datasets loading whole rows into memory when the plan is NATURAL? Couldn't it sort only primary keys for example? There might be something I 'm missing...
Regards,
Haris
I 'd like to share my recent testing experiences with you hoping to help in Firebird development.
I 've been using Firebird (classic server > 70 connections for web serving) for 13 years now.
Recently, I decided to test certain suspicious select queries more carefully. Here are my conclusions and I hope you prove me wrong:
1. If the query plan uses indexing, everything works OK and very fast.
2. If the query plan goes the "NATURAL" way then sorting happens which is totally expected. However, if the tuning variable TempCacheLimit has a value that is not big enough for the dataset the sorting process happens on hard disk and is extremely slow.
it depends on the machine and the dataset but it has to be a very powerful machine to manage it under 10 secs. On my new 64-bit laptop, Firebird 2.5.2 for a dataset around 30-40 MB I got 35-40 seconds.
The NATURAL plan was unavoidable since there was a need of ordering by two columns from two different tables.
I started tuning TempCacheLimit, LockMemSize, DefaultDbCachePages and FileSystemCacheThreshold. I discovered that caching in memory does not speed up things so I gave FileSystemCacheThreshold a big value. I managed to get an execution time around 2 seconds(4 for the first run) when TempCacheLimit was: 536870912(classic super server. With plain classic server you need more memory).
I know that the above value is only a limit. The problem is that during query execution I noticed that 200 up to 480 MB were actually occupied(depending on the run).
I don't know if things are better with Super Server. It is not an option for me because I am using FreeBSD.
As a conclusion, I was disappointed when I tested postgres 9.2 with the same query. It finishes under a second without using extra memory. It is obvious that postgres also does extra work for my query but in a much more efficient way.
Why Firebird sorts datasets loading whole rows into memory when the plan is NATURAL? Couldn't it sort only primary keys for example? There might be something I 'm missing...
Regards,
Haris