-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello,
I have read all related threads about performance problems with multi
core systems but still have no idea what to do to make thinks better.
Below are results of testing postgresql on HP DL380G5 using sysbench.
The results are comparable to:
http://blog.insidesystems.net/articles/2007/04/11/postgresql-scaling-on-6-2-and-7-0
but the same tests running on the same hardware using Linux (kernel
2.6.18-53.1.4.el5 SMP x86_64) are very different.
PostgreSQL is tuned equal.
dmesg:
...
CPU: Intel(R) Xeon(R) CPU X5450 @ 3.00GHz (3000.02-MHz
K8-class CPU)
Origin = "GenuineIntel" Id = 0x10676 Stepping = 6
Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
Features2=0xce3bd<SSE3,RSVD2,MON,DS_CPL,VMX,EST,TM2,SSSE3,CX16,xTPR,PDCM,DCA,<b19>>
AMD Features=0x20000800<SYSCALL,LM>
AMD Features2=0x1<LAHF>
Cores per package: 4
usable memory = 8575655936 (8178 MB)
avail memory = 8288337920 (7904 MB)
ACPI APIC Table: <HP ProLiant>
FreeBSD/SMP: Multiprocessor System Detected: 8 CPUs
...
test:
sysbench --num-threads=${i} --test=oltp --pgsql-user=bench
- --pgsql-db=bench --db-driver=pgsql --max-time=60 --max-requests=0
- --oltp-read-only=on run
tuning:
kern.ipc.shmmax=2147483647
kern.ipc.shmall=524288
kern.ipc.semmsl=512
kern.ipc.semmap=256
kern.ipc.somaxconn=2048
kern.maxfiles=65536
vfs.read_max=32
kern.ipc.semmni=256
kern.ipc.semmns=2048
results:
FreeBSD 7.0-BETA4 amd64 (cvsup on 20.12) GENERIC with SCHED_ULE
#threads #transactions/sec user/system
1 500 7.4%,5.3%
5 1990 30.9%,23.4%
10 2510 39.9%,35.0%
20 2549 44.5%,43.5%
40 1921 29.8%,59.4%
60 1580 22.7%,70.6%
80 1341 18.9%,75.9%
100 1227 16.5%,79.3%
Linux
#threads #transactions/sec
1 693
5 3539
10 5789
20 5791
40 5661
60 5517
80 5401
100 5319
What can be done to improve these results?
Best Regards
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (FreeBSD)
iD8DBQFHal7hxJBWvpalMpkRAsaiAJ9G3ZoTv6cUbR4yix07TEMf7PKm7gCgoroM
+xvcXkcaFjSTxYfjk5rqMko=Tpsq
-----END PGP SIGNATURE-----
> I have read all related threads about performance problems with multi > core systems but still have no idea what to do to make thinks better. > Below are results of testing postgresql on HP DL380G5 using sysbench. > The results are comparable to: > http://blog.insidesystems.net/articles/2007/04/11/postgresql-scaling-on-6-2-and-7-0 > but the same tests running on the same hardware using Linux (kernel > 2.6.18-53.1.4.el5 SMP x86_64) are very different. > PostgreSQL is tuned equal. > > results: > FreeBSD 7.0-BETA4 amd64 (cvsup on 20.12) GENERIC with SCHED_ULE > #threads #transactions/sec user/system > 1 500 7.4%,5.3% > 5 1990 30.9%,23.4% > 10 2510 39.9%,35.0% > 20 2549 44.5%,43.5% > 40 1921 29.8%,59.4% > 60 1580 22.7%,70.6% > 80 1341 18.9%,75.9% > 100 1227 16.5%,79.3% > > Linux > #threads #transactions/sec > 1 693 > 5 3539 > 10 5789 > 20 5791 > 40 5661 > 60 5517 > 80 5401 > 100 5319 > > What can be done to improve these results?What postgres-version did you use for this benchmark? Eventhough this is a synthetic benchmark the difference in performance may indicate some penalties on 8-core servers on FreeBSD. According to http://people.freebsd.org/~kris/scaling/mysql.html mysql scale the same until until 8 clients on both Linux and FreeBSD. This is an older test though and Linux has probably done some optimizations. Could be interesting so see whether the results differ if you disable one of the cpu's and rerun the tests. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Krassimir Slavchev wrote:> Hello, > > I have read all related threads about performance problems with multi > core systems but still have no idea what to do to make thinks better. > Below are results of testing postgresql on HP DL380G5 using sysbench. > The results are comparable to: > http://blog.insidesystems.net/articles/2007/04/11/postgresql-scaling-on-6-2-and-7-0 > but the same tests running on the same hardware using Linux (kernel > 2.6.18-53.1.4.el5 SMP x86_64) are very different. > PostgreSQL is tuned equal. > > dmesg: > ... > CPU: Intel(R) Xeon(R) CPU X5450 @ 3.00GHz (3000.02-MHz > K8-class CPU) > Origin = "GenuineIntel" Id = 0x10676 Stepping = 6 > > Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE> > > Features2=0xce3bd<SSE3,RSVD2,MON,DS_CPL,VMX,EST,TM2,SSSE3,CX16,xTPR,PDCM,DCA,<b19>> > AMD Features=0x20000800<SYSCALL,LM> > AMD Features2=0x1<LAHF> > Cores per package: 4 > usable memory = 8575655936 (8178 MB) > avail memory = 8288337920 (7904 MB) > ACPI APIC Table: <HP ProLiant> > FreeBSD/SMP: Multiprocessor System Detected: 8 CPUs > ... > > test: > sysbench --num-threads=${i} --test=oltp --pgsql-user=bench > --pgsql-db=bench --db-driver=pgsql --max-time=60 --max-requests=0 > --oltp-read-only=on run > > tuning: > kern.ipc.shmmax=2147483647 > kern.ipc.shmall=524288 > kern.ipc.semmsl=512 > kern.ipc.semmap=256 > kern.ipc.somaxconn=2048 > kern.maxfiles=65536 > vfs.read_max=32 > > kern.ipc.semmni=256 > kern.ipc.semmns=2048 > > results: > FreeBSD 7.0-BETA4 amd64 (cvsup on 20.12) GENERIC with SCHED_ULE > #threads #transactions/sec user/system > 1 500 7.4%,5.3% > 5 1990 30.9%,23.4% > 10 2510 39.9%,35.0% > 20 2549 44.5%,43.5% > 40 1921 29.8%,59.4% > 60 1580 22.7%,70.6% > 80 1341 18.9%,75.9% > 100 1227 16.5%,79.3%With kern.hz=100 #threads #transactions/sec 1 553 5 2133 10 2168 20 2567 40 2279 60 1773 80 1529 100 1463> > > What can be done to improve these results? > > Best Regards >_______________________________________________ freebsd-stable@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-stable To unsubscribe, send any mail to "freebsd-stable-unsubscribe@freebsd.org" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (FreeBSD) iD8DBQFHa5gaxJBWvpalMpkRAgjFAJ9Yqz7Yj6ee/z7FUQkDncRuetAwTwCfSBXY h66FOS97LRGr/VmAmdQcJKQ=jEDa -----END PGP SIGNATURE-----
Are you sure that the settitle call is disabled on FreeBSD? On 12/20/07, Krassimir Slavchev <krassi@bulinfo.net> wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hello, > > I have read all related threads about performance problems with multi > core systems but still have no idea what to do to make thinks better. > Below are results of testing postgresql on HP DL380G5 using sysbench. > The results are comparable to: > http://blog.insidesystems.net/articles/2007/04/11/postgresql-scaling-on-6-2-and-7-0 > but the same tests running on the same hardware using Linux (kernel > 2.6.18-53.1.4.el5 SMP x86_64) are very different. > PostgreSQL is tuned equal. > > dmesg: > ... > CPU: Intel(R) Xeon(R) CPU X5450 @ 3.00GHz (3000.02-MHz > K8-class CPU) > Origin = "GenuineIntel" Id = 0x10676 Stepping = 6 > > Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE> > > Features2=0xce3bd<SSE3,RSVD2,MON,DS_CPL,VMX,EST,TM2,SSSE3,CX16,xTPR,PDCM,DCA,<b19>> > AMD Features=0x20000800<SYSCALL,LM> > AMD Features2=0x1<LAHF> > Cores per package: 4 > usable memory = 8575655936 (8178 MB) > avail memory = 8288337920 (7904 MB) > ACPI APIC Table: <HP ProLiant> > FreeBSD/SMP: Multiprocessor System Detected: 8 CPUs > ... > > test: > sysbench --num-threads=${i} --test=oltp --pgsql-user=bench > - --pgsql-db=bench --db-driver=pgsql --max-time=60 --max-requests=0 > - --oltp-read-only=on run > > tuning: > kern.ipc.shmmax=2147483647 > kern.ipc.shmall=524288 > kern.ipc.semmsl=512 > kern.ipc.semmap=256 > kern.ipc.somaxconn=2048 > kern.maxfiles=65536 > vfs.read_max=32 > > kern.ipc.semmni=256 > kern.ipc.semmns=2048 > > results: > FreeBSD 7.0-BETA4 amd64 (cvsup on 20.12) GENERIC with SCHED_ULE > #threads #transactions/sec user/system > 1 500 7.4%,5.3% > 5 1990 30.9%,23.4% > 10 2510 39.9%,35.0% > 20 2549 44.5%,43.5% > 40 1921 29.8%,59.4% > 60 1580 22.7%,70.6% > 80 1341 18.9%,75.9% > 100 1227 16.5%,79.3% > > Linux > #threads #transactions/sec > 1 693 > 5 3539 > 10 5789 > 20 5791 > 40 5661 > 60 5517 > 80 5401 > 100 5319 > > > What can be done to improve these results? > > Best Regards > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.7 (FreeBSD) > > iD8DBQFHal7hxJBWvpalMpkRAsaiAJ9G3ZoTv6cUbR4yix07TEMf7PKm7gCgoroM > +xvcXkcaFjSTxYfjk5rqMko> =Tpsq > -----END PGP SIGNATURE----- > _______________________________________________ > freebsd-stable@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-stable > To unsubscribe, send any mail to "freebsd-stable-unsubscribe@freebsd.org" >
Krassimir Slavchev wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hello, > > I have read all related threads about performance problems with multi > core systems but still have no idea what to do to make thinks better. > Below are results of testing postgresql on HP DL380G5 using sysbench. > The results are comparable to: > http://blog.insidesystems.net/articles/2007/04/11/postgresql-scaling-on-6-2-and-7-0 > but the same tests running on the same hardware using Linux (kernel > 2.6.18-53.1.4.el5 SMP x86_64) are very different. > PostgreSQL is tuned equal. > > dmesg: > ... > CPU: Intel(R) Xeon(R) CPU X5450 @ 3.00GHz (3000.02-MHz > K8-class CPU) > Origin = "GenuineIntel" Id = 0x10676 Stepping = 6 > > Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE> > > Features2=0xce3bd<SSE3,RSVD2,MON,DS_CPL,VMX,EST,TM2,SSSE3,CX16,xTPR,PDCM,DCA,<b19>> > AMD Features=0x20000800<SYSCALL,LM> > AMD Features2=0x1<LAHF> > Cores per package: 4 > usable memory = 8575655936 (8178 MB) > avail memory = 8288337920 (7904 MB) > ACPI APIC Table: <HP ProLiant> > FreeBSD/SMP: Multiprocessor System Detected: 8 CPUs > ... > > test: > sysbench --num-threads=${i} --test=oltp --pgsql-user=bench > - --pgsql-db=bench --db-driver=pgsql --max-time=60 --max-requests=0 > - --oltp-read-only=on run > > tuning: > kern.ipc.shmmax=2147483647 > kern.ipc.shmall=524288 > kern.ipc.semmsl=512 > kern.ipc.semmap=256 > kern.ipc.somaxconn=2048 > kern.maxfiles=65536 > vfs.read_max=32 > > kern.ipc.semmni=256 > kern.ipc.semmns=2048 > > results: > FreeBSD 7.0-BETA4 amd64 (cvsup on 20.12) GENERIC with SCHED_ULE > #threads #transactions/sec user/system > 1 500 7.4%,5.3% > 5 1990 30.9%,23.4% > 10 2510 39.9%,35.0% > 20 2549 44.5%,43.5% > 40 1921 29.8%,59.4% > 60 1580 22.7%,70.6% > 80 1341 18.9%,75.9% > 100 1227 16.5%,79.3% > > Linux > #threads #transactions/sec > 1 693 > 5 3539 > 10 5789 > 20 5791 > 40 5661 > 60 5517 > 80 5401 > 100 5319 > > > What can be done to improve these results? > > Best Regards > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.7 (FreeBSD) > > iD8DBQFHal7hxJBWvpalMpkRAsaiAJ9G3ZoTv6cUbR4yix07TEMf7PKm7gCgoroM > +xvcXkcaFjSTxYfjk5rqMko> =Tpsq > -----END PGP SIGNATURE----- > _______________________________________________ > freebsd-stable@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-stable > To unsubscribe, send any mail to "freebsd-stable-unsubscribe@freebsd.org" > > . >postgresql has some poor default settings on FreeBSD. You need to add: stats_command_string = off update_process_title = off Kris
Krassimir Slavchev wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hello Kris, > > Here is the lock profiling results, see the attachment. > > Please, let me know if you want ssh access to this machine?Thanks, this is very interesting. The problem is already fixed in 8.0 but we were not seeing it being this much of a factor on our test hardware. Possibly it is due to your CPUs being moderately faster and causing a timing difference. Anyway, try this patch. If there is still a performance deficit then repeating the lock profiling will be useful. Kris
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, Kris Kennaway wrote:> Krassimir Slavchev wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> Hello, >> >> Here are lock profiling results with select patch applied. > > OK, you are doing I/O over TCP. Are you sure you are using TCP on both > systems? Linux may not be defaulting to TCP transport for local queries. > > Add --pgsql-host="" to your sysbench command line to make it communicate > over a local domain socket, which is much more efficient. > > Kris >Hmm, Yes linux uses local domain sockets! Here are results using local domain sockets on FreeBSD too: #threads #tranzactions/sec 1 728 5 2996 10 5301 20 3931 40 2466 60 1852 80 1424 100 1216 Just to remember: Linux (2.6.18) #threads #transactions/sec 1 693 5 3539 10 5789 20 5791 40 5661 60 5517 80 5401 100 5319 I have results using Fedora 8 on the same hardware: Linux (2.6.23) #threads #transactions/sec 1 740 5 2675 10 6486 20 6893 40 6623 60 6623 80 6522 100 6417 If we look at the results with up to 10 threads the performance of FreeBSD is very good. May be something can be tuned for number of threads > number of CPUs? Are you interested in lock profiling statistics with more threads than the number of CPUs? Best Regards -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (FreeBSD) iD8DBQFHhMCxxJBWvpalMpkRAvuFAJ4+ab5o4DoLZXrJ3hrr6vbJG9veTgCfdjn5 d8kf8v17QhiYSn20yDgWl3E=Tsub -----END PGP SIGNATURE-----
> >> I have read all related threads about performance problems with multi > >> core systems but still have no idea what to do to make thinks better. > >> Below are results of testing postgresql on HP DL380G5 using sysbench. > >> The results are comparable to: > >> http://blog.insidesystems.net/articles/2007/04/11/postgresql-scaling-on-6-2-and-7-0 > >> > >> but the same tests running on the same hardware using Linux (kernel > >> 2.6.18-53.1.4.el5 SMP x86_64) are very different. > >> PostgreSQL is tuned equal. > > Just to summarize some discussion we had off-list, this problem is now > resolved. It turned out to have two causes: > > 1) sysbench on linux was defaulting to using a unix domain socket to > communicate with pgsql, but FreeBSD was using TCP to 127.0.0.1. TCP has > much more overhead so performance was lower. Using --pgsql-host="" in > sysbench is the fix for this. > > 2) pgsql was not compiled with thread support enabled, which caused lots > of bad interactions with the threaded sysbench client. This probably > would have caused data corruption too (silent in this case because > nothing was checking the query responses). The fix was to recompile the > pgsql client and server with the THREADSAFE option enabled. > > Krassimir reports that with these two fixes, the standard 7.0 kernel has > performance: > > #threads transactions/sec > 1 755 > 8 7129 > 40 6580 > 100 6768 > > compared to Linux: > > Linux (2.6.18) > #threads #transactions/sec > 1 693 > 5 3539 > 10 5789 > 20 5791 > 40 5661 > 60 5517 > 80 5401 > 100 5319 > > Linux (2.6.23) > #threads #transactions/sec > 1 740 > 5 2675 > 10 6486 > 20 6893 > 40 6623 > 60 6623 > 80 6522 > 100 6417 > > I think this is a satisfactory resolution :)Thank you so much! (both of you and those who helped along the way) :-) On monday I will start testing a setup where we are moving from four 10K rpm sas disk in raid 1+0 to eight 15K rpm sas disks in raid 1+0 as well. The former is a ciss p400 controller with 512 MB bbc and the latter is a p800 with 512 MB bbc and an external msa-box. I will test with 7.0 and postgresql 8.2 from ports. Should I test with stable or release? Are there any patches I can apply? Our current db-server is a DL380 G5 woodcrest at 3 Ghz and my test-server will be a 8-core DL360 G5 at 2.4 Ghz (I think). I will log the queries from our live-server and repeat them on the test-server, use pgbench on FreeBSD, Solaris and Linux. I was getting a bit worried when the number varied so much between Linux and FreeBSD. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare
At 12:15 PM 1/11/2008, Kris Kennaway wrote:>Just to summarize some discussion we had off-list, this problem is >now resolved. It turned out to have two causes: > >1) sysbench on linux was defaulting to using a unix domain socket to >communicate with pgsql, but FreeBSD was using TCP to 127.0.0.1. TCP >has much more overhead so performance was lower. Using >--pgsql-host="" in sysbench is the fix for this. > >2) pgsql was not compiled with thread support enabled, which caused >lots of bad interactions with the threaded sysbench client. This >probably would have caused data corruption too (silent in this case >because nothing was checking the query responses). The fix was to >recompile the pgsql client and server with the THREADSAFE option enabled.Thats great news! Just for the record, for build options are there any other options that need to be set other than # diff -u Makefile.default Makefile --- Makefile.default 2008-01-11 20:13:06.000000000 -0500 +++ Makefile 2008-01-11 20:16:02.000000000 -0500 @@ -87,7 +87,7 @@ OPTIONS+= HEIMDAL_KRB5 "Builds with Heimdal kerberos support" off OPTIONS+= OPTIMIZED_CFLAGS "Builds with compiler optimizations (-O3)" off OPTIONS+= LIBC_R "Link w/ libc_r, used by plpython (server)" off -OPTIONS+= THREADSAFE "make libpq thread safe" off +OPTIONS+= THREADSAFE "make libpq thread safe" on # to run regression tests: OPTIONS+= TESTS "Allows the use of a \"check\" target (server)" off OPTIONS+= DEBUG "Builds with debugging symbols" off ? In terms of the kernel was this with ULE or SCHED_4BSD ? ---Mike
El vie, 11-01-2008 a las 18:15 +0100, Kris Kennaway escribi?:> Krassimir reports that with these two fixes, the standard 7.0 kernel > has > performance: > > #threads transactions/sec > 1 755 > 8 7129 > 40 6580 > 100 6768Hi. May i ask what kind of hardware is running this test? Just wanted to compare with this AMDX2 4600+ 2G DDR800 box wich gives: 1 500 2 887 4 880 6 834 8 791 thanks!
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Phillip N. wrote:> El vie, 11-01-2008 a las 18:15 +0100, Kris Kennaway escribi?: >> Krassimir reports that with these two fixes, the standard 7.0 kernel >> has >> performance: >> >> #threads transactions/sec >> 1 755 >> 8 7129 >> 40 6580 >> 100 6768 > > Hi. > > May i ask what kind of hardware is running this test? > > Just wanted to compare with this AMDX2 4600+ 2G DDR800 box wich gives: > > 1 500 > 2 887 > 4 880 > 6 834 > 8 791 > > > thanks! >HP ProLiant DL380G5, 2 x X5450 CPUs, 15k SAS HDD, 8G RAM, see the top of the thread.> > > > > > > _______________________________________________ > freebsd-stable@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-stable > To unsubscribe, send any mail to "freebsd-stable-unsubscribe@freebsd.org" >-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (FreeBSD) iD8DBQFHlFRexJBWvpalMpkRAlC6AKCZmWSF+TEiIfOTIbC2S6/Ak3DlsACgoZ6i 2NLhlPSCDoAz+74CuWerLw0=Q8Z1 -----END PGP SIGNATURE-----