December 17th, 2011, 10:09 AM
Performance issues Firebird due number of users
Currently I am researching Firebird performance. What is causing the bad performance?
Huge performance issues.
- During regular office hours (8 am - 5 pm) everybody complains about bad performance.
- Evening and night shifts are suffering less, system is faster during the night.
Confirm hypothesis: the more users the slower the system (sounds obvious don't you think?).
- Building one single test database of 2.7 Gigabytes. 1000 tables each 10000 records, 50 columns.
- Stressed with algorithm that randomly selects 17 tables, joins them and randomly selects 1 record.
- redo select/join 100 times
- redo all for 2, 3, 4 and 5 users
Remark, all stresstests are localhost only, so no network delay.
The hardware: Pentium DuoCore, 32 bit, 4000 MB ram, Linux Debian
Firebird version: 2.5, default configurations
The results (average of different duplicated results):
1 user --> 1 m 50 s
2 users --> 4 m 10 s
3 users --> 6 m 20 s
4 users --> 8 m 16 s
5 users --> 10 m 39 s
Don't make such ridiculous big and expensive queries. Provide the user just the information he needs. So: a good software design.
Like to read about your questions/remarks and suggestions.
December 19th, 2011, 03:42 AM
Yeah, it's all about on how developers use Firebird, really. You can easily screw up with just a few users and you can build a system with 100 users on top of Firebird, which works very well.
In various consulting gigs, I've seen many problems in respect to client transaction management, being possibly the number one reason for bad performance. Another one is of course bad written SQL statements or missing particular indices. Another one is an over-indexed database with useless indices. Another one is not taking care of a sensible Firebird server configuration in respect to RAM usage.
A lot of stuff worth to be discussed in a one/two day workshop.
December 19th, 2011, 10:11 AM
What about relational design vs object design?
December 19th, 2011, 12:41 PM
Both are important. What's your question?
December 19th, 2011, 04:39 PM
Would a object design provide more performance in respect to a relational design? (Pointers vs Join/FK)
December 25th, 2011, 04:19 PM
I found more:
in tables of 10000 records, 50 rows.
Firebird finds the first 0-1000 records significant faster than the last 9000-10000.
Indexes are good, page fill too.
Why is this? I presume Firebird is starting to search always on top of the table.
But this is stupid, in case of finding 9999, it is obvious faster to start at the bottom when known the total number of records...
Any tips to speed this up?
This means ultimately every database will slow down eventually, right?
--- EDIT ---
To answer my own question:
It finds records faster using the SELECT * FROM x WHERE x.y LIKE "9999"
In comparison, this query is way slower:
SELECT FIRST 1 SKIP 9998 * FROM x
And explaining my performance problems (have to admit, due to my own mistake, learning moment).
Still, a linear correlation between number of users and time to wait.
Clearly performance problems could be solved by using cheap and smart queries.
On databases < 10 G It is more likely to be your own code instead of Firebird.
( P.s. if you don't have access to the source code and suffer performance problems (like me) this could be valuable information. Recommend to improve the queries.
Last edited by rapgame; December 26th, 2011 at 07:33 AM.
Reason: answering own questoin
December 27th, 2011, 03:55 AM
I'll try to explain FIRST/SKIP as good as I can.
In one query you have combination from fields you want to see from one or many tables, combined with some rules
described with JOINs or in WHERE clause. I'll call this combination result1. It's different from the one we see after the execution of a query - I'll call it result2.
Lets say that I have two tables T1 and T2 in relation 1:1 by field D.
SELECT T1.A, T1.B, T2.X, T2.Y FROM T1 INNER JOIN T2 ON T1.D = T2.D WHERE T1.A < 100
T1 have 50 rows with A < 100
Firebird prepares result1 with 50 rows and columns A,B,X,Y, and for that it uses records from tables T1 and T2.
In normal query this result1 is the same with the result2 we see from the query's execution.
FIRST/SKIP works with result1 on the time it's returned. It's applied when you need the result of the query - to use it in union, sub-query or just to see it. FIRST SKIP doesn't work with the table's records. You're working with one table and all records in it. That is why you're a little bit confused - it's like a copy of a table but actually it's a result of a query.
So if my query is like
SELECT FIRST 1 SKIP 5 T1.A, T1.B, T2.X, T2.Y FROM T1 INNER JOIN T2 ON T1.D = T2.D WHERE T1.A < 100
result1 is still 50 rows with columns A,B,X,Y but result2 is only one row - the 6th row from result1.
Firebird first prepares what rows/columns to return according to the JOINS, WHERE, GROUP BY and over that result1 it applies FIRST/SKIP. Because it's unknown what the query will return. FIRST/SKIP fetch FIRST + SKIP records from the result1 and returns only FIRST records as result2.
If I have a table with 1M rows and in field X I have the "number" of the row. 1,2,3.. 1M.
SELECT FIRST 1 SKIP 5 * FROM TABLE WHERE TABLE.X > 900000
without FIRST/SKIP will return only the last 100K records. And when they are ready for fetch Firebird applies the FIRST/SKIP - it'll fetch only 6 records from the table - 900001 to 900006, skipping 900001 to 900005 returning only one row: 900006. After that it'll stop.
But if I use:
SELECT FIRST 1 SKIP 900005 * FROM TABLE.
It will fetch 900K+ records. And the fetch time for 6 and 900K+ records is a little bit different . In your case first you are fetching 1000 records, in the second 9999 records. It's 10 times more records to fetch from the database - it's normal to be slower.
Comments on this post
Last edited by mIRCata; December 27th, 2011 at 05:08 AM.
December 27th, 2011, 04:00 PM
This is the reply every poster is hoping for.
Happy new year mIRCata!
I optimized my queries.
repeated my Firebird test for 5 times, 1 user.
Runtime on Firebird: 19 seconds
Did the same, but then on a MySql database:
Runtime on MySql 4 seconds
I am still testing with multiple users.
But so far MySql keeps extremely faster than Firebird.
Its keeps bungeling around that 4 seconds, even when you are testing with multiple users.
E.g. Firebird 4 users: 1 min 5,
Mysql 5 seconds.
Still a clear linear correlation, but more like fore every user 1 second waiting time... not one minute...
Looking to CPU it seems that Firebird is using less in comparison to Mysql. Like it is stuck in first gear or something...
Last edited by rapgame; December 28th, 2011 at 08:17 AM.