Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Because I plan to develop a rather large (for us anyway) data warehouse with PostgreSQL. I am looking for the right hardware that can handle queries on a database that might grow to over a 100 gigabytes. Right now our decision support system based on postgresql 8.1.3 stores retail sales information for about 4 four years back *but* only as weekly summaries. I want to build the system so it can handle daily sales transactions also. You can imagine how many more records this will involve so I am looking for hardware that can give me the performance I need to make this project useable. In other words parsing and loading the daily transaction logs for our stores is likely to take huge amounts of effort. I need a machine that can complete the task in a reasonable amount of time. As people start to query the database to find sales related reports and information I need to make sure the queries will run reasonably fast for them. I have already hand optimized all of my queries on the current system. But currently I only have weekly sales summaries. Other divisions in our company have done a similar project using MS SQL Server on SMP hardware far outclassing the database server I currently use and they report heavy loads on the server with less than ideal query run times. I am sure I can do my part to optimize the queries once I start this project but there is only so much you can do. At some point you just need more powerful hardware. This is where I am at right now. Apart from that since I will only get this one chance to buy a new server for data processing I need to make sure that I buy something that can grow over time as our needs change. I don't want to buy a server only to find out later that it cannot meet our needs with future database projects. I have to balance a limited budget, room for future performance growth, and current system requirements. Trust me it isn't easy. Juan -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, April 06, 2006 2:57 AM To: pgsql-performance@postgresql.org Cc: Juan Casero (FL FLC); Luke Lonergan Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, > Ok that is beginning to become clear to me. Now I need to determine > if this server is worth the investment for us. Maybe it is not a > speed daemon but to be honest the licensing costs of an SMP aware > RDBMS is outside our budget. You still haven't explained why you want multi-threaded queries. This is sounding like keeping up with the Joneses. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Ok that is beginning to become clear to me. Now I need to determine if this server is worth the investment for us. Maybe it is not a speed daemon but to be honest the licensing costs of an SMP aware RDBMS is outside our budget. When postgresql starts does it start up a super server process and then forks copies of itself to handle incoming requests? Or do I have to specify how many server processes should be started up? I figured maybe I can take advantage of the multiple cpu's on this system by starting up enough postgres server processes to handle large numbers of incoming connections. I have this server available for sixty days so I may as well explore the performance of postgresql on it. Thanks, Juan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Luke Lonergan Sent: Wednesday, April 05, 2006 5:37 PM To: Juan Casero (FL FLC); pgsql-performance@postgresql.org Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, On 4/5/06 1:54 PM, "Juan Casero (FL FLC)" <[EMAIL PROTECTED]> wrote: > I am not sure about this. I mean I have postgresql 8.1.3 running on > my Windows XP P4 HT laptop that I use for testing my webapps. When I > hit this pgsql on this laptop with a large query I can see the load > spike up really high on both of my virtual processors. Whatever, > pgsql is doing it looks like both cpu's are being used indepently. The > usage curve is not identical on both of them that makes me think that > parts of the server are multithreaded. Admittedly I am not familiar > with the source code fo postgresql so I was hoping maybe one of the > developers who is could definitely answer this question. There's no part of the Postgres backend that is threaded or multi-processed. A reasonable explanation for your windows experience is that your web server or the psql client may be taking some CPU cycles while the backend is processing your query. Also, depending on how the CPU load is reported, if the OS is doing prefetching of I/O, it might show up as load. - Luke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
I am evaluating this SunFire T2000 as a replacement for an Intel P3 1Ghz postgresql server. This intel server runs a retail reporting database on postgresql 8.1.3. I need to realize significant performance gains on T2000 server to justify the expense. So I need to tune the postgresql server as much as I can for it. Right now the operating system (solaris 10) sees each thread as a single cpu and only allows each thread 4.16% of the available cpu resources for processing queries. Since postgresql is not multithreaded and since I cannot apparently break past the operating system imposed limits on a single thread I can't fully realize the performance benefits of the T2000 server unless and until I start getting lots of people hitting the database server with requests. This doesn't happen right now. It may happen later on as I write more applications for the server but I am looking to see if the performance benefit we can get from this server is worth the price tag right now. That is why I am looking for ways to tweak postgres on it. Thanks, Juan -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 6:02 PM To: pgsql-performance@postgresql.org Cc: Juan Casero (FL FLC); Luke Lonergan Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, > When I hit > this pgsql on this laptop with a large query I can see the load spike > up really high on both of my virtual processors. Whatever, pgsql is > doing it looks like both cpu's are being used indepently. Nope, sorry, you're being decieved. Postgres is strictly one process, one query. You can use Bizgres MPP to achieve multithreading; it's proprietary and you have to pay for it. It does work well, though. More importantly, though, you haven't really explained why you care about multithreading. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Luke (or anyone else who may be following this thread) Do you think bizgres might be a good choice of database server for the Ultrasparc T1 based T2000? I have downloaded the source code but I was hoping to find out if the potential performance gains were worth the effort to compile and install the code. Thanks, Juan -Original Message- From: Luke Lonergan [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 5:37 PM To: Juan Casero (FL FLC); pgsql-performance@postgresql.org Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, On 4/5/06 1:54 PM, "Juan Casero (FL FLC)" <[EMAIL PROTECTED]> wrote: > I am not sure about this. I mean I have postgresql 8.1.3 running on > my Windows XP P4 HT laptop that I use for testing my webapps. When I > hit this pgsql on this laptop with a large query I can see the load > spike up really high on both of my virtual processors. Whatever, > pgsql is doing it looks like both cpu's are being used indepently. The > usage curve is not identical on both of them that makes me think that > parts of the server are multithreaded. Admittedly I am not familiar > with the source code fo postgresql so I was hoping maybe one of the > developers who is could definitely answer this question. There's no part of the Postgres backend that is threaded or multi-processed. A reasonable explanation for your windows experience is that your web server or the psql client may be taking some CPU cycles while the backend is processing your query. Also, depending on how the CPU load is reported, if the OS is doing prefetching of I/O, it might show up as load. - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
I am not sure about this. I mean I have postgresql 8.1.3 running on my Windows XP P4 HT laptop that I use for testing my webapps. When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. The usage curve is not identical on both of them that makes me think that parts of the server are multithreaded. Admittedly I am not familiar with the source code fo postgresql so I was hoping maybe one of the developers who is could definitely answer this question. Thanks, Juan -Original Message- From: Luke Lonergan [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 4:43 PM To: Juan Casero (FL FLC); pgsql-performance@postgresql.org Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, On 4/5/06 11:12 AM, "Juan Casero (FL FLC)" <[EMAIL PROTECTED]> wrote: > I know the postgresql server is not smp aware but I believe parts of > it are. In particular the buffer manager is supposed to scale the > performance almost linearly with the number of cpu's (including virtual ones). > I don't know however, if I need to recompile the postgresql server > myself to get those benefits. As Tom said, to get the benefits of parallelism on one query, you would need a parallelizing database like Teradata, Oracle Parallel Query option, Netezza, or Bizgres MPP. The announcement about Postgres linear scalability for SMP is only relevant to statement throughput for highly concurrent environments (web sites, OLTP, etc). - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Greetings - I am testing a Sun Microsystems Sun Fire T2000 demo server at our company. I want to know if anyone here has any experience with this hardware and postgresql 8.1.3. I installed the copy of postgresql 8.1.3 from blastwave.org onto this demo box and loaded our production database into it. This box has a single Ultrasparc T1 cpu with six execution piplelines that can each handle 4 threads. With the Unix top utility the postgresql server appears to bounce around between the available threads on the system. For example I run a single large query and I can see the postgresql server sometimes running on cpu/0, other times on cpu/1, cpu/3,etc up to cpu/23. However, never is the load for the postgres server reported to be higher than 4.16%. I did the math and 4.16% x 24 threads = 98.84% cpu load. So I wonder if the Solaris 10 kernel is somehow throttling the processes so that any single virtual processor can do no more than 4.16% load. We got this server last week and I was able to install it in our rack just yesterday. Now I need to see how I can optimize the postgresql server to work on this box. Does anyone have any suggestions? I know the postgresql server is not smp aware but I believe parts of it are. In particular the buffer manager is supposed to scale the performance almost linearly with the number of cpu's (including virtual ones). I don't know however, if I need to recompile the postgresql server myself to get those benefits. I am using the version of postgresql 8.1.3 that is available on blastwave.org. I am also working with the 64 bit version of the database server. This machine has over 8GB of ram so I was thinking of using the 64 bit version of the postgresql server so I can access ram beyong the 4gb limit imposed by 32 bit addressing. Any help or recommendations for performance tweaking of postgresql is very much appreciated. Thanks, Juan
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
Ok thanks. I think I will go with 64 bit everything on the box. If I can get the Sun Fire V20Z then I will stick with Solaris 10 x86 and download the 64 bit PostgreSQL 8.1 binaries from blastwave.org. I develop the PHP code to my DSS system on my Windows XP laptop. Normally, I test the code on this laptop but let it hit the live database when I want to run some tests. Well just this afternoon I installed PostgreSQL 8.1.1 on my windows laptop and rebuilt the the entire live database instance on there from a pg_dump archive. I am blown away by the performance increase in PostgreSQL 8.1.x. Has anyone else had a chance to test it? All the queries I run against it are remarkably fast but more importantly I can see that the two cores of my Hyper Threaded P4 are being used. One of the questions I posted on this list was whether PostgreSQL could make use of the large number of cores available on the Ultrasparc T1000/T2000 cores. I am beginning to think that with PostgreSQL 8.1.x the buffer manager could indeed use all those cores. This could make running a DSS or OLTP on an Ultrasparc T1000/T2000 with PostgreSQL a much better bargain than on an intel system. Any thoughts? Thanks, Juan On Thursday 22 December 2005 22:12, David Lang wrote: > On Wed, 21 Dec 2005, Juan Casero wrote: > > Date: Wed, 21 Dec 2005 22:31:54 -0500 > > From: Juan Casero <[EMAIL PROTECTED]> > > To: pgsql-performance@postgresql.org > > Subject: Re: [PERFORM] What's the best hardver for PostgreSQL 8.1? > > > > Sorry folks. I had a couple of glasses of wine as I wrote this. Anyway > > I originally wanted the box to have more than two drives so I could do > > RAID 5 but that is going to cost too much. Also, contrary to my > > statement below it seems to me I should run the 32 bit postgresql server > > on the 64 bit kernel. Would you agree this will probably yield the best > > performance? > > you definantly need a 64 bit kernel to address as much ram as you will > need. > > the question of 32 bit vs 64 bit postgres needs to be benchmarked, but my > inclination is that you probably do want 64 bit for that as well. > > 64 bit binaries are slightly larger then 32 bit ones (less so on x86/AMD64 > then on any other mixed platform though), but the 64 bit version also has > access to twice as many registers as a 32 bit one, and the Opteron chips > have some other features that become availabel in 64 bit mode (or more > useful) > > like everything else this needs benchmarks to prove with your workload > (I'm trying to get some started, but haven't had a chance yet) > > David Lang > > > I know it > > depends alot on the system but for now this database is about 20 > > gigabytes. Not too large right now but it may grow 5x in the next year. > > > > Thanks, > > Juan > > > > On Wednesday 21 December 2005 22:09, Juan Casero wrote: > >> I just sent my boss an email asking him for a Sun v20z with dual 2.2 Ghz > >> opterons, 2 Gigs of RAM and RAID 1. I would have liked a better server > >> capable of RAID but that seems to be out of his budget right now. Ok so > >> I assume I get this Sun box. Most likely I will go with Linux since it > >> is a fair bet he doesn't want to pay for the Solaris 10 x86 license. > >> Although I kind of like the idea of using Solaris 10 x86 for this. I > >> will assume I need to install the x64 kernel that comes with say Fedora > >> Core 4. Should I run the Postgresql 8.x binaries in 32 bit mode or 64 > >> bit mode? My instinct tells me 64 bit mode is most efficient for our > >> database size about 20 gigs right now but may grow to 100 gigs in a year > >> or so. I just finished loading a 20 gig database on a dual 900 Mhz > >> Ultrasparc III system with 2 gigs of ram and about 768 megs of shared > >> memory available for the posgresql server running Solaris 10. The load > >> has smoked a P4 3.2 Ghz system I am using also with 2 gigs of ram > >> running postgresql 8.0.3. I mean I started the sparc load after the P4 > >> load. The sparc load has finished already rebuilding the database from > >> a pg_dump file but the P4 system is still going. The p4 has 1.3 Gigs of > >> shared memory allocated to postgresql. How about them apples? > >> > >> > >> Thanks, > >> Juan > >> > >> On Wednesday 21 December 2005 18:57, William Yu wrote: > >>> Juan Casero wrote: > >>>> Can you elaborate on the reasons the opteron is better than the Xeon > >>>> when it comes to disk io? I have a PostgreSQL 7.4.8 box running a > >>>> DSS. One of
Re: [PERFORM] MySQL is faster than PgSQL but a large margin in
Agreed. I have a 13 million row table that gets a 100,000 new records every week. There are six indexes on this table. Right about the time when it reached the 10 million row mark updating the table with new records started to take many hours if I left the indexes in place during the update. Indeed there was even some suspicion that the indexes were starting to get corrupted during the load. So I decided to fist drop the indexes when I needed to update the table. Now inserting 100,000 records into the table is nearly instantaneous although it does take me a couple of hours to build the indexes anew. This is still big improvement since at one time it was taking almost 12 hours to update the table with the indexes in place. Juan On Thursday 22 December 2005 08:34, Markus Schaber wrote: > Hi, Madison, > Hi, Luke, > > Luke Lonergan wrote: > > Note that indexes will also slow down loading. > > For large loading bunches, it often makes sense to temporarily drop the > indices before the load, and recreate them afterwards, at least, if you > don't have normal users accessing the database concurrently. > > Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
Sorry folks. I had a couple of glasses of wine as I wrote this. Anyway I originally wanted the box to have more than two drives so I could do RAID 5 but that is going to cost too much. Also, contrary to my statement below it seems to me I should run the 32 bit postgresql server on the 64 bit kernel. Would you agree this will probably yield the best performance?I know it depends alot on the system but for now this database is about 20 gigabytes. Not too large right now but it may grow 5x in the next year. Thanks, Juan On Wednesday 21 December 2005 22:09, Juan Casero wrote: > I just sent my boss an email asking him for a Sun v20z with dual 2.2 Ghz > opterons, 2 Gigs of RAM and RAID 1. I would have liked a better server > capable of RAID but that seems to be out of his budget right now. Ok so I > assume I get this Sun box. Most likely I will go with Linux since it is a > fair bet he doesn't want to pay for the Solaris 10 x86 license. Although I > kind of like the idea of using Solaris 10 x86 for this. I will assume I > need to install the x64 kernel that comes with say Fedora Core 4. Should I > run the Postgresql 8.x binaries in 32 bit mode or 64 bit mode? My > instinct tells me 64 bit mode is most efficient for our database size about > 20 gigs right now but may grow to 100 gigs in a year or so. I just > finished loading a 20 gig database on a dual 900 Mhz Ultrasparc III system > with 2 gigs of ram and about 768 megs of shared memory available for the > posgresql server running Solaris 10. The load has smoked a P4 3.2 Ghz > system I am using also with 2 gigs of ram running postgresql 8.0.3. I > mean I started the sparc load after the P4 load. The sparc load has > finished already rebuilding the database from a pg_dump file but the P4 > system is still going. The p4 has 1.3 Gigs of shared memory allocated to > postgresql. How about them apples? > > > Thanks, > Juan > > On Wednesday 21 December 2005 18:57, William Yu wrote: > > Juan Casero wrote: > > > Can you elaborate on the reasons the opteron is better than the Xeon > > > when it comes to disk io? I have a PostgreSQL 7.4.8 box running a > > > DSS. One of our > > > > Opterons have 64-bit IOMMU -- Xeons don't. That means in 64-bit mode, > > transfers to > 4GB, the OS must allocated the memory < 4GB, DMA to that > > block and then the CPU must do extra work in copying the memory to > > > 4GB. Versus on the Opteron, it's done by the IO adaptor using DMA in the > > background. > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
I just sent my boss an email asking him for a Sun v20z with dual 2.2 Ghz opterons, 2 Gigs of RAM and RAID 1. I would have liked a better server capable of RAID but that seems to be out of his budget right now. Ok so I assume I get this Sun box. Most likely I will go with Linux since it is a fair bet he doesn't want to pay for the Solaris 10 x86 license. Although I kind of like the idea of using Solaris 10 x86 for this. I will assume I need to install the x64 kernel that comes with say Fedora Core 4. Should I run the Postgresql 8.x binaries in 32 bit mode or 64 bit mode? My instinct tells me 64 bit mode is most efficient for our database size about 20 gigs right now but may grow to 100 gigs in a year or so. I just finished loading a 20 gig database on a dual 900 Mhz Ultrasparc III system with 2 gigs of ram and about 768 megs of shared memory available for the posgresql server running Solaris 10. The load has smoked a P4 3.2 Ghz system I am using also with 2 gigs of ram running postgresql 8.0.3. I mean I started the sparc load after the P4 load. The sparc load has finished already rebuilding the database from a pg_dump file but the P4 system is still going. The p4 has 1.3 Gigs of shared memory allocated to postgresql. How about them apples? Thanks, Juan On Wednesday 21 December 2005 18:57, William Yu wrote: > Juan Casero wrote: > > Can you elaborate on the reasons the opteron is better than the Xeon when > > it comes to disk io? I have a PostgreSQL 7.4.8 box running a DSS. One > > of our > > Opterons have 64-bit IOMMU -- Xeons don't. That means in 64-bit mode, > transfers to > 4GB, the OS must allocated the memory < 4GB, DMA to that > block and then the CPU must do extra work in copying the memory to > > 4GB. Versus on the Opteron, it's done by the IO adaptor using DMA in the > background. > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
Can you elaborate on the reasons the opteron is better than the Xeon when it comes to disk io? I have a PostgreSQL 7.4.8 box running a DSS. One of our tables is about 13 million rows. I had a number of queries against this table that used innner joins on 5 or 6 tables including the 13 million row one. The performance was atrocious. The database itself is about 20 gigs but I want it to scale to 100 gigs. I tuned postgresql as best I could and gave the server huge amounts of memory for caching as well. I also tweaked the cost parameters for a sequential scan vs an index scan of the query optimizer and used the query explain mechanism to get some idea of what the optimizer was doing and where I should index the tables. When I added the sixth table to the inner join the query performance took a nose dive. Admittedly this system is a single PIII 1000Mhz with 1.2 gigs of ram and no raid. I do have two Ultra 160 scsi drives with the database tables mount point on a partition on one physical drive and pg_xlog mount point on another partition of the second drive.I have been trying to get my employer to spring for new hardware ($8k to $10k) which I had planned to be a dual - dual core opteron system from HP. Until they agree to spend the money I resorted to writing a plpgsql functions to handle the queries. Inside plpgsql I can break the query apart into seperate stages each of which runs much faster. I can use temporary tables to store intermediate results without worrying about temp table collisions with different users thanks to transaction isolation. I am convinced we need new hardware to scale this application *but* I agree with the consensus voiced here that it is more important to optimize the query first before going out to buy new hardware. I was able to do things with PostgreSQL on this cheap server that I could never imagine doing with SQL server or even oracle on such a low end box. My OS is Fedora Core 3 but I wonder if anyone has tested and benchmarked PostgreSQL on the new Sun x64 servers running Solaris 10 x86. Thanks, Juan On Tuesday 20 December 2005 16:08, Vivek Khera wrote: > On Dec 20, 2005, at 1:27 PM, Antal Attila wrote: > > The budget line is about 30 000$ - 40 000$. > > Like Jim said, without more specifics it is hard to give more > specific recommendations, but I'm architecting something like this > for my current app which needs ~100GB disk space. I made room to > grow in my configuration: > > dual opteron 2.2GHz > 4GB RAM > LSI MegaRAID 320-2X > 14-disk SCSI U320 enclosure with 15k RPM drives, 7 connected to each > channel on the RAID. >1 pair in RAID1 mirror for OS + pg_xlog >rest in RAID10 with each mirrored pair coming from opposite SCSI > channels for data > > I run FreeBSD but whatever you prefer should be sufficient if it is > not windows. > > I don't know how prices are in Hungary, but around here something > like this with 36GB drives comes to around $11,000 or $12,000. > > The place I concentrate on is the disk I/O bandwidth which is why I > prefer Opteron over Intel XEON. > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] High context switches occurring
Guys - Help me out here as I try to understand this benchmark. What is the Sun hardware and operating system we are talking about here and what is the intel hardware and operating system? What was the Sun version of PostgreSQL compiled with? Gcc on Solaris (assuming sparc) or Sun studio? What was PostgreSQL compiled with on intel? Gcc on linux? Thanks, Juan On Monday 19 December 2005 21:08, Anjan Dave wrote: > Re-ran it 3 times on each host - > > Sun: > -bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench > starting vacuum...end. > transaction type: TPC-B (sort of) > scaling factor: 1 > number of clients: 10 > number of transactions per client: 3000 > number of transactions actually processed: 3/3 > tps = 827.810778 (including connections establishing) > tps = 828.410801 (excluding connections establishing) > real0m36.579s > user0m1.222s > sys 0m3.422s > > Intel: > -bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench > starting vacuum...end. > transaction type: TPC-B (sort of) > scaling factor: 1 > number of clients: 10 > number of transactions per client: 3000 > number of transactions actually processed: 3/3 > tps = 597.067503 (including connections establishing) > tps = 597.606169 (excluding connections establishing) > real0m50.380s > user0m2.621s > sys 0m7.818s > > Thanks, > Anjan > > > -Original Message- > From: Anjan Dave > Sent: Wed 12/7/2005 10:54 AM > To: Tom Lane > Cc: Vivek Khera; Postgresql Performance > Subject: Re: [PERFORM] High context switches occurring > > > > Thanks for your inputs, Tom. I was going after high concurrent clients, > but should have read this carefully - > > -s scaling_factor > this should be used with -i (initialize) option. > number of tuples generated will be multiple of the > scaling factor. For example, -s 100 will imply 10M > (10,000,000) tuples in the accounts table. > default is 1. NOTE: scaling factor should be at least > as large as the largest number of clients you intend > to test; else you'll mostly be measuring update > contention. > > I'll rerun the tests. > > Thanks, > Anjan > > > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 06, 2005 6:45 PM > To: Anjan Dave > Cc: Vivek Khera; Postgresql Performance > Subject: Re: [PERFORM] High context switches occurring > > "Anjan Dave" <[EMAIL PROTECTED]> writes: > > -bash-3.00$ time pgbench -c 1000 -t 30 pgbench > > starting vacuum...end. > > transaction type: TPC-B (sort of) > > scaling factor: 1 > > number of clients: 1000 > > number of transactions per client: 30 > > number of transactions actually processed: 3/3 > > tps = 45.871234 (including connections establishing) > > tps = 46.092629 (excluding connections establishing) > > I can hardly think of a worse way to run pgbench :-(. These numbers are > about meaningless, for two reasons: > > 1. You don't want number of clients (-c) much higher than scaling factor > (-s in the initialization step). The number of rows in the "branches" > table will equal -s, and since every transaction updates one > randomly-chosen "branches" row, you will be measuring mostly row-update > contention overhead if there's more concurrent transactions than there > are rows. In the case -s 1, which is what you've got here, there is no > actual concurrency at all --- all the transactions stack up on the > single branches row. > > 2. Running a small number of transactions per client means that > startup/shutdown transients overwhelm the steady-state data. You should > probably run at least a thousand transactions per client if you want > repeatable numbers. > > Try something like "-s 10 -c 10 -t 3000" to get numbers reflecting test > conditions more like what the TPC council had in mind when they designed > this benchmark. I tend to repeat such a test 3 times to see if the > numbers are repeatable, and quote the middle TPS number as long as > they're not too far apart. > > regards, tom lane > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you sea
Re: [PERFORM] PostgreSQL and Ultrasparc T1
Ok. That is what I wanted to know. Right now this database is a PostgreSQL 7.4.8 system. I am using it in a sort of DSS role. I have weekly summaries of the sales for our division going back three years. I have a PHP based webapp that I wrote to give the managers access to this data. The webapp lets them make selections for reports and then it submits a parameterized query to the database for execution. The returned data rows are displayed and formatted in their web browser. My largest sales table is about 13 million rows along with all the indexes it takes up about 20 gigabytes. I need to scale this application up to nearly 100 gigabytes to handle daily sales summaries. Once we start looking at daily sales figures our database size could grow ten to twenty times. I use postgresql because it gives me the kind of enterprise database features I need to program the complex logic for the queries.I also need the transaction isolation facilities it provides so I can optimize the queries in plpgsql without worrying about multiple users temp tables colliding with each other. Additionally, I hope to rewrite the front end application in JSP so maybe I could use the multithreaded features of the Java to exploit a multicore multi-cpu system. There are almost no writes to the database tables. The bulk of the application is just executing parameterized queries and returning huge amounts of data. I know bizgres is supposed to be better at this but I want to stay away from anything that is beta. I cannot afford for this thing to go wrong. My reasoning for looking at the T1000/2000 was simply the large number of cores. I know postgresql uses a super server that forks copies of itself to handle incoming requests on port 5432. But I figured the number of cores on the T1000/2000 processors would be utilized by the forked copies of the postgresql server. From the comments I have seen so far it does not look like this is the case. We had originally sized up a dual processor dual core AMD opteron system from HP for this but I thought I could get more bang for the buck on a T1000/2000. It now seems I may have been wrong. I am stronger in Linux than Solaris so I am not upset I am just trying to find the best hardware for the anticipated needs of this application. Thanks, Juan On Monday 19 December 2005 01:25, Scott Marlowe wrote: > From: [EMAIL PROTECTED] on behalf of Juan Casero > > QUOTE: > > Hi - > > > Can anyone tell me how well PostgreSQL 8.x performs on the new Sun > Ultrasparc T1 processor and architecture on Solaris 10? I have a custom > built retail sales reporting that I developed using PostgreSQL 7.48 and PHP > on a Fedora Core 3 intel box. I want to scale this application upwards to > handle a database that might grow to a 100 GB. Our company is green > mission conscious now so I was hoping I could use that to convince > management to consider a Sun Ultrasparc T1 or T2 system provided that if I > can get the best performance out of it on PostgreSQL. > > ENDQUOTE: > > Well, generally, AMD 64 bit is going to be a better value for your dollar, > and run faster than most Sparc based machines. > > Also, PostgreSQL is generally faster under either BSD or Linux than under > Solaris on the same box. This might or might not hold as you crank up the > numbers of CPUs. > > PostgreSQL runs one process for connection. So, to use extra CPUs, you > really need to have >1 connection running against the database. > > Mostly, databases tend to be either I/O bound, until you give them a lot of > I/O, then they'll be CPU bound. > > After that lots of memory, THEN more CPUs. Two CPUs is always useful, as > one can be servicing the OS and another the database. But unless you're > gonna have lots of users hooked up, more than 2 to 4 CPUs is usually a > waste. > > So, I'd recommend a dual core or dual dual core (i.e. 4 cores) AMD64 system > with 2 or more gigs ram, and at least a pair of fast drives in a mirror > with a hardare RAID controller with battery backed cache. If you'll be > trundling through all 100 gigs of your data set regularly, then get all the > memory you can put in a machine at a reasonable cost before buying lots of > CPUs. > > But without knowing what you're gonna be doing we can't really make solid > recommendations... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] PostgreSQL and Ultrasparc T1
Hi - Can anyone tell me how well PostgreSQL 8.x performs on the new Sun Ultrasparc T1 processor and architecture on Solaris 10? I have a custom built retail sales reporting that I developed using PostgreSQL 7.48 and PHP on a Fedora Core 3 intel box. I want to scale this application upwards to handle a database that might grow to a 100 GB. Our company is green mission conscious now so I was hoping I could use that to convince management to consider a Sun Ultrasparc T1 or T2 system provided that if I can get the best performance out of it on PostgreSQL. So will newer versions of PostgreSQL (8.1.x) be able to take of advantage of the multiple cores on a T1 or T2?I cannot change the database and this will be a hard sell unless I can convince them that the performance advantages are too good to pass up. The company is moving in the Win32 direction and so I have to provide rock solid reasons for why I want to use Solaris Sparc on a T1 or T2 server for this database application instead of Windows on SQL Server. Thanks, Juan --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
