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

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5

    Performance drop


    The performance of our database drops significant. Clear cause of this is not known.

    Server: HP Proliant ML350 G5
    Raid 5, 150.000 rpm
    3 GB dedicated.
    OS: XEN server, fbserver runs on virtualisation of debian Lenny.
    Firebird 2.

    I suggest it was caused by indexing???
    Can somebody support this?
    What could be the cause of instantly performance loss during normal office labour.

    Sorry for my bad English. Your help and advice is appreciated.

    R.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    14
    need more details!

    did you check the hardware? is the RAID ok?
    is it still slow or it came back to normal?

    did you try restaring the FB daemon?
    are the CPU-s working hard? how about the memory?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    We checked the hardware with a HP diagnostic disk. No abnormalities where found.

    Next morning everything was normal again. The database server runs in favour for at most 30 clients that working with a Delphi application. It is an ordinary office.

    Processorload was between 0.00 and 0.30.

    Memory was fluctuated between 0.25 and 0.98

    I did not try to restart the FB deamon. Is this advised? What about the danger of dataloss?

    R.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,054
    Rep Power
    383
    If performance went:
    normal->slow->normal
    the least likely culprit is the database internals.

    Odds are you had a bottleneck somewhere in your environment.

    Remember, Firebird access could slow down if your network was maxed out by users doing things completely unrelated to Firebird or the Delphi clients.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Thanks. So we have a total of 9 servers for dedicated tasks like:

    data/files
    mail
    database
    calculations
    communication
    security

    I am a little lost now, difficult to find the bottleneck.

    Clients are connecting to the database with the Delphi app. but there is also a webinterfase (ASP based, running on ISS) for external users that also connects the database. It is hard to monitor all the activities.

    If you have some advises, please let me know.

    R.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Dear reader,

    To deal with this problem I intensified the monitoring of the entire network. (Also the whole application is in the exclusion list of our anti-virus protection). Our system consists of:

    Client (30 PCs):
    Windows XP
    Delphi application
    Duo Core, 2GB, 1 GBit network

    Switch: 1GBit network

    Server:
    Linux Debian Lenny
    Firebird 2.0.4
    Quadcore, 6GB, 1GBit, RAID5: 15.000 rpm

    For weeks everything went normal.
    To improve the performance our programmer decides to make the most used table smaller. All old records where moved to a new table: History. This was one week ago.

    Just a question: do you think this will help???

    In the week after this Diskwrite, CPU, memory, network use use of the server where significantly higher than normal. No other things in our system where changed or changing. The level of usage of the database in our company was the same as before. But a lot of the employees are inform me of the fact that search and opening a record take significantly more time than normal, (up to 5 seconds instead of max: 1 sec).

    Could this been due the change in tables? Maybe some re-indexing or something, because of the change in content?

    I was wondering if somebody could explain me what happens exactly when you use a delete, update and/or insert statement? Are some actions delayed on purpose to save the system from really abnormal processor load. (I have a mailsystem that works like this). I mean it is known that update and delete statements are very intensive. Besides, big changes in a table will make a current index useless.

    After one week the performance improved, everything works fine know.

    So what do you think? Could you explain this. I hope this message is read by those intelligent people that helped and advised me before. Sorry for my bad English and thanks for your intention.

    Robert
    (system-admin/student)
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,054
    Rep Power
    383
    If performance keeps speeding up slowing down and speeding up again while schema including indexes and statistics remain stable and up to date then the two most likely culprits are an external bottleneck as discussed earlier or resource contention if multiple folk are trying to change the same records at the same time (although Firebird is generally good about that stuff).

    Another possibility is that there are certain periods when users are running particularly demanding queries or batch updates and during those periods, others will see a slow down.

    It is really hard to answer these questions in the abstract.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Originally Posted by clivew
    ....

    Another possibility is that there are certain periods when users are running particularly demanding queries or batch updates and during those periods, others will see a slow down.

    It is really hard to answer these questions in the abstract.
    The funny thing is that there are users performing demanding updates, but at those moments performance is just fine.

    Indeed this is hard to explain. Our programmer keeps declaring that his software is oke and running without problems on several other sites.

    But thanks anyway, if I find some additional information or maybe a solution I will post it on this forum.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    It is announced that Firebird 2.5 is stable on Debian Lenny.
    That it also includes performance improvements.
    Hardware issues with the HP Proliant Servers are solved by HP.

IMN logo majestic logo threadwatch logo seochat tools logo