#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5

    Performance issues Firebird due number of users


    Hi,

    Currently I am researching Firebird performance. What is causing the bad performance?

    The problem:

    Huge performance issues.

    The symptoms:

    - 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.

    The goal:

    Confirm hypothesis: the more users the slower the system (sounds obvious don't you think?).

    The approach:

    - 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

    Conclusion:

    Hypothesis confirmed.

    Solution:

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    What about relational design vs object design?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    Both are important. What's your question?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    My question:

    Would a object design provide more performance in respect to a relational design? (Pointers vs Join/FK)
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    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
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    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.

    This query:

    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

    • mariuz agrees
    Last edited by mIRCata; December 27th, 2011 at 05:08 AM.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    This is the reply every poster is hoping for.

    Happy new year mIRCata!

    ---EDIT---

    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.

    wow.


    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.

IMN logo majestic logo threadwatch logo seochat tools logo