RE: [firebird-support] performance issue with firebird 3.0 embedded on linux
I was trying to use dedicated connection for each thread, and it seems a little better than one connection for all the threads, but not much, the performance line is still going down hill from single thread to multiple threads, with SuperClassic or Classic. I did not try Super mode before, since my project might have multiple processes open a same database.. but anyway I had a try this time too. it looks better than Classic/SuperClassic, going up from 5 (single thread) to almost 10 tx/sec (12 threads). My project used Ese database at Windows side, the performance is pretty close to what you tested with Firebird. I got around 28 tx/sec with 8 threads. Have you got chance to run your test on Linux? To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Sat, 18 Jun 2016 10:40:04 +0200 Subject: Re: [firebird-support] performance issue with firebird 3.0 embedded on linux On 15-6-2016 17:55, chen hsu chen...@outlook.com [firebird-support] wrote: > Thanks, Helen. Please see my replies inline. > > I am sure it is not 3.0 specific, 2.5 is the same. and the main issue is > scalability, sequential transaction performance is actually pretty good, > comparable to ESE store on windows I was comparing firebird against. > but when running multiple transactions in parallel, ESE perf increased > from 4 tx/sec to 26 tx/sec (128 byte/record, 200 records per > transaction, 6 transaction threads on 6 cores, which I expect the best, > logical cpu/ht is not very useful performance-wise), while firebird > decreased. These transactions do not write-conflict with each other. > > The basic flow of our code is like, first the main function creates a > database connection, and spawns multiple threads, passing in the > database connection. Each thread does this: create a transaction using > the db connection, do insertion of data, and commit the transaction. > Each thread measures its own elapsed time. I did a similar test against FB 3 on Windows (against localhost, 1000 tx per thread, 100 inserts per tx, using a single prepared statement), and the difference in results are quite dramatic. Using super server and 8 threads (I have a quad core with 8 logical processors), I get +/- 12.7 transactions per second per thread (or 101 tx per second). With classic I get +/- 3.4 transactions per second per thread (or 27 tx per second). With superclassic I get +/- 3.8 transactions per second per thread (or 30 tx per second). I used a table with a bigint primary key (generated identity) and char(128) character set octets. Removing the primary key column increase throughput for super server by +/- 40%, for classic server only 17%, for superclassic only 15%. Mark -- Mark Rotteveel
Re: [firebird-support] performance issue with firebird 3.0 embedded on linux
On 15-6-2016 17:55, chen hsu chen...@outlook.com [firebird-support] wrote: > Thanks, Helen. Please see my replies inline. > > I am sure it is not 3.0 specific, 2.5 is the same. and the main issue is > scalability, sequential transaction performance is actually pretty good, > comparable to ESE store on windows I was comparing firebird against. > but when running multiple transactions in parallel, ESE perf increased > from 4 tx/sec to 26 tx/sec (128 byte/record, 200 records per > transaction, 6 transaction threads on 6 cores, which I expect the best, > logical cpu/ht is not very useful performance-wise), while firebird > decreased. These transactions do not write-conflict with each other. > > The basic flow of our code is like, first the main function creates a > database connection, and spawns multiple threads, passing in the > database connection. Each thread does this: create a transaction using > the db connection, do insertion of data, and commit the transaction. > Each thread measures its own elapsed time. I did a similar test against FB 3 on Windows (against localhost, 1000 tx per thread, 100 inserts per tx, using a single prepared statement), and the difference in results are quite dramatic. Using super server and 8 threads (I have a quad core with 8 logical processors), I get +/- 12.7 transactions per second per thread (or 101 tx per second). With classic I get +/- 3.4 transactions per second per thread (or 27 tx per second). With superclassic I get +/- 3.8 transactions per second per thread (or 30 tx per second). I used a table with a bigint primary key (generated identity) and char(128) character set octets. Removing the primary key column increase throughput for super server by +/- 40%, for classic server only 17%, for superclassic only 15%. Mark -- Mark Rotteveel
RE: [firebird-support] performance issue with firebird 3.0 embedded on linux
I see. That might be the cause that I did not see throughput increase by adding more threads. let me try to have each thread create its own dedicated connection. To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Fri, 17 Jun 2016 12:11:03 -0700 Subject: RE: [firebird-support] performance issue with firebird 3.0 embedded on linux ---In firebird-support@yahoogroups.com, wrote : > yes, one connection per database, any thread needs to access this database > would > create its own transaction. Then you have serialization at connection level. Both engine and remote layer doesn't allow to run more than one thread per connection at the same time. Regards, Vlad
RE: [firebird-support] performance issue with firebird 3.0 embedded on linux
---In firebird-support@yahoogroups.com, wrote : > yes, one connection per database, any thread needs to access this database > would > create its own transaction. Then you have serialization at connection level. Both engine and remote layer doesn't allow to run more than one thread per connection at the same time. Regards, Vlad
RE: [firebird-support] performance issue with firebird 3.0 embedded on linux
yes, one connection per database, any thread needs to access this database would create its own transaction. To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Wed, 15 Jun 2016 21:38:48 +0200 Subject: Re: [firebird-support] performance issue with firebird 3.0 embedded on linux Hi, do you mean that you share one connection between multiple threads? regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Wednesday, June 15, 2016 5:55 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] performance issue with firebird 3.0 embedded on linux Thanks, Helen. Please see my replies inline. I am sure it is not 3.0 specific, 2.5 is the same. and the main issue is scalability, sequential transaction performance is actually pretty good, comparable to ESE store on windows I was comparing firebird against. but when running multiple transactions in parallel, ESE perf increased from 4 tx/sec to 26 tx/sec (128 byte/record, 200 records per transaction, 6 transaction threads on 6 cores, which I expect the best, logical cpu/ht is not very useful performance-wise), while firebird decreased. These transactions do not write-conflict with each other. The basic flow of our code is like, first the main function creates a database connection, and spawns multiple threads, passing in the database connection. Each thread does this: create a transaction using the db connection, do insertion of data, and commit the transaction. Each thread measures its own elapsed time.&nb sp; To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Wed, 15 Jun 2016 19:04:33 +1200 Subject: Re: [firebird-support] performance issue with firebird 3.0 embedded on linux Wednesday, June 15, 2016, 5:58:39 PM, Karol B. wrote: > test without details say nothing to me > 1. Did you compare results with e.g. FB2.5 in on the same maschine with same configuration (FBConfig) My project migrated from FB2.5 to FB3.0. I am pretty sure it is not 3.0 specific. > 2. What is your page size and type of HDD? SSD. 512GB Samsung EVO 850. The file system is ext4, block size 4096. > 3. Do you have BOST feature enabled on CPU and HT? -- Boost? Yes, Turbo boost and hyper threading are enabled. > 4. Did you compare results on GLOBAL TEMPORARY TABLE - i know this is not the same but can show some info? Could you give me some details about how to use GLOBAL TEMPORARY TABLE? > 5. How do you test this times about your threads and how do you start them? I used the monolithic time in code i.e. clock_gettime(CLOCK_MONOTONIC, &ts) on each thread to calculate the elapsed time. should be high resolution. > 6. Exact version of FB Firebird-3.0.0.32483-0 and 7. If on Windows, how did you go about configuring CpuAffinityMask? - which processors did you intend to set it for and what was the value you configured? It's on Linux, I read from document that multiple processors are by default enabled. but I anyway set CpuAffinityMask to 4095, but it seems no change to performance. - did you try to do this configuration whilst an engine instance was running, without restarting? No. each time I change configuration, I restart the program. HB
Re: [firebird-support] performance issue with firebird 3.0 embedded on linux
Hi, do you mean that you share one connection between multiple threads? regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Wednesday, June 15, 2016 5:55 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] performance issue with firebird 3.0 embedded on linux Thanks, Helen. Please see my replies inline. I am sure it is not 3.0 specific, 2.5 is the same. and the main issue is scalability, sequential transaction performance is actually pretty good, comparable to ESE store on windows I was comparing firebird against. but when running multiple transactions in parallel, ESE perf increased from 4 tx/sec to 26 tx/sec (128 byte/record, 200 records per transaction, 6 transaction threads on 6 cores, which I expect the best, logical cpu/ht is not very useful performance-wise), while firebird decreased. These transactions do not write-conflict with each other. The basic flow of our code is like, first the main function creates a database connection, and spawns multiple threads, passing in the database connection. Each thread does this: create a transaction using the db connection, do insertion of data, and commit the transaction. Each thread measures its own elapsed time.&nb sp; To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Wed, 15 Jun 2016 19:04:33 +1200 Subject: Re: [firebird-support] performance issue with firebird 3.0 embedded on linux Wednesday, June 15, 2016, 5:58:39 PM, Karol B. wrote: > test without details say nothing to me > 1. Did you compare results with e.g. FB2.5 in on the same maschine with same > configuration (FBConfig) My project migrated from FB2.5 to FB3.0. I am pretty sure it is not 3.0 specific. > 2. What is your page size and type of HDD? SSD. 512GB Samsung EVO 850. The file system is ext4, block size 4096. > 3. Do you have BOST feature enabled on CPU and HT? -- Boost? Yes, Turbo boost and hyper threading are enabled. > 4. Did you compare results on GLOBAL TEMPORARY TABLE - i know this is not the > same but can show some info? Could you give me some details about how to use GLOBAL TEMPORARY TABLE? > 5. How do you test this times about your threads and how do you start them? I used the monolithic time in code i.e. clock_gettime(CLOCK_MONOTONIC, &ts) on each thread to calculate the elapsed time. should be high resolution. > 6. Exact version of FB Firebird-3.0.0.32483-0 and 7. If on Windows, how did you go about configuring CpuAffinityMask? - which processors did you intend to set it for and what was the value you configured? It's on Linux, I read from document that multiple processors are by default enabled. but I anyway set CpuAffinityMask to 4095, but it seems no change to performance. - did you try to do this configuration whilst an engine instance was running, without restarting? No. each time I change configuration, I restart the program. HB
RE: [firebird-support] performance issue with firebird 3.0 embedded on linux
Thanks, Helen. Please see my replies inline. I am sure it is not 3.0 specific, 2.5 is the same. and the main issue is scalability, sequential transaction performance is actually pretty good, comparable to ESE store on windows I was comparing firebird against. but when running multiple transactions in parallel, ESE perf increased from 4 tx/sec to 26 tx/sec (128 byte/record, 200 records per transaction, 6 transaction threads on 6 cores, which I expect the best, logical cpu/ht is not very useful performance-wise), while firebird decreased. These transactions do not write-conflict with each other. The basic flow of our code is like, first the main function creates a database connection, and spawns multiple threads, passing in the database connection. Each thread does this: create a transaction using the db connection, do insertion of data, and commit the transaction. Each thread measures its own elapsed time. To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Wed, 15 Jun 2016 19:04:33 +1200 Subject: Re: [firebird-support] performance issue with firebird 3.0 embedded on linux Wednesday, June 15, 2016, 5:58:39 PM, Karol B. wrote: > test without details say nothing to me > 1. Did you compare results with e.g. FB2.5 in on the same maschine with same > configuration (FBConfig) My project migrated from FB2.5 to FB3.0. I am pretty sure it is not 3.0 specific. > 2. What is your page size and type of HDD? SSD. 512GB Samsung EVO 850. The file system is ext4, block size 4096. > 3. Do you have BOST feature enabled on CPU and HT? -- Boost? Yes, Turbo boost and hyper threading are enabled. > 4. Did you compare results on GLOBAL TEMPORARY TABLE - i know this is not the > same but can show some info? Could you give me some details about how to use GLOBAL TEMPORARY TABLE? > 5. How do you test this times about your threads and how do you start them? I used the monolithic time in code i.e. clock_gettime(CLOCK_MONOTONIC, &ts) on each thread to calculate the elapsed time. should be high resolution. > 6. Exact version of FB Firebird-3.0.0.32483-0 and 7. If on Windows, how did you go about configuring CpuAffinityMask? - which processors did you intend to set it for and what was the value you configured? It's on Linux, I read from document that multiple processors are by default enabled. but I anyway set CpuAffinityMask to 4095, but it seems no change to performance. - did you try to do this configuration whilst an engine instance was running, without restarting? No. each time I change configuration, I restart the program. HB
Re: [firebird-support] performance issue with firebird 3.0 embedded on linux
Wednesday, June 15, 2016, 5:58:39 PM, Karol B. wrote: > test without details say nothing to me > 1. Did you compare results with e.g. FB2.5 in on the same maschine with same > configuration (FBConfig) > 2. What is your page size and type of HDD? > 3. Do you have BOST feature enabled on CPU and HT? -- Boost? > 4. Did you compare results on GLOBAL TEMPORARY TABLE - i know this is not the > same but can show some info? > 5. How do you test this times about your threads and how do you start them? > 6. Exact version of FB and 7. If on Windows, how did you go about configuring CpuAffinityMask? - which processors did you intend to set it for and what was the value you configured? - did you try to do this configuration whilst an engine instance was running, without restarting? HB
Re: [firebird-support] performance issue with firebird 3.0 embedded on linux
Hi, test without details say nothing to me 1. Did you compare results with e.g. FB2.5 in on the same maschine with same configuration (FBConfig) 2. What is your page size and type of HDD? 3. Do you have BOST feature enabled on CPU and HT? 4. Did you compare results on GLOBAL TEMPORARY TABLE - i know this is not the same but can show some info? 5. How do you test this times about your threads and how do you start them? 6. Exact version of FB regards, Karol Bieniaszewski W dniu 2016-06-15 01:44:55 użytkownik chen hsu chen...@outlook.com [firebird-support] napisał: Hi there, Recently I am doing some performance test on firebird 3.0 embedded used in my project. The test is pretty straightforward, on one same table, use 100 insertions per transaction as base test unit (creating a transaction, using a loop to insert 100 records, and committing the transaction), and each test unit is executed on a separate thread. I was trying to see the scalability of firebird with regards to multiple CPUs (each runs a base test unit). But the result is pretty strange, like this: with one cpu, 200 insertion per transaction, 1 transaction in total. each record is 128 bytes. * Finish Insert: total=25600 bytes elapsed=00.244 throughput=104918.032787 bytes/sec, 819.672131 ops/sec, 4.098361 tx/sec succeeded=1 with 2 cpus, 200 insertion per transaction, 2 transaction in total. each record is 128 bytes. * Fi nish Insert: total=51200 bytes elapsed=01.807 throughput=28334.255672 bytes/sec, 221.361372 ops/sec, 1.106807 tx/sec succeeded=2 with 3 cpus, 200 insertion per transaction, 3 transaction in total. each record is 128 bytes. * Finish Insert: total=76800 bytes elapsed=02.377 throughput=32309.633992 bytes/sec, 252.419016 ops/sec, 1.262095 tx/sec succeeded=3 with 4 cpus, 200 insertion per transaction, 4 transaction in total. each record is 128 bytes. * Finish Insert: total=102400 bytes elapsed=05.383 throughput=19022.849712 bytes/sec, 148.616013 ops/sec, 0.743080 tx/sec succeeded=4 with 5 cpus, 200 insertion per transaction, 5 transaction in total. each record is 128 bytes. * Finish Insert: total=128000 bytes elapsed=06.428 throughput=19912.881145 bytes/sec, 155.569384 ops/sec, 0.777847 tx/sec succeeded=5 with 6 cpus, 200 insertion per transaction, 6 transaction in total. each record is 128 bytes. * Finish Insert: total=153600 bytes elapsed=07.655 throughput=20065.316786 bytes/sec, 156.760287 ops/sec, 0.783801 tx/sec succeeded=6 The machine has a 6-core i7 processor. it looks starting from 2 parallel transactions (on 2 CPUs), the performance dropped dramatically from 4 tx/sec to 1.1 ~ 0.7 tx/sec. I tried Super or SuperClassic service configuration, still the same pattern. I am wondering if I missed something in configuration, or it is the known behavior of firebird? Any advice is welcome! Thanks a lot. -chen
