[firebird-support] Re: A question about rdb$character_set_id
11.02.2014 03:42, Ian Lawrence Barwick wrote: Is the integer value of the column rdb$character_set_id from the table rdb$character_sets constant between Firebird versions? E.g. will character set UTF8 always be 4 (and was it 4 in pre-2.5 versions)? Yes, these values remain constant. Purely theoretically, some different UTF8 incarnation may be introduced with a different ID to replace the old one (similar to what happened with UNICODE_FSS - UTF8), but ID=4 will still work as old UTF8. Dmitry
Re: [firebird-support] Firebird slowdowns while resources are available
Hi Zoltan, this problem is often topic here. Solving this problem is a long boring process, where community will try to explain you basics about transactions and transaction markers meaning, ask you to send gstat statistics, then explain about record versioning and garbage and long-running transactions, about advanced monitoring and so on. In the end you will land with big3 - long-running transactions, bad plans and wrong configuration, each is another big topic . In short, your database size looks pretty serious to afford professional services http://www.firebirdsql.org/en/professional-support/ Regards, Alexey Kovyazin IBSurgeon PS If somebody here wants to go through steps described above, please don't hesitate to do this. Hi, I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores - the memory usage of the server is around 4-6 GB and the rest of the memory is used as OS cache - the disc queue lenghts almost never go over 0.3 with around 2-5 ms latency - there is almost no network activity on the server. Still, the slowdown seems to be linked to the general load on the server. During the night when no users are connected to the database/no background jobs are running a test query used for reference executes in 4-5 seconds while during the day when all the users are connected to the database executing the same reference query requires 60+ seconds to finish. It should be added though that the slowdown is general in nature, there are no specific queries that are slower while the server is under load, everything gets slower within the specific Firebird database. The server has other databases with a very low number of transactions executed daily and these other databases show no sign of slowdowns. I even created a copy of the live database experiencing slowdowns and executed the same query against both the originaly and the duplicate database - the original did execute the query slow and hte duplicate fast. The only difference between the original and the duplicate I know is the number of connected users/concurrent transactions. As I found no evident reasons of all these in the available OS resources so I tried to fetch statistics from Firebird. The observations: - at peak times the databse has 30-40 transactions running in parallel according to mon$statements (where mon$state == 1 which according to the archives means the transactions are running or are waiting for a lock) - fb_lock_print displays the following about the database: LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 2097152, Used: 1335440 Flags: 0x0001 Enqs: 9993237, Converts: 93191, Rejects: 1417230, Blocks: 2 Deadlock scans: 0, Deadlocks: 0, Scan interval: 10 Acquires: 19972846, Acquire blocks: 0, Spin count: 0 Mutex wait: 0.0% Hash slots: 1009, Hash lengths (min/avg/max): 0/ 2/ 7 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (38): forward: 20824, backward: 872088 Free owners (126): forward: 973360, backward: 728016 Free locks (370): forward: 852200, backward: 195936 Free requests (12425): forward: 614608, backward: 1230536 Lock Ordering: Enabled Here I noted that the rejects field accounts for ~14% of enqs field but unfortunately I don't know the exact meaning of these values. I guess about 14% of the lock requests are rejected for some reason but I might be completely wrong. So the questions: - How should the output of fb_lock_print interpreted in this case? Are these numbers wrong in some sense? Can they be improved by some parameter tuning? - What additional steps should be taken to pinpoint what causes the slowdowns? Thanks, Zoltan
Re: [firebird-support] Firebird slowdowns while resources are available
Hi Alexey, I've sent a mail directly to You about this particular case asking for personal assistance. Any suggestions or hints are welcome if someone else is willing to participate. Best Regards Zsolt 2014-02-11 9:58 GMT+01:00 Alexey Kovyazin a...@ib-aid.com: Hi Zoltan, this problem is often topic here. Solving this problem is a long boring process, where community will try to explain you basics about transactions and transaction markers meaning, ask you to send gstat statistics, then explain about record versioning and garbage and long-running transactions, about advanced monitoring and so on. In the end you will land with big3 - long-running transactions, bad plans and wrong configuration, each is another big topic . In short, your database size looks pretty serious to afford professional services http://www.firebirdsql.org/en/professional-support/ Regards, Alexey Kovyazin IBSurgeon PS If somebody here wants to go through steps described above, please don't hesitate to do this. Hi, I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores - the memory usage of the server is around 4-6 GB and the rest of the memory is used as OS cache - the disc queue lenghts almost never go over 0.3 with around 2-5 ms latency - there is almost no network activity on the server. Still, the slowdown seems to be linked to the general load on the server. During the night when no users are connected to the database/no background jobs are running a test query used for reference executes in 4-5 seconds while during the day when all the users are connected to the database executing the same reference query requires 60+ seconds to finish. It should be added though that the slowdown is general in nature, there are no specific queries that are slower while the server is under load, everything gets slower within the specific Firebird database. The server has other databases with a very low number of transactions executed daily and these other databases show no sign of slowdowns. I even created a copy of the live database experiencing slowdowns and executed the same query against both the originaly and the duplicate database - the original did execute the query slow and hte duplicate fast. The only difference between the original and the duplicate I know is the number of connected users/concurrent transactions. As I found no evident reasons of all these in the available OS resources so I tried to fetch statistics from Firebird. The observations: - at peak times the databse has 30-40 transactions running in parallel according to mon$statements (where mon$state == 1 which according to the archives means the transactions are running or are waiting for a lock) - fb_lock_print displays the following about the database: LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 2097152, Used: 1335440 Flags: 0x0001 Enqs: 9993237, Converts: 93191, Rejects: 1417230, Blocks: 2 Deadlock scans: 0, Deadlocks: 0, Scan interval: 10 Acquires: 19972846, Acquire blocks: 0, Spin count: 0 Mutex wait: 0.0% Hash slots: 1009, Hash lengths (min/avg/max): 0/ 2/ 7 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (38): forward: 20824, backward: 872088 Free owners (126): forward: 973360, backward: 728016 Free locks (370): forward: 852200, backward: 195936 Free requests (12425): forward: 614608, backward: 1230536 Lock Ordering: Enabled Here I noted that the rejects field accounts for ~14% of enqs field but unfortunately I don't know the exact meaning of these values. I guess about 14% of the lock requests are rejected for some reason but I might be completely wrong. So the questions: - How should the output of fb_lock_print interpreted in this case? Are these numbers wrong in some sense? Can they be improved by some parameter tuning? - What additional steps should be taken to pinpoint what causes the slowdowns? Thanks, Zoltan
[firebird-support] Firebird slowdowns while resources are available
- fb_lock_print displays the following about the database: LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 2097152, Used: 1335440 Flags: 0x0001 Enqs: 9993237, Converts: 93191, Rejects: 1417230, Blocks: 2 Deadlock scans: 0, Deadlocks: 0, Scan interval: 10 Acquires: 19972846, Acquire blocks: 0, Spin count: 0 Mutex wait: 0.0% Hash slots: 1009, Hash lengths (min/avg/max):0/ 2/ 7 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (38):forward: 20824, backward: 872088 Free owners (126): forward: 973360, backward: 728016 Free locks (370): forward: 852200, backward: 195936 Free requests (12425): forward: 614608, backward: 1230536 Lock Ordering: Enabled Here I noted that the rejects field accounts for ~14% of enqs field but unfortunately I don't know the exact meaning of these values. I guess about 14% of the lock requests are rejected for some reason but I might be completely wrong. So the questions: - How should the output of fb_lock_print interpreted in this case? Are these numbers wrong in some sense? Can they be improved by some parameter tuning? Rejects = Lock requests that cannot be satisfied no big deal No wait locks? Engine locks? I wouldn't worry about it. Looking at your Lock Header - it looks fine. Your problem is elsewhere. Paul Beach IBPhoenix
Re: [firebird-support] Firebird slowdowns while resources are available
Hi Alexey, Thanks for the answer. I'll look at our options regarding the professional services. Regards, Zoltan PS: I am familiar with database concepts like MVCC, transactions, locking and query execution planning but unfortunately I don't know how to investigate these mechanisms within Firebird :-( On Tue, Feb 11, 2014 at 9:58 AM, Alexey Kovyazin a...@ib-aid.com wrote: Hi Zoltan, this problem is often topic here. Solving this problem is a long boring process, where community will try to explain you basics about transactions and transaction markers meaning, ask you to send gstat statistics, then explain about record versioning and garbage and long-running transactions, about advanced monitoring and so on. In the end you will land with big3 - long-running transactions, bad plans and wrong configuration, each is another big topic . In short, your database size looks pretty serious to afford professional services http://www.firebirdsql.org/en/professional-support/ Regards, Alexey Kovyazin IBSurgeon PS If somebody here wants to go through steps described above, please don't hesitate to do this. Hi, I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores - the memory usage of the server is around 4-6 GB and the rest of the memory is used as OS cache - the disc queue lenghts almost never go over 0.3 with around 2-5 ms latency - there is almost no network activity on the server. Still, the slowdown seems to be linked to the general load on the server. During the night when no users are connected to the database/no background jobs are running a test query used for reference executes in 4-5 seconds while during the day when all the users are connected to the database executing the same reference query requires 60+ seconds to finish. It should be added though that the slowdown is general in nature, there are no specific queries that are slower while the server is under load, everything gets slower within the specific Firebird database. The server has other databases with a very low number of transactions executed daily and these other databases show no sign of slowdowns. I even created a copy of the live database experiencing slowdowns and executed the same query against both the originaly and the duplicate database - the original did execute the query slow and hte duplicate fast. The only difference between the original and the duplicate I know is the number of connected users/concurrent transactions. As I found no evident reasons of all these in the available OS resources so I tried to fetch statistics from Firebird. The observations: - at peak times the databse has 30-40 transactions running in parallel according to mon$statements (where mon$state == 1 which according to the archives means the transactions are running or are waiting for a lock) - fb_lock_print displays the following about the database: LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 2097152, Used: 1335440 Flags: 0x0001 Enqs: 9993237, Converts: 93191, Rejects: 1417230, Blocks: 2 Deadlock scans: 0, Deadlocks: 0, Scan interval: 10 Acquires: 19972846, Acquire blocks: 0, Spin count: 0 Mutex wait: 0.0% Hash slots: 1009, Hash lengths (min/avg/max): 0/ 2/ 7 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (38): forward: 20824, backward: 872088 Free owners (126): forward: 973360, backward: 728016 Free locks (370): forward: 852200, backward: 195936 Free requests (12425): forward: 614608, backward: 1230536 Lock Ordering: Enabled Here I noted that the rejects field accounts for ~14% of enqs field but unfortunately I don't know the exact meaning of these values. I guess about 14% of the lock requests are rejected for some reason but I might be completely wrong. So the questions: - How should the output of fb_lock_print interpreted in this case? Are these numbers wrong in some sense? Can they be improved by some parameter tuning? - What additional steps should be taken to pinpoint what causes the slowdowns? Thanks, Zoltan -- Zizi ...nálatok a cégnél múltból nagyon sok van...
Re: [firebird-support] Re: A question about rdb$character_set_id
2014-02-11 17:54 GMT+09:00 Dmitry Yemanov dim...@users.sourceforge.net: 11.02.2014 03:42, Ian Lawrence Barwick wrote: Is the integer value of the column rdb$character_set_id from the table rdb$character_sets constant between Firebird versions? E.g. will character set UTF8 always be 4 (and was it 4 in pre-2.5 versions)? Yes, these values remain constant. Purely theoretically, some different UTF8 incarnation may be introduced with a different ID to replace the old one (similar to what happened with UNICODE_FSS - UTF8), but ID=4 will still work as old UTF8. Excellent, that makes things much easier for me. Thanks for the confirmation! Regards Ian Barwick
Re: [firebird-support] Firebird slowdowns while resources are available
Hi Paul, Thanks for the answer. On Tue, Feb 11, 2014 at 11:05 AM, Paul Beach pabe...@waitrose.com wrote: - How should the output of fb_lock_print interpreted in this case? Are these numbers wrong in some sense? Can they be improved by some parameter tuning? Rejects = Lock requests that cannot be satisfied no big deal No wait locks? Engine locks? I wouldn't worry about it. Looking at your Lock Header - it looks fine. Your problem is elsewhere. Another thing I noticed since my original e-mail (and after reading a few chapters in a Firebird book) is that the gap between OIT and OAT can be farely large (500 000 +) during the slowdowns, though there is no linear or polynomial correlation between the refenrece query's execution time and the size of the gap: [image: Inline image 1] (blue is the execution time of the reference query (left side y axis), purple is the gap (right side y axis)) As I read, gap is not the cause of the slowness just an observable result of transactions that might result in slowness. So now I think this is the area that should be investigated further. It seems that to further analyze this I'll need some of the advanced Firebird tools. -- Zizi ...nálatok a cégnél múltból nagyon sok van... [Non-text portions of this message have been removed]
[firebird-support] Transaction statistics issue
I found a strange issue when sweep is executed. I'm using Firebird 2.5.2 Super Server for Linux AMD 64 bits. I first execute SHOW DATABASE in isql: PAGE_SIZE 16384 Number of DB pages allocated = 524480 Sweep interval = 2 Forced Writes are OFF Transaction - oldest = 26483174 Transaction - oldest active = 26483175 Transaction - oldest snapshot = 26483171 Transaction - Next = 26483367 Shortly after, I do a manual sweep to the same database. This lines appear in firebird.log: Sweep is started by SYSDBA Database path_to_databse.fdb OIT 26489872, OAT 26489873, OST 26489873, Next 140660205433875 As you can see, it seems to be a huge gap between next transaction and the other values. I guess that is not true, as just some minutes went on from measured points. Is it a normal behaviour? No its a bug http://tracker.firebirdsql.org/browse/CORE-3978 Regards Paul
[firebird-support] datetime trunc
Hi, Is there an equivalent function to trunc datetime fields in firebird ? Something like date_trunc in postgre [1] -- Maxi [1] http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
Re: [firebird-support] datetime trunc
EXTRACT()http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina maxi_roba...@yahoo.com.ar wrote: Hi, Is there an equivalent function to trunc datetime fields in firebird ? Something like date_trunc in postgre [1] -- Maxi [1] http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
Re: [firebird-support] Firebird slowdowns while resources are available
I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores With SuperServer on a single database? I doubt. The most obvious switch is to a SMP-capable architecture like SuperClassic or Classic. Then there is some general tuning/tweaking away from default settings necessary, but this all depends on your particular case ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Transaction statistics issue
I found a strange issue when sweep is executed. I'm using Firebird 2.5.2 Super Server for Linux AMD 64 bits. I first execute SHOW DATABASE in isql: PAGE_SIZE 16384 Number of DB pages allocated = 524480 Sweep interval = 2 Forced Writes are OFF Transaction - oldest = 26483174 Transaction - oldest active = 26483175 Transaction - oldest snapshot = 26483171 Transaction - Next = 26483367 Shortly after, I do a manual sweep to the same database. This lines appear in firebird.log: Sweep is started by SYSDBA Database path_to_databse.fdb OIT 26489872, OAT 26489873, OST 26489873, Next 140660205433875 As you can see, it seems to be a huge gap between next transaction and the other values. I guess that is not true, as just some minutes went on from measured points. Is it a normal behaviour? No, this is a known bug: http://tracker.firebirdsql.org/browse/CORE-3978-- Does this also apply to the output via the Trace API? Thanks! With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] datetime trunc
On 11-2-2014 16:06, Virna Constantin wrote:
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
:
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina
maxi_roba...@yahoo.com.ar wrote:
Hi,
Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]
Re: [firebird-support] Firebird slowdowns while resources are available
2.5 should behave pretty well on SMP systems, at least according to the release notes. Windows task manager shows that CPU load is distributed among the available cores. Am I missing something here? On Feb 11, 2014 8:15 PM, Thomas Steinmaurer t...@iblogmanager.com wrote: I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores With SuperServer on a single database? I doubt. The most obvious switch is to a SMP-capable architecture like SuperClassic or Classic. Then there is some general tuning/tweaking away from default settings necessary, but this all depends on your particular case ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Firebird slowdowns while resources are available
2.5 should behave pretty well on SMP systems, at least according to the release notes. Windows task manager shows that CPU load is distributed among the available cores. Am I missing something here? Then you are not using SuperServer, or SuperServer not serving a single database but several databases. What Firebird related processes do you see in the Windows Task Manager? - fbserver.exe - Single fb_inet_server.exe - Multiple fb_inet_server.exe -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. On Feb 11, 2014 8:15 PM, Thomas Steinmaurer t...@iblogmanager.com mailto:t...@iblogmanager.com wrote: __ I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores With SuperServer on a single database? I doubt. The most obvious switch is to a SMP-capable architecture like SuperClassic or Classic. Then there is some general tuning/tweaking away from default settings necessary, but this all depends on your particular case ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] datetime trunc
SELECT CAST(CURRENT_TIMESTAMP as DATE) || ' ' || EXTRACT(HOUR FROM
CURRENT_TIMESTAMP) || ':00:00' as date1
FROM RDB$DATABASE
Best Regard
| ISMAEL |
- Original Message -
From: Mark Rotteveel
To: firebird-support@yahoogroups.com
Sent: Tuesday, February 11, 2014 2:20 PM
Subject: Re: [firebird-support] datetime trunc
On 11-2-2014 16:06, Virna Constantin wrote:
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
:
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina
maxi_roba...@yahoo.com.ar wrote:
Hi,
Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]
Re: [firebird-support] Firebird slowdowns while resources are available
I have one fbserver.exe. I have multiple databases though, one with high load, the others with very low load . On Feb 11, 2014 8:34 PM, Thomas Steinmaurer t...@iblogmanager.com wrote: 2.5 should behave pretty well on SMP systems, at least according to the release notes. Windows task manager shows that CPU load is distributed among the available cores. Am I missing something here? Then you are not using SuperServer, or SuperServer not serving a single database but several databases. What Firebird related processes do you see in the Windows Task Manager? - fbserver.exe - Single fb_inet_server.exe - Multiple fb_inet_server.exe -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. On Feb 11, 2014 8:15 PM, Thomas Steinmaurer t...@iblogmanager.com mailto:t...@iblogmanager.com wrote: __ I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores With SuperServer on a single database? I doubt. The most obvious switch is to a SMP-capable architecture like SuperClassic or Classic. Then there is some general tuning/tweaking away from default settings necessary, but this all depends on your particular case ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
RE: [firebird-support] Firebird slowdowns while resources are available
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Mezei Zoltan Sent: Tuesday, February 11, 2014 3:14 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Firebird slowdowns while resources are available I have one fbserver.exe. I have multiple databases though, one with high load, the others with very low load . SL How many database connections, in total and per database would you have at any time.
Re: [firebird-support] datetime trunc
Cast the result of the extract as a date or timestamp.
On 12/02/2014 6:20 AM, Mark Rotteveel m...@lawinegevaar.nl wrote:
On 11-2-2014 16:06, Virna Constantin wrote:
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
:
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina
maxi_roba...@yahoo.com.ar wrote:
Hi,
Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo Groups Links
Re: [firebird-support] datetime trunc
On 11-2-2014 21:54, Alan McDonald wrote:
Cast the result of the extract as a date or timestamp.
That doesn't work and that is ok: I'd be surprised when casting the
result (= 20) of EXTRACT(hour from TIMESTAMP '2001-02-16 20:38:40')
would result in 2001-02-16 20:00:00, while the result (= 2001) of
EXTRACT(year FROM TIMESTAMP '2001-02-16 20:38:40') would result in
2001-01-01 00:00:00.
The TS asked for an equivalent function, there is none in Firebird,
although there is a workaround as demonstrated by Ismael.
On 12/02/2014 6:20 AM, Mark Rotteveel m...@lawinegevaar.nl
mailto:m...@lawinegevaar.nl wrote:
On 11-2-2014 16:06, Virna Constantin wrote:
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
:
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
--
Mark Rotteveel
Re: [firebird-support] Firebird slowdowns while resources are available
I have one fbserver.exe. I have multiple databases though, one with high load, the others with very low load . fbserver.exe = SuperServer (SS). With several databases, the CPU utilization makes sense then, even under SuperServer. But still, SuperClassic or Classic might even utilize the CPU environment more, even for a single database. With SS, you are roughly speaking bound to a single CPU/core per database, shared across all connections for that database. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. On Feb 11, 2014 8:34 PM, Thomas Steinmaurer t...@iblogmanager.com mailto:t...@iblogmanager.com wrote: __ 2.5 should behave pretty well on SMP systems, at least according to the release notes. Windows task manager shows that CPU load is distributed among the available cores. Am I missing something here? Then you are not using SuperServer, or SuperServer not serving a single database but several databases. What Firebird related processes do you see in the Windows Task Manager? - fbserver.exe - Single fb_inet_server.exe - Multiple fb_inet_server.exe -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. On Feb 11, 2014 8:15 PM, Thomas Steinmaurer t...@iblogmanager.com mailto:t...@iblogmanager.com mailto:t...@iblogmanager.com mailto:t...@iblogmanager.com wrote: __ I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment: - 64 bit Firebird 2.5.2 server running in SuperServer mode - the database is running on a 64 bit Windows 2008 R2 server OS - the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM - the database size is around 37 GB and the number of concurrent connections to the database is around 150. While observing the slowdowns: - the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores With SuperServer on a single database? I doubt. The most obvious switch is to a SMP-capable architecture like SuperClassic or Classic. Then there is some general tuning/tweaking away from default settings necessary, but this all depends on your particular case ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Firebird slowdowns while resources are available
On Tue, Feb 11, 2014 at 5:52 AM, Mezei Zoltan mezei.zol...@gmail.comwrote: Rejects = Lock requests that cannot be satisfied no big deal \ Slightly more specifically, (IIRC) they're requests to convert a lock from read to write without releasing the read lock. That happens to prevent deadlocks. A high reject rate is consistent with high contention for pages. Cheers, Ann
[firebird-support] Re: Transaction statistics issue
11.02.2014 23:11, Thomas Steinmaurer пишет: Does this also apply to the output via the Trace API? AFAIR, no. Dmitry
