Re: [PERFORM] VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
You should rather consider VxFS tuning - it has an auto-discovery for DIRECT I/O according the block size. Just change this setting to 8K or 16-32K depending on your workload - then all I/O operations with a bigger block size will be executed in DIRECT mode and bypass FS cache (which logical as usually it'll correspond to a full scan or a seq scan of some data), while I/O requests with smaller blocks will remain cached which is very useful as it'll mainly cache random I/O (mainly index access).. With such a tuning I've got over %35 performance improvement comparing to any other states (full DIRECT or fully cached). Rgds, -Dimitri Rgds, -Dimitri On 5/5/11, Robert Haas wrote: > On Sat, Apr 30, 2011 at 4:51 AM, Hsien-Wen Chu > wrote: >> since the block size is 8k for the default, and it consisted with many >> tuple/line; as my understand, if any tuple/line is changed(maybe >> update, insert, delete). the block will be marked as dirty block. and >> then it will be flashed to disk by bgwriter. > > True... > >> so my question is if the data block(8k) is aligned with the file >> system block? if it is aligned with file system block, so what's the >> potential issue make it is not safe for direct io. (please assume >> vxfs, vxvm and the disk sector is aligned ).please correct me if any >> incorrect. > > It's not about safety - it's about performance. On a machine with > 64GB of RAM, a typical setting for shared_buffers set to 8GB. If you > start reading blocks into the PostgreSQL cache - or writing them out > of the cache - in a way that bypasses the filesystem cache, you're > going to have only 8GB of cache, instead of some much larger amount. > More cache = better performance. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] MySQL HandlerSocket - Is this possible in PG?
Michael Ben-Nes writes: > I wonder if PostgreSQL should replicate this functionality somehow. How can > I represent this idea to the developers? They will probably know if this > feature worth something. As I didn't have enough time to follow this thread in detail I'm not sure how closely it is related, but have you tried preprepare? https://github.com/dimitri/preprepare Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Simple database, multiple instances?
Mario Splivalo writes: > I have simple database schema, containing just three tables: > > samples, drones, drones_history. > > Now, those tables hold data for the drones for a simulation. Each simulation > dataset will grow to around 10 GB in around 6 months. > > Since the data is not related in any way I was thinking in separating each > simulation into it's own database. That way it would be much easier for me > to, at later date, move some of the databases to other servers (when dataset > grows beyond the original server storage capacity limit). Do you intend to run queries across multiple simulations at once? If yes, you want to avoid multi databases. Other than that, I'd go with a naming convention like samples_ and maybe some inheritance to ease querying multiple simulations. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to achieve sustained disk performance of 1.25 GB write for 5 mins
You may also try the Sun's F5100 (flash storage array) - you may easily get 700 MB/s just with a single I/O stream (single process), so just with 2 streams you'll get your throughput.. - The array has 2TB total space and max throughput should be around 4GB/s.. Rgds, -Dimitri On 11/18/10, Greg Smith wrote: > Eric Comeau wrote: >> Ideally 1 large file, but it may have to be multiple. We find that if >> we send multiple files it just causes the disk to thrash more so we >> get better throughput by sending one large file. > > If it's really one disk, sure. The problem you're facing is that your > typical drive controller is going to top out at somewhere between 300 - > 500MB/s of sequential writes before it becomes the bottleneck. Above > somewhere between 6 and 10 drives attached to one controller on current > hardware, adding more to a RAID-0 volume only increases the ability to > handle seeks quickly. If you want to try and do this with traditional > hard drives, I'd guess you'd need 3 controllers with at least 4 > short-stroked drives attached to each to have any hope of hitting > 1.25GB/s. Once you do that, you'll run into CPU time as the next > bottleneck. At that point, you'll probably need one CPU per controller, > all writing out at once, to keep up with your target. > > The only popular hardware design that comes to mind aimed at this sort > of thing was Sun's "Thumper" design, most recently seen in the Sun Fire > X4540. That put 8 controllers with 6 disks attached to each, claiming > "demonstrated up to 2 GB/sec from disk to network". It will take a > design like that, running across multiple controllers, to get what > you're looking for on the disk side--presuming everything else keeps up. > > One of the big SSD-on-PCI-e designs mentioned here already may very well > end up being a better choice for you here though, as those aren't going > to require quite as much hardware all get wired up. > > -- > Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD > PostgreSQL Training, Services and Supportwww.2ndQuadrant.us > "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences
"Lello, Nick" writes: > A bigger gain can probably be had if you have a tightly controlled > suite of queries that will be run against the database and you can > spend the time to tune each to ensure it performs no sequential scans > (ie: Every query uses index lookups). Given a fixed pool of queries, you can prepare them in advance so that you don't usually pay the parsing and planning costs. I've found that the planning is easily more expensive than the executing when all data fits in RAM. Enter pgbouncer and preprepare : http://wiki.postgresql.org/wiki/PgBouncer http://preprepare.projects.postgresql.org/README.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows
The problem here is that we're trying to keep an image of a whole world for any transaction which is in most cases will need to get a look on few streets around.. ;-) I understand well that it's respecting the standard and so on, but the background problem that you may see your table bloated just because there is a long running transaction appeared in another database, and if it's maintained/used/etc by another team - the problem very quickly may become human rather technical :-)) So, why simply don't add a FORCE option to VACUUM?.. - In this case if one executes "VACUUM FORCE TABLE" will be just aware about what he's doing and be sure no one of the active transactions will be ever access this table. What do you think?.. ;-) Rgds, -Dimitri On 8/22/10, Robert Haas wrote: > On Sat, Aug 21, 2010 at 9:49 AM, Alexandre de Arruda Paes > wrote: >> Only for discussion: the CLUSTER command, in my little knowledge, is a >> intrusive command that's cannot recover the dead tuples too. >> >> Only TRUNCATE can do this job, but obviously is not applicable all the >> time. > > Either VACUUM or CLUSTER will recover *dead* tuples. What you can't > recover are tuples that are still visible to some running transaction. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows
Great! - it's what I expected until now :-) but discussion in this thread put my mind in trouble :-)) So, the advice for Alexandre here is just to check the age of the oldest running transaction and the last time when the table in question was modified.. - if modification time is older than the oldest transaction = we have a problem in PG.. Otherwise it works as expected to match MVCC. Rgds, -Dimitri On 8/21/10, Scott Marlowe wrote: > No, it means it can't clean rows that are younger than the oldest > transaction currently in progress. if you started a transaction 5 > hours ago, then all the dead tuples created in the last 5 hours are > not recoverable. Dead tuples created before that transaction are > recoverable. If you run transactions for days or weeks, then you're > gonna have issues. > > On Sat, Aug 21, 2010 at 2:25 AM, Dimitri wrote: >> So, does it mean that VACUUM will never clean dead rows if you have a >> non-stop transactional activity in your PG database???... (24/7 OLTP >> for ex.) >> >> Rgds, >> -Dimitri >> >> >> On 8/19/10, Kevin Grittner wrote: >>> Alexandre de Arruda Paes wrote: >>>> 2010/8/18 Tom Lane >>> >>>>> There's an open transaction somewhere that VACUUM is preserving >>>>> the tuples for. This transaction need not ever have touched the >>>>> table, or ever intend to touch the table --- but VACUUM cannot >>>>> know that, so it saves any tuples that the transaction might be >>>>> entitled to see if it looked. >>>>> >>>>> > carmen=# select * from vlocks where relname='tp93t'; select * >>>>> > from pg_stat_activity where usename='webpa'; >>>>> >>>>> You keep on showing us only subsets of pg_stat_activity :-( >>> >>>> select * from pg_stat_activity where usename='webpa'; >>> >>> You keep on showing us only subsets of pg_stat_activity :-( >>> >>> *ANY* open transaction, including "idle in transaction" including >>> transactions by other users in other databases will prevent vacuum >>> from cleaning up rows, for the reasons Tom already gave you. >>> >>> What do you get from?: >>> >>> select * from pg_stat_activity where current_query <> '' >>> order by xact_start limit 10; >>> >>> -Kevin >>> >>> -- >>> Sent via pgsql-performance mailing list >>> (pgsql-performance@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-performance >>> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > > > -- > To understand recursion, one must first understand recursion. > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows
So, does it mean that VACUUM will never clean dead rows if you have a non-stop transactional activity in your PG database???... (24/7 OLTP for ex.) Rgds, -Dimitri On 8/19/10, Kevin Grittner wrote: > Alexandre de Arruda Paes wrote: >> 2010/8/18 Tom Lane > >>> There's an open transaction somewhere that VACUUM is preserving >>> the tuples for. This transaction need not ever have touched the >>> table, or ever intend to touch the table --- but VACUUM cannot >>> know that, so it saves any tuples that the transaction might be >>> entitled to see if it looked. >>> >>> > carmen=# select * from vlocks where relname='tp93t'; select * >>> > from pg_stat_activity where usename='webpa'; >>> >>> You keep on showing us only subsets of pg_stat_activity :-( > >> select * from pg_stat_activity where usename='webpa'; > > You keep on showing us only subsets of pg_stat_activity :-( > > *ANY* open transaction, including "idle in transaction" including > transactions by other users in other databases will prevent vacuum > from cleaning up rows, for the reasons Tom already gave you. > > What do you get from?: > > select * from pg_stat_activity where current_query <> '' > order by xact_start limit 10; > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 32 vs 64 bit build on Solaris Sparc
Hi Joe, the general rule on Solaris SPARC is: - if you need to address a big size of memory (over 4G): compile in 64bit - otherwise: compile in 32bit ;-) It's true that 32bit code will run faster comparing to 64bit ont the 64bit SPARC - you'll operate with 2 times shorter addresses, and in some cases SPARC will be able to execute 2 operations in parallel on 32bit code, while it'll be still one operation on 64bit code.. - But it's all about the code, because once you start to do I/O requests all kind of optimization on the instructions will be lost due I/O latency ;-)) So, as usual, a real answer in each case may be obtained only by a real test.. Just test both versions and you'll see yourself what is a valid in your case :-)) Same problem regarding compilers: in some cases GCC4 will give a better result, in some cases Sun Studio will be better (there are many posts in blogs about optimal compiler options to use).. - don't hesitate to try and don't forget to share here with others :-)) Rgds, -Dimitri On 8/11/10, Joseph Conway wrote: > With a 16 CPU, 32 GB Solaris Sparc server, is there any conceivable > reason to use a 32 bit build rather than a 64 bit build? Apparently the > Sun PostgreSQL package includes a README that indicates you might want > to think twice about using 64 bit because it is slower -- this seems > like outdated advice, but I was looking for confirmation one way or the > other. > > Also semi-related question: when building from source, using gcc, > enabling debug (but *not* assert) is normally not much of a performance > hit. Is the same true if you build with the Sun CC? > > Thanks in advance for any thoughts/experiences. > > Joe > > > > -- > Joe Conway > credativ LLC: http://www.credativ.us > Linux, PostgreSQL, and general Open Source > Training, Service, Consulting, & 24x7 Support > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Craig Ringer writes: > 9.0 has application_name to let apps identify themselves. Perhaps a > "pooled_client_ip", to be set by a pooler rather than the app, could be > added to address this problem in a way that can be used by all poolers > new and existing, not just any new in-core pooling system. X-Forwarded-For ? -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using more tha one index per table
Greg Smith writes: > Craig James wrote: >> By using "current" and encouraging people to link to that, we could >> quickly change the Google pagerank so that a search for Postgres would >> turn up the most-recent version of documentation. > > How do you propose to encourage people to do that? What about adding version information in huge letters in the top blue bar, with all versions available in lower letters than what you're looking at, and with current version nicely highlighted (color, underlining, subtitle, whatever, we'd have to find a visual hints). In other words, make it so big that you don't have to read the page content to realise what version it is you're looking at. Maybe we would need to have this information stay visible on the page at the same place when you scroll, too. Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Tom Lane writes: > I agree with the comments to the effect that this is really a packaging > and documentation problem. There is no need for us to re-invent the > existing solutions, but there is a need for making sure that they are > readily available and people know when to use them. On this topic, I think we're getting back to the idea of having non-core daemon helpers that should get "supervised" the way postmaster already does with backends wrt starting and stoping them at the right time. So a supervisor daemon with a supervisor API that would have to support autovacuum as a use case, then things like pgagent, PGQ and pgbouncer, would be very welcome. What about starting a new thread about that? Or you already know you won't want to push the extensibility of PostgreSQL there? Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query with planner row strange estimation
It's probably one of the cases when having HINTS in PostgreSQL may be very helpful.. SELECT /*+ enable_nestloop=off */ ... FROM ... will just fix this query without impacting other queries and without adding any additional instructions into the application code.. So, why there is a such resistance to implement hints withing SQL queries in PG?.. Rgds, -Dimitri On 7/9/10, Robert Haas wrote: > On Fri, Jul 9, 2010 at 6:13 AM, damien hostin > wrote: >>> Have you tried running ANALYZE on the production server? >>> >>> You might also want to try ALTER TABLE ... SET STATISTICS to a large >>> value on some of the join columns involved in the query. >> >> Hello, >> >> Before comparing the test case on the two machines, I run analyse on the >> whole and look at pg_stats table to see if change occurs for the columns. >> but on the production server the stats never became as good as on the >> desktop computer. I set statistic at 1 on column used by the join, run >> analyse which take a 300 row sample then look at the stats. The stats >> are not as good as on the desktop. Row number is nearly the same but only >> 1 >> or 2 values are found. >> >> The data are not balanced the same way on the two computer : >> - Desktop is 12000 rows with 6000 implicated in the query (50%), >> - "Production" (actually a dev/test server) is 6 million rows with 6000 >> implicated in the query (0,1%). >> Columns used in the query are nullable, and in the 5994000 other rows that >> are not implicated in the query these columns are null. >> >> I don't know if the statistic target is a % or a number of value to >> obtain, > > It's a number of values to obtain. > >> but event set at max (1), it didn't managed to collect good stats (for >> this particular query). > > I think there's a cutoff where it won't collect values unless they > occur significantly more often than the average frequency. I wonder > if that might be biting you here: without the actual values in the MCV > table, the join selectivity estimates probably aren't too good. > >> As I don't know what more to do, my conclusion is that the data need to be >> better balanced to allow the analyse gather better stats. But if there is >> a >> way to improve the stats/query with this ugly balanced data, I'm open to >> it >> ! >> >> I hope that in real production, data will never be loaded this way. If >> this >> appened we will maybe set enable_nestloop to off, but I don't think it's a >> good solution, other query have a chance to get slower. > > Yeah, that usually works out poorly. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Architecting a database
t...@exquisiteimages.com writes: > I am wondering how I should architect this in PostgreSQL. Should I follow > a similar strategy and have a separate database for each client and one > database that contains the global data? As others said already, there's more problems to foresee doing so that there are advantages. If you must separate data for security concerns, your situation would be much more comfortable using schema. If it's all about performances, see about partitioning the data, and maybe not even on the client id but monthly, e.g., depending on the queries you run in your application. Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL as a local in-memory cache
Tom Lane writes: > The problem with a system-wide no-WAL setting is it means you can't > trust the system catalogs after a crash. Which means you are forced to > use initdb to recover from any crash, in return for not a lot of savings > (for typical usages where there's not really much churn in the > catalogs). What about having a "catalog only" WAL setting, userset ? I'm not yet clear on the point but it well seems that the per transaction WAL setting is impossible because of catalogs (meaning mainly DDL support), but I can see us enforcing durability and crash safety there. That would probably mean that setting WAL level this low yet doing any kind of DDL would need to be either an ERROR, or better yet, a WARNING telling that the WAL level can not be that low so has been raised by the system. Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL as a local in-memory cache
Hi, Josh Berkus writes: > a) Eliminate WAL logging entirely > b) Eliminate checkpointing > c) Turn off the background writer > d) Have PostgreSQL refuse to restart after a crash and instead call an > exteral script (for reprovisioning) Well I guess I'd prefer a per-transaction setting, allowing to bypass WAL logging and checkpointing. Forcing the backend to care itself for writing the data I'm not sure is a good thing, but if you say so. Then you could have the GUC set for a whole cluster, only a database etc. We already have synchronous_commit to trade durability against performances, we could maybe support protect_data = off too. The d) point I'm not sure still applies if you have per transaction setting, which I think makes the most sense. The data you choose not to protect is missing at restart, just add some way to register a hook there. We already have one (shared_preload_libraries) but it requires coding in C. Calling a user function at the end of recovery and before accepting connection would be good I think. A user function (per database) is better than a script because if you want to run it before accepting connections and still cause changes in the database… Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Parallel queries for a web-application |performance testing
"Pierre C" writes: > The same is true of a web server : 1000 active php interpreters (each eating > several megabytes or more) are not ideal for performance ! > > For php, I like lighttpd with php-fastcgi : the webserver proxies requests > to a small pool of php processes, which are only busy while generating the > page. Once the page is generated the webserver handles all (slow) IO to the > client. I use haproxy for that, it handles requests queues very effectively. -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Parallel queries for a web-application |performance testing
Balkrishna Sharma writes: > I will have a web application having postgres 8.4+ as backend. At any given > time, there will be max of 1000 parallel web-users interacting with the > database (read/write) > I wish to do performance testing of 1000 simultaneous read/write to > the database. See about tsung, and either benckmarck only the PostgreSQL side of things, or at the HTTP side of things directly : that will run your application code against PostgreSQL. http://tsung.erlang-projects.org/ And as Kevin said, consider using a connection pool, such as pgbouncer. Once you have setup the benchmark with Tsung, adding pgbouncer and comparing the results will be easy. Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 8K recordsize bad on ZFS?
As I said, the record size is applied on the file creation :-) so by copying your data from one directory to another one you've made the new record size applied on the newly created files :-) (equal to backup restore if there was not enough space).. Did you try to redo the same but still keeping record size equal 8K ? ;-) I think the problem you've observed is simply related to the copy-on-write nature of ZFS - if you bring any modification to the data your sequential order of pages was broken with a time, and finally the sequential read was transformed to the random access.. But once you've re-copied your files again - the right order was applied again. BTW, 8K is recommended for OLTP workloads, but for DW you may stay with 128K without problem. Rgds, -Dimitri On 5/10/10, Josh Berkus wrote: > On 5/9/10 1:45 AM, Dimitri wrote: >> Josh, >> >> it'll be great if you explain how did you change the records size to >> 128K? - as this size is assigned on the file creation and cannot be >> changed later - I suppose that you made a backup of your data and then >> process a full restore.. is it so? > > You can change the recordsize of the zpool dynamically, then simply copy > the data directory (with PostgreSQL shut down) to a new directory on > that zpool. This assumes that you have enough space on the zpool, of > course. > > We didn't test how it would work to let the files in the Postgres > instance get gradually replaced by "natural" updating. > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 8K recordsize bad on ZFS?
Josh, it'll be great if you explain how did you change the records size to 128K? - as this size is assigned on the file creation and cannot be changed later - I suppose that you made a backup of your data and then process a full restore.. is it so? Rgds, -Dimitri On 5/8/10, Josh Berkus wrote: > Jignesh, All: > > Most of our Solaris users have been, I think, following Jignesh's advice > from his benchmark tests to set ZFS page size to 8K for the data zpool. > However, I've discovered that this is sometimes a serious problem for > some hardware. > > For example, having the recordsize set to 8K on a Sun 4170 with 8 drives > recently gave me these appalling Bonnie++ results: > > Version 1.96 --Sequential Output-- --Sequential Input- > --Random- > Concurrency 4 -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- > --Seeks-- > MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP > /sec %CP > db111 24G 260044 33 62110 17 89914 15 > 1167 25 > Latency6549ms4882ms 3395ms > 107ms > > I know that's hard to read. What it's saying is: > > Seq Writes: 260mb/s combined > Seq Reads: 89mb/s combined > Read Latency: 3.3s > > Best guess is that this is a result of overloading the array/drives with > commands for all those small blocks; certainly the behavior observed > (stuttering I/O, latency) is in line with that issue. > > Anyway, since this is a DW-like workload, we just bumped the recordsize > up to 128K and the performance issues went away ... reads up over 300mb/s. > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] shared_buffers advice
Greg Smith writes: > However, that doesn't actually solve any of the problems I was talking about > though, which is why I'm not even talking about that part. We need the glue > to pull out software releases, run whatever testing tool is appropriate, and > then save the run artifacts in some standardized form so they can be > referenced with associated build/configuration information to track down a > regression when it does show up. Building those boring bits are the real > issue here; load testing tools are easy to find because those are fun to > work on. Oh, ok. I missparsed the previous message. Tsung has a way to monitor OS level information, and I guess adding the build/configuration would be... as easy as adding it to pgbench :) > And as a general commentary on the vision here, tsung will never fit into > this anyway because "something that can run on the buildfarm machines with > the software they already have installed" is the primary target. I don't > see anything about tsung so interesting that it trumps that priority, even > though it is an interesting tool. I though we might talk about a performance farm which would be quite different, if only because to sustain a high enough client load you might need more than one injector machine targeting a given server at once. But if you target the buildfarm, introducing new dependencies does sound like a problem (that I can't evaluate the importance of). Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] mysql to postgresql, performance questions
Corin writes: > I'm running quite a large social community website (250k users, 16gb > database). We are currently preparing a complete relaunch and thinking about > switching from mysql 5.1.37 innodb to postgresql 8.4.2. The database server > is a dual dualcore operton 2216 with 12gb ram running on debian amd64. > > For a first impression I ran a simple query on our users table (snapshot For more serious impression and realistic figures, you could use tsung atop the http side of your application and compare how it performs given a certain load of concurrent users. In your situation I'd expect to win a lot going to PostgreSQL on concurrency scaling. Tsung is made to test that. http://tsung.erlang-projects.org/ Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] shared_buffers advice
Greg Smith writes: > I'm not sure how to make progress on similar ideas about > tuning closer to the filesystem level without having something automated > that takes over the actual benchmark running and data recording steps; it's > just way too time consuming to do those right now with every tool that's > available for PostgreSQL so far. That's the problem I work on, there are > easily a half dozen good ideas for improvements here floating around where > coding time is dwarfed by required performance validation time. I still think the best tool around currently for this kind of testing is tsung, but I've yet to have the time to put money where my mouth is, as they say. Still, I'd be happy to take some time a help you decide if it's the tool you want to base your performance testing suite on or not. http://tsung.erlang-projects.org/ Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] queries with subquery constraints on partitioned tables not optimized?
Tom Lane writes:
> "Davor J." writes:
>> Now, if one takes a subquery for "1", the optimizer evaluates it first
>> (let's say to "1"), but then searches for it (sequentially) in every
>> partition, which, for large partitions, can be very time-consuming and goes
>> beyond the point of partitioning.
>
> No, the optimizer doesn't "evaluate it first". Subqueries aren't ever
> assumed to reduce to constants. (If you actually do have a constant
> expression, why don't you just leave out the word SELECT?)
It's easy to experience the same problem with a JOIN you'd want to
happen at the partition level that the planner will apply on the Append
Node.
I'm yet to figure out if 8.4 is smarter about this, meanwhile I'm using
array tricks to force the push-down.
WHERE ...
AND service = ANY ((SELECT array_accum(id) FROM services WHERE x=281)
|| (SELECT array_accum(id) FROM services WHERE y=281))
It happens that I need the array concatenation more than the = ANY
operator (as compared to IN), so I also have queries using = ANY
('{}':int[] || (SELECT array_accum(x) ...)) to really force the planner
into doing the join in the partitions rather than after the Append has
taken place.
Regards,
--
dim
PS: If you're interrested into complete examples, I'll be able to
provide for them in private.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] performance config help
Bob Dusek writes: > So, pgBouncer is pretty good. It doesn't appear to be as good as > limiting TCON and using pconnect, but since we can't limit TCON in a > production environment, we may not have a choice. You can still use pconnect() with pgbouncer, in transaction mode, if your application is compatible with that (no advisory locks or other session level tricks). Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Message queue table - strange performance drop with changing limit size.
Jesper Krogh writes: > I have a "message queue" table, that contains in the order of 1-10m > "messages". It is implemented using TheSchwartz: > http://search.cpan.org/~bradfitz/TheSchwartz-1.07/lib/TheSchwartz.pm One way to approach queueing efficiently with PostgreSQL is to rely on PGQ. New upcoming 3.0 version (alpha1 has been released) contains the basics for having cooperative consumers, stable version (2.1.10) only allows multiple consumers to all do the same work (think replication). http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Load experimentation
Scott Marlowe writes: > That's a lot of work to get to COPY. Well, yes. I though about it this way only after having read that OP is uneasy with producing another format from his source data, and considering it's a one-shot operation. Ah, tradeoffs, how to find the right one! -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Load experimentation
Hi, Ben Brehmer writes: > By "Loading data" I am implying: "psql -U postgres -d somedatabase -f > sql_file.sql". The sql_file.sql contains table creates and insert > statements. There are no > indexes present nor created during the load. > > OS: x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red > Hat 4.1.2-44) > > PostgreSQL: I will try upgrading to latest version. > > COPY command: Unfortunately I'm stuck with INSERTS due to the nature > this data was generated (Hadoop/MapReduce). What I think you could do is the followings: - switch to using 8.4 - load your files in a *local* database - pg_dump -Fc - now pg_restore -j X on the amazon setup That way you will be using COPY rather than INSERTs and parallel loading built-in pg_restore (and optimisations of when to add the indexes and constraints). The X is to choose depending on the IO power and the number of CPU... Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] maintaining a reference to a fetched row
Brian Karlak writes: > I have a simple queuing application written on top of postgres which I'm > trying to squeeze some more performance out of. Have you tried to write a custom PGQ consumer yet? http://wiki.postgresql.org/wiki/PGQ_Tutorial Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Cédric Villemain writes: >> If you want the latest and greatest, then you can use Debian testing. > > testing and sid are usually the same with a 15 days delay. And receive no out-of-band security updates, so you keep the holes for 3 days when lucky, and 10 to 15 days otherwise, when choosing testing. So consider stable first, and if you like to be in danger every time you dist-upgrade while *having* to do it each and every day, sid is for your production servers. > I strongly suggets to have a debian lenny and to backport newer packages if > really required (like postgres 8.4). Debian come with good tools to achieve > that (and there is debian-backport repository, sure) stable + backports + volatile (when it makes sense) is a perfect choice :) -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Tom Lane writes: > It's worth your time to learn how to do this on whatever system you > prefer to use. Then, if you're ever in a situation where you really > need patch XYZ right now, you can easily add that patch to the package > sources and rebuild a custom version that will play nicely within the > distro's package system --- right up to being automatically replaced > when the next regular release does come out. I recently had to do just that (local fix a contrib module failure, pg_freespacemap). It looks like this when you're using debian: $ apt-get source postgresql-8.3 $ cd postgresql-8.3-8.3.7 $ cp /path/to/patch.diff debian/patches/13-pg_fsm.diff --- pick yourself next available id $ $EDITOR debian/changelog $ debuild Now you have a new .deb you want to distribute for upgrades (targeting preprod first, of course). The changelog editing is best done knowing what is a NMU and how to represent it there, and how version sorting works; that's the key to automatic overwrite at next official minor upgrade, and it allows to put the version under your name, so that you can GnuPG sign the packages at the end of the debuild process. http://www.debian.org/doc/developers-reference/pkgs.html#nmu Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow select times on select with xpath
astro77 writes: > Kevin Grittner wrote: >> I would try to minimize how many XML values it had to read, parse, and >> search. The best approach that comes to mind would be to use tsearch2 >> techniques (with a GIN or GiST index on the tsvector) to identify >> which rows contain 'fdc3da1f-060f-4c34-9c30-d9334d9272ae', and use AND >> to combine that with your xpath search. > > Thanks Kevin. I thought about using tsearch2 but I need to be able to select > exact values on other numerical queries and cannot use "contains" queries. > It's got to be fast so I cannot have lots of records returned and have to do > secondary processing on the xml for the records which contain the exact > value I'm looking for. This is one of the reasons I moved from using Lucene > for searching. I hope this makes sense. I think he meant something following this skeleton: SELECT ... FROM ( SELECT ... FROM ... WHERE /* insert preliminary filtering here */ ) WHERE /* insert xpath related filtering here */ Hopefully you have a preliminary filtering available that's restrictive enough for the xpath filtering to only have to check few rows. Kevin proposes that this preliminary filtering be based on Tsearch with an adequate index (GiST for data changing a lot, GIN for pretty static set). As you can see the two-steps filtering can be done in a single SQL query. Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Persistent Plan Cache
Hi, Heikki Linnakangas writes: > Joshua Rubin wrote: >> We "hardcode" the parts of the where clause so that the prepared plan >> will not vary among the possible partitions of the table. The only >> values that are bound would not affect the planner's choice of table. > > Then you would benefit from using prepared statements in the client, > and/or connection pooling to avoid having to re-prepare because of > reconnecting. And you can do both in a transparent way (wrt pooling) using preprepare. The problem without it is for the application to know when the statement is already prepared (that depends on whether the pooling software will assign a new fresh connection or not). Using preprepare your application skip the point and simply EXECUTE the already prepared statements. http://preprepare.projects.postgresql.org/README.html http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/preprepare/preprepare/ http://packages.debian.org/search?keywords=preprepare Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] View vs Stored Proc Performance
Merlin Moncure writes: > like joining the result to another table...the planner can see > 'through' the view, etc. in a function, the result is fetched first > and materialized without looking at the rest of the query. I though the planner would "see through" SQL language functions and inline them when possible, so they often can make for parametrized views... Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] load / stress testing
Try tsung, dig the archives for a pg specific howto. Tsung is open source and supports multiple protocols. Regards, -- dim Le 31 juil. 2009 à 08:50, Chris a écrit : Hi, Everyone says "load test using your app" - out of interest how does everyone do that at the database level? I've tried playr (https://area51.myyearbook.com/trac.cgi/wiki/Playr) but haven't been able to get it working properly. I'm not sure what other tools are available. TIA. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
Hi, Le 16 juil. 09 à 11:52, Andres Freund a écrit : If I interpret those findings correcty the execution is approx. as fast as DB2, only DB2 is doing automated plan caching while pg is not. If it _really_ is necessary that this is that fast, you can prepare the query like I showed. A form of automatic plan caching by PostgreSQL is possible and available as an extension called "preprepare", which aims to have your statements already prepared by the time you're able to send queries in, at which point you simply EXECUTE and never care about the PREPARE in your code. http://preprepare.projects.postgresql.org/README.html The module is only available for PostgreSQL 8.3 or 8.4, and works in a fully automatic manner only in 8.4 (using local_preload_librairies), and in both cases requires you to: - edit postgresql.conf and restart if preloading, else reload - put your full PREPARE statements into a table - psql -c "EXECUTE myquery;" mydb - enjoy Regards, -- dim PS: soon to be in debian sid for both 8.3 and 8.4, currently in NEW queue -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] tsvector_update_trigger performance?
Also consider on update triggers that you could want to run anyway -- dim Le 25 juin 2009 à 07:45, Craig Ringer a écrit : On Wed, 2009-06-24 at 21:03 -0700, Chris St Denis wrote: This sounds like something that should just be on by default, not a trigger. Is there some reason it would waste the io of writing a new row to disk if nothing has changed? or is it just considered too much unnecessary overhead to compare them? I think the theory is that carefully written applications generally do not generate redundant updates in the first place. An application that avoids redundant updates should not have to pay the cost of redundant update detection and elimination. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] tsvector_update_trigger performance?
Hi, Le 24 juin 09 à 18:29, Alvaro Herrera a écrit : Oleg Bartunov wrote: On Wed, 24 Jun 2009, Chris St Denis wrote: Is tsvector_update_trigger() smart enough to not bother updating a tsvector if the text in that column has not changed? no, you should do check yourself. There are several examples in mailing lists. Or you could try using the supress_redundant_updates_trigger() function that has been included in 8.4 (should be easy to backport) http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/backports/min_update/ http://blog.tapoueh.org/projects.html#sec9 But it won't handle the case where some other random column has changed, but the UPDATE is not affecting the text indexed... -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres replication: dump/restore, PITR, Slony,...?
Hi, Shaul Dar writes: > 1. A staging server, which receives new data and updates the DB > 2. Two web servers that have copies of the DB (essentially read-only) > and answer user queries (with load balancer) [...] > Suggestions? I'd consider WAL Shipping for the staging server and some trigger based asynchronous replication for feeding the web servers. More specifically, I'd have a try at Skytools, using walmgr.py for WAL Shipping and Londiste for replication. http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/Londiste_Tutorial Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hosted servers with good DB disk performance?
"Markus Wanner" writes: > If anybody has ever tried their systems, I'd like to hear back. I wish such > an offering would exist for Europe (guess that's just a matter of time). http://www.niftyname.org/ http://lost-oasis.fr/ It seems to be coming very soon, in France :) -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pointers needed on optimizing slow SQL statements
Віталій Тимчишин writes: > I'd prefer ALTER VIEW SET ANALYZE=true; or CREATE/DROP ANALYZE ; > Also it should be possible to change statistics target for analyzed > columns. Yeah, my idea was ALTER VIEW ENABLE ANALYZE; but that's an easy point to solve if the idea proves helpful. > Such a statement would allow to analyze multi-table correlations. Note > that for view planner should be able to use correlation information > even for queries that do not use view, but may benefit from the > information. That sounds like the hard part of it, but maybe our lovely geniuses will come back and tell: "oh, you can do it this way, easy enough". :) Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pointers needed on optimizing slow SQL statements
Hi, Le 6 juin 09 à 10:50, Simon Riggs a écrit : On Wed, 2009-06-03 at 21:21 -0400, Robert Haas wrote: But, we're not always real clever about selectivity. Sometimes you have to fake the planner out, as discussed here. [...] Fortunately, these kinds of problems are fairly rare, but they can be extremely frustrating to debug. With any kind of query debugging, the first question to ask yourself is "Are any of my selectivity estimates way off?". If the answer to that question is no, you should then ask "Where is all the time going in this plan?". If the answer to the first question is yes, though, your time is usually better spent fixing that problem, because once you do, the plan will most likely change to something a lot better. The Function Index solution works, but it would be much better if we could get the planner to remember certain selectivities. I'm thinking a command like ANALYZE foo [WHERE ] which would specifically analyze the selectivity of the given WHERE clause for use in queries. I don't know the stats subsystem well enough to judge by myself how good this idea is, but I have some remarks about it: - it looks good :) - where to store the clauses to analyze? - do we want to tackle JOIN selectivity patterns too (more than one table)? An extension to the ANALYZE foo WHERE ... idea would be then to be able to analyze random SQL, which could lead to allow for maintaining VIEW stats. Is this already done, and if not, feasible and a good idea? This way one could define a view and have the system analyze the clauses and selectivity of joins etc, then the hard part is for the planner to be able to use those in user queries... mmm... maybe this isn't going to help much? Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Scalability in postgres
Just to say you don't need a mega server to keep thousands connections with Oracle, it's just trivial, nor CPU affinity and other stuff you may or may not need with Sybase :-) Regarding PostgreSQL, I think it'll only benefit to have an integrated connection pooler as it'll make happy all populations anyway: - those who don't like the idea may always disable it :-) - those who have a lot but mostly inactive sessions will be happy to simplify session pooling - those who really seeking for the most optimal workload on their servers will be happy twice: if there are any PG scalability limits, integrated pooler will be in most cases more performant than external; if there are no PG scalability limits - it'll still help to size PG most optimally according a HW or OS capacities.. Rgds, -Dimitri On 6/3/09, Kevin Grittner wrote: > Dimitri wrote: >> Few weeks ago tested a customer application on 16 cores with Oracle: >> - 20,000 sessions in total >> - 70,000 queries/sec >> >> without any problem on a mid-range Sun box + Solaris 10.. > > I'm not sure what point you are trying to make. Could you elaborate? > > (If it's that Oracle doesn't need an external connection pool, then > are you advocating that PostgreSQL include that in the base product?) > > -Kevin > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Scalability in postgres
Few weeks ago tested a customer application on 16 cores with Oracle: - 20,000 sessions in total - 70,000 queries/sec without any problem on a mid-range Sun box + Solaris 10.. Rgds, -Dimitri On 6/3/09, Kevin Grittner wrote: > James Mansion wrote: > >> I'm sure most of us evaluating Postgres from a background in Sybase >> or SQLServer would regard 5000 connections as no big deal. > > Sure, but the architecture of those products is based around all the > work being done by "engines" which try to establish affinity to > different CPUs, and loop through the various tasks to be done. You > don't get a context switch storm because you normally have the number > of engines set at or below the number of CPUs. The down side is that > they spend a lot of time spinning around queue access to see if > anything has become available to do -- which causes them not to play > nice with other processes on the same box. > > If you do connection pooling and queue requests, you get the best of > both worlds. If that could be built into PostgreSQL, it would > probably reduce the number of posts requesting support for bad > configurations, and help with benchmarks which don't use proper > connection pooling for the product; but it would actually not add any > capability which isn't there if you do your homework > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best way to load test a postgresql server
"Kenneth Cox" writes: > On Tue, 02 Jun 2009 05:26:41 -0400, Dimitri Fontaine > wrote: >> I'd recommand having a look at tsung which will be able to replay a >> typical application scenario with as many concurrent users as you want >> to: http://archives.postgresql.org/pgsql-admin/2008-12/msg00032.php >> http://tsung.erlang-projects.org/ >> http://pgfouine.projects.postgresql.org/tsung.html > > I am having a look at tsung and not getting very far yet. Have you had luck > with it and do you really mean as many concurrent users as you want? Last time I used it it was in the context of a web application and to compare PostgreSQL against Informix after a migration. So I used the HTTP protocol support of the injector. Tsung is based on erlang and can be run from more than one node at any time, last time I checked you could run 600 to 800 concurrent clients from each node. Recent versions of erlang allow a much greater number per node, one or two orders of magnitude greater, as I've been told by Tsung's main developer. > I was > hoping to use it to simulate my current load while tuning and making > improvements. So far tsung doesn't appear well suited to my needs. I use > persistent connections; each tsung session uses a new connection. I have > multiple applications that have very usage patterns (some web and largely > idle, some non web and almost saturated); tsung has virtual users choosing > a session based on a probability with think times. I know many programming > languages; tsung (and its error messages) is in erlang. Tsung can be setup as an http or postgresql proxy: in this mode it'll prepare session files for you while you use your application as usual. The thinktime it sees will then get randomized at run time to better reflect real usage. You can define several user arrival phases to see what happens when the load raises then get back to normal traffic. Lots of options, really. Tsung generates statistics and comes with tools to analyze them and provide graphs organized into a web page, one of those tools allow to draw graphs from different simulations onto the same chart, with the same scaling, in order to easily compare results. It seems to me tsung is a good tool for your use case. Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best way to load test a postgresql server
Hi, "Peter Sheats" writes: > I’m about to set up a large instance on Amazon EC2 to be our DB server. > > Before we switch to using it in production I would like to simulate some load on it so that I know what it can handle and so that I can make sure I have the > optimal settings in the config file. > > What is the best strategy out there for doing this? Does anyone know of some resource that talks about doing this? I'd recommand having a look at tsung which will be able to replay a typical application scenario with as many concurrent users as you want to: http://archives.postgresql.org/pgsql-admin/2008-12/msg00032.php http://tsung.erlang-projects.org/ http://pgfouine.projects.postgresql.org/tsung.html If you want to replay your logs at the current production speed and concurrency, see Playr. https://area51.myyearbook.com/trac.cgi/wiki/Playr Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Clustering
Hi, Le 27 mai 09 à 19:57, Alan McKay a écrit : I have done some googling and found a few things on the matter. But am looking for some suggestions from the experts out there. Got any good pointers for reading material to help me get up to speed on PostgreSQL clustering? What options are available? What are the issues? Terminology. I'm pretty new to the whole data-warehouse thing. And once I do all the reading, I'll even be open to product recommendations :-) Depending on your exact needs, which the terminology you're using only allow to guess about, you might enjoy this reading: http://wiki.postgresql.org/wiki/Image:Moskva_DB_Tools.v3.pdf -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hosted servers with good DB disk performance?
Hi, Greg Smith writes: > I keep falling into situations where it would be nice to host a server > somewhere else. Virtual host solutions and the mysterious cloud are no good > for the ones I run into though, as disk performance is important for all the > applications I have to deal with. A french company here is working on several points of interest for you, I'd say. They provide dedicated server renting and are working on their own OpenSource cloud solution, so there's nothing mysterious about it, and you can even run the software in your own datacenter(s). http://lost-oasis.fr/ http://www.niftyname.org/ OK, granted, the company's french and their site too, but the OpenSource cloud solution is in english and the code available in public git repositories (and tarballs). > What I'd love to have is a way to rent a fairly serious piece of dedicated > hardware, ideally with multiple (at least 4) hard drives in a RAID > configuration and a battery-backed write cache. The cache is > negotiable. Linux would be preferred, FreeBSD or Solaris would also work; > not Windows though (see "good DB performance"). > > Is anyone aware of a company that offers such a thing? Did you omit to say "english spoken" as a requirement? :) -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/19/09, Merlin Moncure wrote:
> On Tue, May 19, 2009 at 11:53 AM, Dimitri wrote:
>> On 5/19/09, Merlin Moncure wrote:
>>> On Mon, May 18, 2009 at 6:32 PM, Dimitri wrote:
>>>> Thanks Dave for correction, but I'm also curious where the time is
>>>> wasted in this case?..
>>>>
>>>> 0.84ms is displayed by "psql" once the result output is printed, and I
>>>> got similar time within my client (using libpq) which is not printing
>>>> any output..
>>>
>>> Using libpq? What is the exact method you are using to execute
>>> queries...PQexec?
>>
>> exactly
>>
>>> If you are preparing queries against libpq, the
>>> best way to execute queries is via PQexecPrepared.
>>
>> the query is *once* prepared via PQexec,
>> then it's looping with "execute" via PQexec.
>> Why PQexecPrepared will be better in my case?..
>
> It can be better or worse (usually better). the parameters are
> separated from the query string. Regardless of performance, the
> parametrized interfaces are superior for any queries taking arguments
> and should be used when possible.
you're probably right, but I don't like either when solution become so
complicated - PG has a so elegant way to execute a prepared query!
>
>>> Another note: I would like to point out again that there are possible
>>> negative side effects in using char(n) vs. varchar(n) that IIRC do not
>>> exist in mysql. When you repeat your test I strongly advise switching
>>> to varchar.
>>
>> if it's true for any case, why not just replace CHAR implementation by
>> VARCHAR directly within PG code?..
>
> First, let me explain the difference. char(n) is padded out to 'n' on
> disk and when returned. despite this, the length is still stored so
> there is no real advantage to using the char(n) type except that the
> returned string is of a guaranteed length. mysql, at least the
> particular version and storage engine that I am logged into right now,
> does not do this for char(n). In other words, select cast('abc' as
> char(50)) returns a string of 50 chars on pgsql and 3 chars on mysql.
> I will leave it as an exercise to the reader to figure out whom is
> following the standard. pg's handling of the situation is not
> necessarily optimal, but we just tell everyone to quit using 'char(n)'
> type.
>
> Unless for example your 'NOTE' column is mostly full or mostly null,
> your query is not fair because postgres has to both store and return a
> proportionally greater amount of data. This makes the comparison
> hardly apples to apples. This stuff counts when we are measuring at
> microsecond level.
Good point! I may confirm only at least at the beginning all fields
are fully filled within a database. Will test both engines with
VARCHAR next time to be sure it's not an issue.
>
>>> Another question: how exactly are you connecting to the database?
>>> local machine? if so, domain socket or tcp/ip?
>>
>> local TCP/IP, same as MySQL
>
> would be curious to see if you get different results from domain socket.
at least for PG there was no difference if I remember well.
However, before when I tested on the real network I finished by change
completely my code to reduce a network traffic (initially I've used
cursors), and finally PG traffic was lower or similar to MySQL, it was
an interesting stuff too :-)
Rgds,
-Dimitri
>
> merlin
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/19/09, Scott Carey wrote: > > On 5/19/09 3:46 AM, "Dimitri" wrote: > >> On 5/19/09, Scott Carey wrote: >>> >>> On 5/18/09 3:32 PM, "Dimitri" wrote: >>> >>>> On 5/18/09, Scott Carey wrote: >>>>> Great data Dimitri!' >>>> >>>> Thank you! :-) >>>> >>>>> >>>>> I see a few key trends in the poor scalability: >>>>> >>>>> The throughput scales roughly with %CPU fairly well. But CPU used >>>>> doesn't >>>>> go past ~50% on the 32 core tests. This indicates lock contention. >>>>> >>>> >>>> You should not look on #1 STATs, but on #2 - they are all with the >>>> latest "fixes" - on all of them CPU is used well (90% in pic on >>>> 32cores). >>>> Also, keep in mind these cores are having 2 threads, and from Solaris >>>> point of view they are seen as CPU (so 64 CPU) and %busy is accounted >>>> as for 64 CPU >>>> >>> >>> Well, if the CPU usage is actually higher, then it might not be lock >>> waiting >>> -- it could be spin locks or context switches or cache coherency >>> overhead. >>> Postgres may also not be very SMT friendly, at least on the hardware >>> tested >>> here. >> >> do you mean SMP or CMT? ;-) >> however both should work well with PostgreSQL. I also think about CPU >> affinity - probably it may help to avoid CPU cache misses - but makes >> sense mostly if pooler will be added as a part of PG. > > Symmetric Multi Threading (HyperThreading in Intels marketing terms, other > marketing terms for Sun or IBM). One CPU core that can handle more than one > concurrently executing thread. > Technically, 'SMT' allows instructions in flight from multiple threads at > once in a superscalar Cpu core while some implementations differ and might > technically CMT (one thread or the other, but can switch fast, or a > non-superscalar core). > > For many implementations of 'multiple threads on one CPU core' many of the > processor resources are reduced per thread when it is active -- caches get > split, instruction re-order buffers are split, etc. That is rather hardware > implementation dependant. > > For Intel's SMT (and other similar), spin-locks hurt scalability if they > aren't using new special instructions for the spin to yield pipeline slots > to the other thread. > > Generally, code that stresses common processor resources more than CPU > execution will scale poorly with SMT/CMT etc. All application are scaling well anyway, except if you have any kind of lock contention inside of the application itself or meet any kind of system resource become hot. But well, here we may spend days to discuss :-) > > So I'm not sure about the Postgres details, but the general case of an > application that doesn't benefit from these technologies exists, and there > is a non-zero chance that Postgres has some characteristics of such an app. > >>> >>> (what was the context switch rate? I didn't see that in the data, just >>> mutex spins). >> >> increasing with a load, as this ex.: >> http://dimitrik.free.fr/Report_20090505/5539_dim_STAT_100.html#bmk_CPU_CtxSwit >> ch_100 >> > > Well, on most systems over 100K context switches/sec is a lot. And those > reach 18 /sec. > However, this is 'only' 10 context switches per transaction and less than > 20% system CPU, so maybe those numbers aren't quite as big as they seem. > > Out of curiosity, what was the context switch rate for MySql at its peak > throughput? the main MySQL problem is a mutex locking like here: http://dimitrik.free.fr/Report_20090504/5465_dim_STAT_31.html#bmk_SpinMtx_31 so you have to limit a number of active threads to lower this contention (similar to pooler idea folks told here) and the context switch is even higher (~200K/sec) >> >>> >>> The scalability curve is definitely showing something. Prepared >>> statements >>> were tried, as were most of the other suggestions other than one: >>> >>> What happens if the queries are more complicated (say, they take 15ms >>> server >>> side with a more complicated plan required)? That is a harder question >>> to >>> answer >> >> What I observed is: if planner takes more long time (like initially >> with 8.3.7 and analyze target 1000) the scalability problem is >> appearing more strange - >> http://dimitrik.free.fr/Report_2009
Re: [PERFORM] Any better plan for this query?..
On 5/19/09, Merlin Moncure wrote: > On Mon, May 18, 2009 at 6:32 PM, Dimitri wrote: >> Thanks Dave for correction, but I'm also curious where the time is >> wasted in this case?.. >> >> 0.84ms is displayed by "psql" once the result output is printed, and I >> got similar time within my client (using libpq) which is not printing >> any output.. > > Using libpq? What is the exact method you are using to execute > queries...PQexec? exactly > If you are preparing queries against libpq, the > best way to execute queries is via PQexecPrepared. the query is *once* prepared via PQexec, then it's looping with "execute" via PQexec. Why PQexecPrepared will be better in my case?.. > Also, it's > interesting to see if you can get any benefit from asynchronous > queries (PQsendPrepared), but this might involve more changes to your > application than you are willing to make. > > Another note: I would like to point out again that there are possible > negative side effects in using char(n) vs. varchar(n) that IIRC do not > exist in mysql. When you repeat your test I strongly advise switching > to varchar. if it's true for any case, why not just replace CHAR implementation by VARCHAR directly within PG code?.. > > Another question: how exactly are you connecting to the database? > local machine? if so, domain socket or tcp/ip? local TCP/IP, same as MySQL > What are you doing > with the results...immediately discarding? from PQ side they immediately discarded once all rows are fetched > > One last thing: when you get access to the server, can you run a > custom format query test from pgbench and compare the results to your > test similarly configured (same number of backends, etc) in terms of > tps? I'll try Rgds, -Dimitri -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
The response time is not progressive, it's simply jumping, it's likely since 16 sessions there is a sort of serialization happening somewhere.. As well on 16 sessions the throughput in TPS is near the same as on 8 (response time is only twice bigger for the moment), but on 32 it's dramatically dropping down.. Rgds, -Dimitri On 5/19/09, Simon Riggs wrote: > > On Tue, 2009-05-19 at 14:00 +0200, Dimitri wrote: > >> I may confirm the issue with hash join - it's repeating both with >> prepared and not prepared statements - it's curious because initially >> the response time is lowering near ~1ms (the lowest seen until now) >> and then once workload growing to 16 sessions it's jumping to 2.5ms, >> then with 32 sessions it's 18ms, etc.. > > Is it just bad all the time, or does it get worse over time? > > Do you get the same behaviour as 32 sessions if you run 16 sessions for > twice as long? > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
I may confirm the issue with hash join - it's repeating both with prepared and not prepared statements - it's curious because initially the response time is lowering near ~1ms (the lowest seen until now) and then once workload growing to 16 sessions it's jumping to 2.5ms, then with 32 sessions it's 18ms, etc.. I've retested on 24 isolated cores, so any external secondary effects are avoided. Rgds, -Dimitri On 5/19/09, Dimitri wrote: > On 5/19/09, Simon Riggs wrote: >> >> On Tue, 2009-05-19 at 00:33 +0200, Dimitri wrote: >>> > >>> > In particular, running the tests repeatedly using >>> > H.REF_OBJECT = '01' >>> > rather than varying the value seems likely to benefit MySQL. The >>> >>> let me repeat again - the reference is *random*, >>> the '01' value I've used just to show a query execution >>> plan. >>> >>> also, what is important - the random ID is chosen in way that no one >>> user use the same to avoid deadlocks previously seen with PostgreSQL >>> (see the "Deadlock mystery" note 2 years ago >>> http://dimitrik.free.fr/db_STRESS_BMK_Part1.html#note_4355 ) >> >> OK, didn't pick up on that. >> >> (Like Tom, I was thinking query cache) >> >> Can you comment on the distribution of values for that column? If you >> are picking randomly, this implies distribution is uniform and so I am >> surprised we are mis-estimating the selectivity. > > yes, the distribution of reference values is uniform between > '01' to '001000' (10M), only one OBJECT row by one > reference, and only 20 rows with the same reference in HISTORY table. > >> >>> I think yes (but of course I did not try to replay it several times) >> >> If you could that would be appreciated. We don't want to go chasing >> after something that is not repeatable. > > I'll retry and let you know. > > Rgds, > -Dimitri > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/19/09, Simon Riggs wrote: > > On Tue, 2009-05-19 at 00:33 +0200, Dimitri wrote: >> > >> > In particular, running the tests repeatedly using >> >H.REF_OBJECT = '01' >> > rather than varying the value seems likely to benefit MySQL. The >> >> let me repeat again - the reference is *random*, >> the '01' value I've used just to show a query execution >> plan. >> >> also, what is important - the random ID is chosen in way that no one >> user use the same to avoid deadlocks previously seen with PostgreSQL >> (see the "Deadlock mystery" note 2 years ago >> http://dimitrik.free.fr/db_STRESS_BMK_Part1.html#note_4355 ) > > OK, didn't pick up on that. > > (Like Tom, I was thinking query cache) > > Can you comment on the distribution of values for that column? If you > are picking randomly, this implies distribution is uniform and so I am > surprised we are mis-estimating the selectivity. yes, the distribution of reference values is uniform between '01' to '001000' (10M), only one OBJECT row by one reference, and only 20 rows with the same reference in HISTORY table. > >> I think yes (but of course I did not try to replay it several times) > > If you could that would be appreciated. We don't want to go chasing > after something that is not repeatable. I'll retry and let you know. Rgds, -Dimitri -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/19/09, Scott Carey wrote: > > On 5/18/09 3:32 PM, "Dimitri" wrote: > >> On 5/18/09, Scott Carey wrote: >>> Great data Dimitri!' >> >> Thank you! :-) >> >>> >>> I see a few key trends in the poor scalability: >>> >>> The throughput scales roughly with %CPU fairly well. But CPU used >>> doesn't >>> go past ~50% on the 32 core tests. This indicates lock contention. >>> >> >> You should not look on #1 STATs, but on #2 - they are all with the >> latest "fixes" - on all of them CPU is used well (90% in pic on >> 32cores). >> Also, keep in mind these cores are having 2 threads, and from Solaris >> point of view they are seen as CPU (so 64 CPU) and %busy is accounted >> as for 64 CPU >> > > Well, if the CPU usage is actually higher, then it might not be lock waiting > -- it could be spin locks or context switches or cache coherency overhead. > Postgres may also not be very SMT friendly, at least on the hardware tested > here. do you mean SMP or CMT? ;-) however both should work well with PostgreSQL. I also think about CPU affinity - probably it may help to avoid CPU cache misses - but makes sense mostly if pooler will be added as a part of PG. > > (what was the context switch rate? I didn't see that in the data, just > mutex spins). increasing with a load, as this ex.: http://dimitrik.free.fr/Report_20090505/5539_dim_STAT_100.html#bmk_CPU_CtxSwitch_100 > > The scalability curve is definitely showing something. Prepared statements > were tried, as were most of the other suggestions other than one: > > What happens if the queries are more complicated (say, they take 15ms server > side with a more complicated plan required)? That is a harder question to > answer What I observed is: if planner takes more long time (like initially with 8.3.7 and analyze target 1000) the scalability problem is appearing more strange - http://dimitrik.free.fr/Report_20090505/5521_dim_STAT_18.html - as you see CPU even not used more than 60% , and as you may see spin locks are lowering - CPUs are not spinning for locks, there is something else.. I'm supposing a problem of some kind of synchronization - background processes are not waking up on time or something like this... Then, if more time spent on the query execution itself and not planner: - if it'll be I/O time - I/O will hide everything else until you increase a storage performance and/or add more RAM, but then you come back to the initial issue :-) - if it'll be a CPU time it may be interesting! :-) Rgds, -Dimitri -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
No, Tom, the query cache was off. I put it always explicitly off on MySQL as it has scalability issues. Rgds, -Dimitri On 5/19/09, Tom Lane wrote: > Simon Riggs writes: >> In particular, running the tests repeatedly using >> H.REF_OBJECT = '01' >> rather than varying the value seems likely to benefit MySQL. > > ... mumble ... query cache? > > regards, tom lane > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/18/09, Simon Riggs wrote: > > On Mon, 2009-05-18 at 20:00 +0200, Dimitri wrote: > >> >From my point of view it needs first to understand where the time is >> wasted on a single query (even when the statement is prepared it runs >> still slower comparing to MySQL). > > There is still a significant number of things to say about these numbers > and much tuning still to do, so I'm still confident of improving those > numbers if we needed to. > > In particular, running the tests repeatedly using > H.REF_OBJECT = '01' > rather than varying the value seems likely to benefit MySQL. The let me repeat again - the reference is *random*, the '01' value I've used just to show a query execution plan. also, what is important - the random ID is chosen in way that no one user use the same to avoid deadlocks previously seen with PostgreSQL (see the "Deadlock mystery" note 2 years ago http://dimitrik.free.fr/db_STRESS_BMK_Part1.html#note_4355 ) > distribution of values is clearly non-linear; while Postgres picks a > strange plan for that particular value, I would guess there are also > values for which the MySQL plan is sub-optimal. Depending upon the > distribution of selected data we might see the results go either way. > > What I find worrying is your result of a scalability wall for hash > joins. Is that a repeatable issue? I think yes (but of course I did not try to replay it several times) Rgds, -Dimitri > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/18/09, Scott Carey wrote: > Great data Dimitri!' Thank you! :-) > > I see a few key trends in the poor scalability: > > The throughput scales roughly with %CPU fairly well. But CPU used doesn't > go past ~50% on the 32 core tests. This indicates lock contention. > You should not look on #1 STATs, but on #2 - they are all with the latest "fixes" - on all of them CPU is used well (90% in pic on 32cores). Also, keep in mind these cores are having 2 threads, and from Solaris point of view they are seen as CPU (so 64 CPU) and %busy is accounted as for 64 CPU > Other proof of lock contention are the mutex locks / sec graph which climbs exactly, except no locking was seen on processes while I tried to trace them.. What I think will be needed here is a global and corelated tracing of all PG processes - I did not expect to do it now, but next time > rapidly as the system gets more inefficient (along with context switches). > > Another trend is the system calls/sec which caps out with the test, at about > 400,000 per sec on the peak (non-prepared statement) result. Note that when > the buffer size is 256MB, the performance scales much worse and is slower. > And correlated with this the system calls/sec per transaction is more than > double, at slower throughput. of course, because even the data were cached by filesystem to get them you still need to call a read() system call.. > > Using the OS to cache pages is not as fast as pages in shared_buffers, by a > more significant amount with many cores and higher concurrency than in the > low concurrency case. exactly, it's what I also wanted to demonstrate because I often hear "PG is delegating caching to the filesystem" - and I don't think it's optimal :-) > > The system is largely lock limited in the poor scaling results. This holds > true with or without the use of prepared statements -- which help a some, > but not a lot and don't affect the scalability. we are agree here, but again - 20K mutex spins/sec is a quite low value, that's why I hope on the bigger server it'll be more clear where is a bottleneck :-) Rgds, -Dimitri > > > 4096MB shared buffers, 32 cores, 8.4, read only: > http://dimitrik.free.fr/Report_20090505/5539_dim_STAT_70.html > > 256MB cache, 32 cores, 8.4, read-only: > http://dimitrik.free.fr/Report_20090505/5539_dim_STAT_52.html > > 4096MB shared buffs, 32 cores, 8.4, read only, prepared statements > http://dimitrik.free.fr/Report_20090505/5539_dim_STAT_70.html > > On 5/18/09 11:00 AM, "Dimitri" wrote: > >> Folks, I've just published a full report including all results here: >> http://dimitrik.free.fr/db_STRESS_PostgreSQL_837_and_84_May2009.html >> >> From my point of view it needs first to understand where the time is >> wasted on a single query (even when the statement is prepared it runs >> still slower comparing to MySQL). >> >> Then to investigate on scalability issue I think a bigger server will >> be needed here (I'm looking for 64cores at least :-)) >> >> If you have some other ideas or patches (like Simon) - don't hesitate >> to send them - once I'll get an access to the server again the >> available test time will be very limited.. >> >> Best regards! >> -Dimitri >> >> >> On 5/18/09, Simon Riggs wrote: >>> >>> On Thu, 2009-05-14 at 20:25 +0200, Dimitri wrote: >>> >>>> # lwlock_wait_8.4.d `pgrep -n postgres` >>> >>>>Lock IdMode Combined Time (ns) >>>> FirstLockMgrLock Exclusive 803700 >>>>BufFreelistLock Exclusive 3001600 >>>> FirstLockMgrLock Shared 4586600 >>>> FirstBufMappingLock Exclusive 6283900 >>>> FirstBufMappingLock Shared 21792900 >>> >>> I've published two patches to -Hackers to see if we can improve the read >>> only numbers on 32+ cores. >>> >>> Try shared_buffer_partitions = 256 >>> >>> -- >>> Simon Riggs www.2ndQuadrant.com >>> PostgreSQL Training, Services and Support >>> >>> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Thanks Dave for correction, but I'm also curious where the time is wasted in this case?.. 0.84ms is displayed by "psql" once the result output is printed, and I got similar time within my client (using libpq) which is not printing any output.. Rgds, -Dimitri On 5/18/09, Dave Dutcher wrote: > > What I don't understand is the part where you talking about disabling hash > joins: > >>* result: planner replaced hash join is replaced by merge join >>* execution time: 0.84ms ! >>* NOTE: curiously planner is expecting to execute this query in 0.29ms > - so it's supposed from its logic to be faster, so why this plan is not used > from the beginning???... >> >> Sort (cost=4562.83..4568.66 rows=2329 width=176) (actual > time=0.237..0.237 rows=20 loops=1) >> Sort Key: h.horder >> Sort Method: quicksort Memory: 30kB >> -> Merge Join (cost=4345.89..4432.58 rows=2329 width=176) > (actual time=0.065..0.216 rows=20 loops=1) >> Merge Cond: (s.ref = h.ref_stat) >> -> Index Scan using stat_ref_idx on stat s > (cost=0.00..49.25 rows=1000 width=45) (actual time=0.018..0.089 rows=193 > loops=1) >> -> Sort (cost=4345.89..4351.72 rows=2329 width=135) > (actual time=0.042..0.043 rows=20 loops=1) >> Sort Key: h.ref_stat >> Sort Method: quicksort Memory: 30kB >> -> Index Scan using history_ref_idx on history h > (cost=0.00..4215.64 rows=2329 width=135) (actual time=0.012..0.025 rows=20 > loops=1) >> Index Cond: (ref_object = '01'::bpchar) >> Total runtime: 0.288 ms >>(12 rows) > > The explain analyze ran the query in 0.288 ms. That is the actual time it > took to run the query on the server. It is not an estimate of the time. > You measured 0.84 ms to run the query, which seems to imply either a problem > in one of the timing methods or that 66% of your query execution time is > sending the results to the client. I'm curious how you did you execution > time measurements. > > Dave > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Folks, I've just published a full report including all results here: http://dimitrik.free.fr/db_STRESS_PostgreSQL_837_and_84_May2009.html >From my point of view it needs first to understand where the time is wasted on a single query (even when the statement is prepared it runs still slower comparing to MySQL). Then to investigate on scalability issue I think a bigger server will be needed here (I'm looking for 64cores at least :-)) If you have some other ideas or patches (like Simon) - don't hesitate to send them - once I'll get an access to the server again the available test time will be very limited.. Best regards! -Dimitri On 5/18/09, Simon Riggs wrote: > > On Thu, 2009-05-14 at 20:25 +0200, Dimitri wrote: > >> # lwlock_wait_8.4.d `pgrep -n postgres` > >>Lock IdMode Combined Time (ns) >> FirstLockMgrLock Exclusive 803700 >>BufFreelistLock Exclusive 3001600 >> FirstLockMgrLock Shared 4586600 >> FirstBufMappingLock Exclusive 6283900 >> FirstBufMappingLock Shared 21792900 > > I've published two patches to -Hackers to see if we can improve the read > only numbers on 32+ cores. > > Try shared_buffer_partitions = 256 > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Scott, let me now finish my report and regroup all data together, and then we'll continue discussion as it'll come more in debug/profile phase.. - I'll be not polite from my part to send some tons of attachments to the mail list :-) Rgds, -Dimitri On 5/13/09, Scott Carey wrote: > > On 5/13/09 3:22 AM, "Dimitri" wrote: > >> Hi Scott, >> >> On 5/12/09, Scott Carey wrote: >>> Although nobody wants to support it, he should try the patch that Jignesh >>> K. >>> Shah (from Sun) proposed that makes ProcArrayLock lighter-weight. If it >>> makes 32 cores much faster, then we have a smoking gun. >>> >>> Although everyone here is talking about this as an 'unoptimal' solution, >>> the >>> fact is there is no evidence that a connection pooler will fix the >>> scalability from 16 > 32 cores. >>> Certainly a connection pooler will help most results, but it may not fix >>> the >>> scalability problem. >>> >>> A question for Dimitri: >>> What is the scalability from 16 > 32 cores at the 'peak' load that occurs >>> near 2x the CPU count? Is it also poor? If this is also poor, IMO the >>> community here should not be complaining about this unopimal case -- a >>> connection pooler at that stage does little and prepared statements will >>> increase throughput but not likely alter scalability. >> >> I'm attaching a small graph showing a TPS level on PG 8.4 depending on >> number of cores (X-axis is a number of concurrent users, Y-axis is the >> TPS number). As you may see TPS increase is near linear while moving >> from 8 to 16 cores, while on 32cores even it's growing slightly >> differently, what is unclear is why TPS level is staying limited to >> 11.000 TPS on 32cores. And it's pure read-only workload. >> > > Interesting. What hardware is this, btw? Looks like the 32 core system > probably has 2x the CPU and a bit less interconnect efficiency versus the 16 > core one (which would be typical). > Is the 16 core case the same, but with fewer cores per processor active? Or > fewer processors total? > Understanding the scaling difference may require a better understanding of > the other differences besides core count. > >>> >>> If that result scales, then the short term answer is a connection pooler. >>> >>> In the tests that Jingesh ran -- making the ProcArrayLock faster helped >>> the >>> case where connections = 2x the CPU core count quite a bit. >>> >>> The thread about the CPU scalability is "Proposal of tunable fix for >>> scalability of 8.4", originally posted by "Jignesh K. Shah" >>> , March 11 2009. >>> >>> It would be very useful to see results of this benchmark with: >>> 1. A Connection Pooler >> >> will not help, as each client is *not* disconnecting/reconnecting >> during the test, as well PG is keeping well even 256 users. And TPS >> limit is reached already on 64 users, don't think pooler will help >> here. >> > > Actually, it might help a little. Postgres has a flaw that makes backends > block on a lock briefly based on the number of total backends -- active or > completely passive. Your tool has some (very small) user-side delay and a > connection pooler would probably allow 64 of your users to efficiently 'fit' > in 48 or so connection pooler slots. > > It is not about connecting and disconnecting in this case, its about > minimizing Postgres' process count. If this does help, it would hint at > certain bottlenecks. If it doesn't it would point elsewhere (and quiet some > critics). > > However, its unrealistic for any process-per-connection system to have less > backends than about 2x the core count -- else any waiting on I/O or network > will just starve CPU. So this would just be done for research, not a real > answer to making it scale better. > > For those who say "but, what if its I/O bound! You don't need more > backends then!": Well you don't need more CPU either if you're I/O bound. > By definition, CPU scaling tests imply the I/O can keep up. > > >>> 2. Jignesh's patch >> >> I've already tested it and it did not help in my case because the real >> problem is elsewhere.. (however, I did not test it yet with my latest >> config params) >> > > Great to hear that! -- That means this case is probably not ProcArrayLock. > If its Solaris, could we get: > 1. What is the CPU stats when it is in the inefficient
Re: [PERFORM] Any better plan for this query?..
It's absolutely great! it'll not help here because a think time is 0. but for any kind of solution with a spooler it's a must to try! Rgds, -Dimitri On 5/13/09, Dimitri Fontaine wrote: > Hi, > > Le 13 mai 09 à 18:42, Scott Carey a écrit : >>> will not help, as each client is *not* disconnecting/reconnecting >>> during the test, as well PG is keeping well even 256 users. And TPS >>> limit is reached already on 64 users, don't think pooler will help >>> here. >> >> Actually, it might help a little. Postgres has a flaw that makes >> backends >> block on a lock briefly based on the number of total backends -- >> active or >> completely passive. Your tool has some (very small) user-side delay >> and a >> connection pooler would probably allow 64 of your users to >> efficiently 'fit' >> in 48 or so connection pooler slots. > > It seems you have think time, and I'm only insisting on what Scott > said, but having thinktime means a connection pool can help. Pgbouncer > is a good choice because it won't even attempt to parse the queries, > and it has a flexible configuration. > >>>> 3. Prepared statements >>> yes, I'm preparing this test. > > It's possible to use prepared statement and benefit from pgbouncer at > the same time, but up until now it requires the application to test > whether its statements are already prepared at connection time, > because the application is not controlling when pgbouncer is reusing > an existing backend or giving it a fresh one. > > As I think I need this solution too, I've coded a PG module to scratch > that itch this morning, and just published it (BSD licenced) on > pgfoundry: >http://preprepare.projects.postgresql.org/README.html >http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/preprepare/preprepare/ > > With this module and the proper pgbouncer setup (connect_query='SELECT > prepare_all();') the application has no more to special case the fresh- > backend-nothing-prepared case, it's all transparent, just replace your > SELECT query with its EXECUTE foo(x, y, z) counter part. > > I've took the approach to setup the prepared statements themselves > into a table with columns name and statement, this latter one > containing the full PREPARE SQL command. There's a custom variable > preprepare.relation that has to be your table name (shema qualified). > Each statement that you then put in there will get prepared when you > SELECT prepare_all(); > > Hope this helps, regards, > -- > dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Folks, sorry, I'm outpassed little bit by the events :-)) I've finished tests with PREPARE/EXECUTE - it's much faster of course, and the max TSP is 15.000 now on 24 cores! - I've done various tests to see where is the limit bottleneck may be present - it's more likely something timer or interrupt based, etc. Nothing special via DTrace, or probably it'll say you more things then me, but for a 10sec period it's quite small wait time: # lwlock_wait_8.4.d `pgrep -n postgres` Lock IdMode Count FirstBufMappingLock Exclusive 1 FirstLockMgrLock Exclusive 1 BufFreelistLock Exclusive 3 FirstBufMappingLock Shared 4 FirstLockMgrLock Shared 4 Lock IdMode Combined Time (ns) FirstLockMgrLock Exclusive 803700 BufFreelistLock Exclusive 3001600 FirstLockMgrLock Shared 4586600 FirstBufMappingLock Exclusive 6283900 FirstBufMappingLock Shared 21792900 On the same time those lock waits are appearing only on 24 or 32 cores. I'll plan to replay this case on the bigger server (64 cores or more) - it'll be much more evident if the problem is in locks. Currently I'm finishing my report with all data all of you asked (system graphs, pgsql, and other). I'll publish it on my web site and send you a link. Rgds, -Dimitri On 5/14/09, Simon Riggs wrote: > > On Tue, 2009-05-12 at 14:28 +0200, Dimitri wrote: > >> As problem I'm considering a scalability issue on Read-Only workload - >> only selects, no disk access, and if on move from 8 to 16 cores we >> gain near 100%, on move from 16 to 32 cores it's only 10%... > > Dimitri, > > Will you be re-running the Read-Only tests? > > Can you run the Dtrace script to assess LWlock contention during the > run? > > Would you re-run the tests with a patch? > > Thanks, > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi, Le 13 mai 09 à 18:42, Scott Carey a écrit : will not help, as each client is *not* disconnecting/reconnecting during the test, as well PG is keeping well even 256 users. And TPS limit is reached already on 64 users, don't think pooler will help here. Actually, it might help a little. Postgres has a flaw that makes backends block on a lock briefly based on the number of total backends -- active or completely passive. Your tool has some (very small) user-side delay and a connection pooler would probably allow 64 of your users to efficiently 'fit' in 48 or so connection pooler slots. It seems you have think time, and I'm only insisting on what Scott said, but having thinktime means a connection pool can help. Pgbouncer is a good choice because it won't even attempt to parse the queries, and it has a flexible configuration. 3. Prepared statements yes, I'm preparing this test. It's possible to use prepared statement and benefit from pgbouncer at the same time, but up until now it requires the application to test whether its statements are already prepared at connection time, because the application is not controlling when pgbouncer is reusing an existing backend or giving it a fresh one. As I think I need this solution too, I've coded a PG module to scratch that itch this morning, and just published it (BSD licenced) on pgfoundry: http://preprepare.projects.postgresql.org/README.html http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/preprepare/preprepare/ With this module and the proper pgbouncer setup (connect_query='SELECT prepare_all();') the application has no more to special case the fresh- backend-nothing-prepared case, it's all transparent, just replace your SELECT query with its EXECUTE foo(x, y, z) counter part. I've took the approach to setup the prepared statements themselves into a table with columns name and statement, this latter one containing the full PREPARE SQL command. There's a custom variable preprepare.relation that has to be your table name (shema qualified). Each statement that you then put in there will get prepared when you SELECT prepare_all(); Hope this helps, regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
The idea is good, but *only* pooling will be not enough. I mean if all what pooler is doing is only keeping no more than N backends working - it'll be not enough. You never know what exactly your query will do - if you choose your N value to be sure to not overload CPU and then some of your queries start to read from disk - you waste your idle CPU time because it was still possible to run other queries requiring CPU time rather I/O, etc... I wrote some ideas about an "ideal" solution here (just omit the word "mysql" - as it's a theory it's valable for any db engine): http://dimitrik.free.fr/db_STRESS_MySQL_540_and_others_Apr2009.html#note_5442 Rgds, -Dimitri On 5/13/09, Kevin Grittner wrote: > Glenn Maynard wrote: >> I'm sorry, but I'm confused. Everyone keeps talking about >> connection pooling, but Dimitri has said repeatedly that each client >> makes a single connection and then keeps it open until the end of >> the test, not that it makes a single connection per SQL query. >> Connection startup costs shouldn't be an issue. Am I missing >> something here? > > Quite aside from the overhead of spawning new processes, if you have > more active connections than you have resources for them to go after, > you just increase context switching and resource contention, both of > which have some cost, without any offsetting gains. That would tend > to explain why performance tapers off after a certain point. A > connection pool which queues requests prevents this degradation. > > It would be interesting, with each of the CPU counts, to profile > PostgreSQL at the peak of each curve to see where the time goes when > it is operating with an optimal poolsize. Tapering after that point > is rather uninteresting, and profiles would be less useful beyond that > point, as the noise from the context switching and resource contention > would make it harder to spot issues that really matter.. > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On MySQL there is no changes if I set the number of sessions in the config file to 400 or to 2000 - for 2000 it'll just allocate more memory. After latest fix with default_statistics_target=5, version 8.3.7 is running as fast as 8.4, even 8.4 is little little bit slower. I understand your position with a pooler, but I also want you think about idea that 128 cores system will become a commodity server very soon, and to use these cores on their full power you'll need a database engine capable to run 256 users without pooler, because a pooler will not help you here anymore.. Rgds, -Dimitri On 5/12/09, Robert Haas wrote: > On Tue, May 12, 2009 at 11:22 AM, Dimitri wrote: >> Robert, what I'm testing now is 256 users max. The workload is growing >> progressively from 1, 2, 4, 8 ... to 256 users. Of course the Max >> throughput is reached on the number of users equal to 2 * number of >> cores, but what's important for me here - database should continue to >> keep the workload! - response time regressing, but the troughput >> should remain near the same. >> >> So, do I really need a pooler to keep 256 users working?? - I don't >> think so, but please, correct me. > > Not an expert on this, but there has been a lot of discussion of the > importance of connection pooling in this space. Is MySQL still faster > if you lower max_connections to a value that is closer to the number > of users, like 400 rather than 2000? > >> BTW, I did not look to put PostgreSQL in bad conditions - the test is >> the test, and as I said 2 years ago PostgreSQL outperformed MySQL on >> the same test case, and there was nothing done within MySQL code to >> improve it explicitly for db_STRESS.. And I'm staying pretty honest >> when I'm testing something. > > Yeah but it's not really clear what that something is. I believe you > said upthread that PG 8.4 beta 1 is faster than PG 8.3.7, but PG 8.4 > beta 1 is slower than MySQL 5.4 whereas PG 8.3.7 was faster than some > older version of MySQL. So PG got faster and MySQL got faster, but > they sped things up more than we did. If our performance were getting > WORSE, I'd be worried about that, but the fact that they were able to > make more improvement on this particular case than we were doesn't > excite me very much. Sure, I'd love it if PG were even faster than it > is, and if you have a suggested patch please send it in... or if you > want to profile it and send the results that would be great too. But > I guess my point is that the case of a very large number of > simultaneous users with pauses-for-thought between queries has already > been looked at in the very recent past in a way that's very similar to > what you are doing (and by someone who works at the same company you > do, no less!) so I'm not quite sure why we're rehashing the issue. > > ...Robert > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
I'm also confused, but seems discussion giving also other ideas :-) But yes, each client is connecting to the database server only *once*. To presice how the test is running: - 1 client is started => 1 in total - sleep ... - 1 another client is started => 2 in total - sleep .. - 2 another clients are started => 4 in total - sleep .. ... ... ===> 256 in total - sleep ... - kill clients So I even able to monitor how each new client impact all others. The test kit is quite flexible to prepare any kind of stress situations. Rgds, -Dimitri On 5/12/09, Glenn Maynard wrote: > I'm sorry, but I'm confused. Everyone keeps talking about connection > pooling, but Dimitri has said repeatedly that each client makes a > single connection and then keeps it open until the end of the test, > not that it makes a single connection per SQL query. Connection > startup costs shouldn't be an issue. Am I missing something here? > test(N) starts N clients, each client creates a single connection and > hammers the server for a while on that connection. test(N) is run for > N=1,2,4,8...256. This seems like a very reasonable test scenario. > > -- > Glenn Maynard > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Scott, On 5/12/09, Scott Carey wrote: > Although nobody wants to support it, he should try the patch that Jignesh K. > Shah (from Sun) proposed that makes ProcArrayLock lighter-weight. If it > makes 32 cores much faster, then we have a smoking gun. > > Although everyone here is talking about this as an 'unoptimal' solution, the > fact is there is no evidence that a connection pooler will fix the > scalability from 16 > 32 cores. > Certainly a connection pooler will help most results, but it may not fix the > scalability problem. > > A question for Dimitri: > What is the scalability from 16 > 32 cores at the 'peak' load that occurs > near 2x the CPU count? Is it also poor? If this is also poor, IMO the > community here should not be complaining about this unopimal case -- a > connection pooler at that stage does little and prepared statements will > increase throughput but not likely alter scalability. I'm attaching a small graph showing a TPS level on PG 8.4 depending on number of cores (X-axis is a number of concurrent users, Y-axis is the TPS number). As you may see TPS increase is near linear while moving from 8 to 16 cores, while on 32cores even it's growing slightly differently, what is unclear is why TPS level is staying limited to 11.000 TPS on 32cores. And it's pure read-only workload. > > If that result scales, then the short term answer is a connection pooler. > > In the tests that Jingesh ran -- making the ProcArrayLock faster helped the > case where connections = 2x the CPU core count quite a bit. > > The thread about the CPU scalability is "Proposal of tunable fix for > scalability of 8.4", originally posted by "Jignesh K. Shah" > , March 11 2009. > > It would be very useful to see results of this benchmark with: > 1. A Connection Pooler will not help, as each client is *not* disconnecting/reconnecting during the test, as well PG is keeping well even 256 users. And TPS limit is reached already on 64 users, don't think pooler will help here. > 2. Jignesh's patch I've already tested it and it did not help in my case because the real problem is elsewhere.. (however, I did not test it yet with my latest config params) > 3. Prepared statements > yes, I'm preparing this test. > #3 is important, because prepared statements are ideal for queries that > perform well with low statistics_targets, and not ideal for those that > require high statistics targets. Realistically, an app won't have more than > a couple dozen statement forms to prepare. Setting the default statistics > target to 5 is just a way to make some other query perform bad. Agree, but as you may have a different statistic target *per* table it should not be a problem. What is sure - all time spent on parse and planner will be removed here, and the final time should be a pure execution. Rgds, -Dimitri > > > On 5/12/09 10:53 AM, "Alvaro Herrera" wrote: > >> Andres Freund escribió: >> >>> Naturally it would still be nice to be good in this not optimal >>> workload... >> >> I find it hard to justify wasting our scarce development resources into >> optimizing such a contrived workload. >> >> -- >> Alvaro Herrera >> http://www.CommandPrompt.com/ >> The PostgreSQL Company - Command Prompt, Inc. >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > <> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/12/09, Robert Haas wrote: > On Tue, May 12, 2009 at 1:00 PM, Dimitri wrote: >> On MySQL there is no changes if I set the number of sessions in the >> config file to 400 or to 2000 - for 2000 it'll just allocate more >> memory. > > I don't care whether the setting affects the speed of MySQL. I want > to know if it affects the speed of PostgreSQL. the problem is they both have "max_connections" parameter, so as you asked for MySQL I answered for MySQL, did not test yet for PostgreSQL, will be in the next series.. > >> After latest fix with default_statistics_target=5, version 8.3.7 is >> running as fast as 8.4, even 8.4 is little little bit slower. >> >> I understand your position with a pooler, but I also want you think >> about idea that 128 cores system will become a commodity server very >> soon, and to use these cores on their full power you'll need a >> database engine capable to run 256 users without pooler, because a >> pooler will not help you here anymore.. > > So what? People with 128-core systems will not be running trivial > joins that return in 1-2ms and have one second think times between > them. And if they are, and if they have nothing better to do than > worry about whether MySQL can process those queries in 1/2000th of the > think time rather than 1/1000th of the think time, then they can use > MySQL. If we're going to worry about performance on 128-core system, > we would be much better advised to put our efforts into parallel query > execution than how many microseconds it takes to execute very simple > queries. Do you really think nowdays for example a web forum application having PG as a backend will have queries running slower than 1-2ms to print a thread message within your browser??? or banking transactions?? > > Still, I have no problem with making PostgreSQL faster in the case > you're describing. I'm just not interested in doing it on my own time > for free. I am sure there are a number of people who read this list > regularly who would be willing to do it for money, though. Maybe even > me. :-) > > ...Robert > You don't need to believe me, but I'm doing it for free - I still have my work to finish in parallel :-)) And on the same time I don't see any other way to learn and improve my knowledge, but nobody is perfect :-)) Rgds, -Dimitri -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi, Le 12 mai 09 à 18:32, Robert Haas a écrit : implement this same logic internally? IOW, when a client disconnects, instead of having the backend exit immediately, have it perform the equivalent of DISCARD ALL and then stick around for a minute or two and, if a new connection request arrives within that time, have the old backend handle the new connection... A much better idea to solve this, in my opinion, would be to have pgbouncer as a postmaster child, integrated into PostgreSQL. It allows for choosing whether you want session pooling, transaction pooling or statement pooling, which is a more deterministic way to choose when your client connection will benefit from a fresh backend or an existing one. And it's respecting some backend timeouts etc. It's Open-Source proven technology, and I think I've heard about some PostgreSQL distribution where it's already a postmaster's child. And when associated with Hot Standby (and Sync Wal Shipping), having a connection pooler in -core could allow for transparent Read-Write access to the slave: at the moment you need an XID (and when connected on the slave), the backend could tell the pgbouncer process to redirect the connection to the master. With such a feature, you don't have to build client side high availability, just connect to either the master or the slave and be done with it, whatever the SQL you're gonna run. Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
No, they keep connections till the end of the test. Rgds, -Dimitri On 5/12/09, Joshua D. Drake wrote: > On Tue, 2009-05-12 at 17:22 +0200, Dimitri wrote: >> Robert, what I'm testing now is 256 users max. The workload is growing >> progressively from 1, 2, 4, 8 ... to 256 users. Of course the Max >> throughput is reached on the number of users equal to 2 * number of >> cores, but what's important for me here - database should continue to >> keep the workload! - response time regressing, but the troughput >> should remain near the same. >> >> So, do I really need a pooler to keep 256 users working?? - I don't >> think so, but please, correct me. > > If they disconnect and reconnect yes. If they keep the connections live > then no. > > Joshua D. Drake > > -- > PostgreSQL - XMPP: jdr...@jabber.postgresql.org >Consulting, Development, Support, Training >503-667-4564 - http://www.commandprompt.com/ >The PostgreSQL Company, serving since 1997 > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
On 5/12/09, Stefan Kaltenbrunner wrote: > Dimitri wrote: >> Hi Stefan, >> >> sorry, I did not have a time to bring all details into the toolkit - >> but at least I published it instead to tell a "nice story" about :-) > > fair point and appreciated. But it seems important that benchmarking > results can be verified by others as well... until now there were only people running Solaris or Linux :-)) > >> >> The client process is a binary compiled with libpq. Client is >> interpreting a scenario script and publish via SHM a time spent on >> each SQL request. I did not publish sources yet as it'll also require >> to explain how to compile them :-)) So for the moment it's shipped as >> a freeware, but with time everything will be available (BTW, you're >> the first who asking for sources (well, except IBM guys who asked to >> get it on POWER boxes, but it's another story :-)) > > well there is no licence tag(or a copyright notice) or anything als > associated with the download which makes it a bit harder than it really > needs to be. > The reason why I was actually looking for the source is that all my > available benchmark platforms are none of the ones you are providing > binaries for which kinda reduces its usefulness. > agree, will improve this point >> >> What is good is each client is publishing *live* its internal stats an >> we're able to get live data and follow any kind of "waves" in >> performance. Each session is a single process, so there is no >> contention between clients as you may see on some other tools. The >> current scenario script contains 2 selects (representing a Read >> transaction) and delete/insert/update (representing Write >> transaction). According a start parameters each client executing a >> given number Reads per Write. It's connecting on the beginning and >> disconnecting at the end of the test. > > well I have seen clients getting bottlenecked internally (like wasting > more time in getting rid/absorbing of the actual result than it took the > server to generate the answer...). > How sure are you that your "live publishing of data" does not affect the > benchmark results(because it kinda generates an artifical think time) > for example? On all my test tools client are publishing their data via shared memory segment (ISM), all they do is just *incrementing* their current stats values and continuing their processing. Another dedicated program should be executed to print these stats - it's connecting to the same SHM segment and printing a *difference* between values for the current and the next interval. Let me know if you need more details. > But what I get from your answer is that you are basically doing one > connect/disconnect per client and the testcase you are talking about has > 256 clients? Exactly, only one connect/disconnect per test, and number of clients is growing progressively from 1, 2, 4, 8, 16, .. to 256 Rgds, -Dimitri > > > Stefan > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] What is the most optimal config parameters to keep stable write TPS ?..
Good point! I missed it.. - will 20MB be enough? Rgds, -Dimitri On 5/12/09, Julian v. Bock wrote: > Hi > >>>>>> "D" == Dimitri writes: > > D> current postgresql.conf: > > D> # > D> max_connections = 2000 # (change requires restart) > D> temp_buffers = 200MB > > temp_buffers are kept per connection and not freed until the session > ends. If you use some kind of connection pooling this can eat up a lot > of ram that could be used for caching instead. > > Regards, > Julian > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Robert, what I'm testing now is 256 users max. The workload is growing progressively from 1, 2, 4, 8 ... to 256 users. Of course the Max throughput is reached on the number of users equal to 2 * number of cores, but what's important for me here - database should continue to keep the workload! - response time regressing, but the troughput should remain near the same. So, do I really need a pooler to keep 256 users working?? - I don't think so, but please, correct me. BTW, I did not look to put PostgreSQL in bad conditions - the test is the test, and as I said 2 years ago PostgreSQL outperformed MySQL on the same test case, and there was nothing done within MySQL code to improve it explicitly for db_STRESS.. And I'm staying pretty honest when I'm testing something. Rgds, -Dimitri On 5/12/09, Robert Haas wrote: > On Tue, May 12, 2009 at 8:59 AM, Dimitri wrote: >> Wait wait, currently I'm playing the "stress scenario", so there are >> only 256 sessions max, but thing time is zero (full stress). Scenario >> with 1600 users is to test how database is solid just to keep a huge >> amount of users, but doing only one transaction per second (very low >> global TPS comparing to what database is able to do, but it's testing >> how well its internals working to manage the user sessions). > > Didn't we beat this to death in mid-March on this very same list? > Last time I think it was Jignesh Shah. AIUI, it's a well-known fact > that PostgreSQL doesn't do very well at this kind of workload unless > you use a connection pooler. > > *goes and checks the archives* Sure enough, 116 emails under the > subject line "Proposal of tunable fix for scalability of 8.4". > > So, if your goal is to find a scenario under which PostgreSQL performs > as badly as possible, congratulations - you've discovered the same > case that we already knew about. Obviously it would be nice to > improve it, but IIRC so far no one has had any very good ideas on how > to do that. If this example mimics a real-world workload that you > care about, and if using a connection pooler is just not a realistic > option in that scenario for whatever reason, then you'd be better off > working on how to fix it than on measuring it, because it seems to me > we already know it's got problems, per previous discussions. > > ...Robert > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Stefan, sorry, I did not have a time to bring all details into the toolkit - but at least I published it instead to tell a "nice story" about :-) The client process is a binary compiled with libpq. Client is interpreting a scenario script and publish via SHM a time spent on each SQL request. I did not publish sources yet as it'll also require to explain how to compile them :-)) So for the moment it's shipped as a freeware, but with time everything will be available (BTW, you're the first who asking for sources (well, except IBM guys who asked to get it on POWER boxes, but it's another story :-)) What is good is each client is publishing *live* its internal stats an we're able to get live data and follow any kind of "waves" in performance. Each session is a single process, so there is no contention between clients as you may see on some other tools. The current scenario script contains 2 selects (representing a Read transaction) and delete/insert/update (representing Write transaction). According a start parameters each client executing a given number Reads per Write. It's connecting on the beginning and disconnecting at the end of the test. It's also possible to extend it to do other queries, or simply give to each client a different scenario script - what's important is to able to collect then its stats live to understand what's going wrong (if any).. I'm planning to extend it and give an easy way to run it against any database schema, it's only question of time.. Rgds, -Dimitri On 5/12/09, Stefan Kaltenbrunner wrote: > Dimitri wrote: >> Folks, before you start to think "what a dumb guy doing a dumb thing" :-)) >> I'll explain you few details: >> >> it's for more than 10 years I'm using a db_STRESS kit >> (http://dimitrik.free.fr/db_STRESS.html) to check databases >> performance and scalability. Until now I was very happy with results >> it gave me as it stress very well each database engine internals an >> put on light some things I should probably skip on other workloads. >> What do you want, with a time the "fast" query executed before in >> 500ms now runs within 1-2ms - not only hardware was improved but also >> database engines increased their performance a lot! :-)) > > I was attempting to look into that "benchmark" kit a bit but I find the > information on that page a bit lacking :( a few notices: > > * is the sourcecode for the benchmark actually available? the "kit" > seems to contain a few precompiled binaries and some source/headfiles > but there are no building instructions, no makefile or even a README > which makes it really hard to verify exactly what the benchmark is doing > or if the benchmark client might actually be the problem here. > > * there is very little information on how the toolkit talks to the > database - some of the binaries seem to contain a static copy of libpq > or such? > > * how many queries per session is the toolkit actually using - some > earlier comments seem to imply you are doing a connect/disconnect cycle > for every query ist that actually true? > > > Stefan > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Wait wait, currently I'm playing the "stress scenario", so there are only 256 sessions max, but thing time is zero (full stress). Scenario with 1600 users is to test how database is solid just to keep a huge amount of users, but doing only one transaction per second (very low global TPS comparing to what database is able to do, but it's testing how well its internals working to manage the user sessions). I did not plan to do 1600 users test this time (all depends on time :-)) So, do I need to increase WAL buffers for 256 users? My LOG and DATA are placed on separated storage LUNs and controllers from the beginning. I've changed the default 0.5 checkpoint_completion_target to 0.8 now, should I go until 0.95 ?.. Also, to avoid TPS "waves" and bring stability on Read+Write workload I followed advices from a parallel thread: bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 4.0 shared_buffers = 1024MB I've also tried shared_buffers=256MB as it was advised, but then Read-Only workload decreasing performance as PG self caching helps anyway. Also, checkpoint_timeout is 30s now, and of course a huge difference came with moving default_statistics_target to 5 ! -but this one I found myself :-)) Probably checkpoint_timeout may be bigger now with the current settings? - the goal here is to keep Read+Write TPS as stable as possible and also avoid a long recovery in case of system/database/other crash (in theory). Rgds, -Dimitri On 5/12/09, Simon Riggs wrote: > > On Tue, 2009-05-12 at 12:19 +0200, Dimitri wrote: > >> What I'm trying to do now is to understand what exactly is the >> problem. > > You're running with 1600 users, which is above the scalability limit > uncovered (by Sun...) during earlier benchmarking. The scalability > issues are understood but currently considered above the > reasonable-setting limit and so nobody has been inclined to improve > matters. > > You should use a connection concentrator to reduce the number of > sessions down to say 400. > > You're WAL buffers setting is also too low and you will be experiencing > contention on the WALWriteLock. Increase wal_buffers to about x8 where > you have it now. > > You can move pg_xlog to its own set of drives. > > Set checkpoint_completion_target to 0.95. > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
For the moment I'm even not considering any scalability issues on the Read+Write workload - it may always be related to the storage box, and storage latency or controller/cache efficiency may play a lot. As problem I'm considering a scalability issue on Read-Only workload - only selects, no disk access, and if on move from 8 to 16 cores we gain near 100%, on move from 16 to 32 cores it's only 10%... I think I have to replay Read-Only with prepare/execute and check how much it'll help (don't know if there are some internal locking used when a planner is involved).. And yes, I'll try to profile on 32 cores, it makes sense. Rgds, -Dimitri On 5/12/09, Heikki Linnakangas wrote: > Dimitri wrote: >> What I discovered so far with all your help: >> - the impact of a planner >> - the impact of the analyze target >> - the impact of prepare / execute >> - scalability limit on 32 cores > > You've received good advice on how to minimize the impact of the first > three points, and using those techniques should bring a benefit. But I'm > pretty surprised by the bad scalability you're seeing and no-one seems > to have a good idea on where that limit is coming from. At a quick > glance, I don't see any inherent bottlenecks in the schema and workload. > > If you could analyze where the bottleneck is with multiple cores, that > would be great. With something like oprofile, it should be possible to > figure out where the time is spent. > > My first guess would be the WALInsertLock: writing to WAL is protected > by that and it an become a bottleneck with lots of small > UPDATE/DELETE/INSERT transactions. But a profile would be required to > verify that. > > -- >Heikki Linnakangas >EnterpriseDB http://www.enterprisedb.com > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Wow, Simon! :-)) yes, I'm working in Sun Benchmark Center :-)) (I'm not using my Sun email on public lists only to avid a spam) and as came here and asking questions it's probably proving my intentions to show PostgreSQL in its best light, no?.. - I never liked "not honest" comparisons :-)) Regarding your bet: from a very young age I learned a one thing - you take any 2 person who betting for any reason - you'll find in them one idiot and one bastard :-)) idiot - because betting while missing knowledge, and bastard - because knowing the truth is not honset to get a profit from idiots :-)) That's why I never betting in my life, but every time telling the same story in such situation... Did you like it? ;-)) However, no problem to give you a credit as well to all pg-perf list as it provides a very valuable help! :-)) Rgds, -Dimitri On 5/12/09, Simon Riggs wrote: > > On Tue, 2009-05-12 at 12:19 +0200, Dimitri wrote: > >> For my big surprise, MySQL was faster! > > Ours too. > > ** I bet you $1000 that I can improve the performance of your benchmark > results with PostgreSQL. You give me $1000 up-front and if I can't > improve your high end numbers I'll give you $2000 back. Either way, you > name me and link to me from your blog. Assuming you re-run the tests as > requested and give me reasonable access to info and measurements. ** > > I note your blog identifies you as a Sun employee. Is that correct? If > you do not give us the opportunity to improve upon the results then > reasonable observers might be persuaded you did not wish to show > PostgreSQL in its best light. You up for it? > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Folks, before you start to think "what a dumb guy doing a dumb thing" :-)) I'll explain you few details: it's for more than 10 years I'm using a db_STRESS kit (http://dimitrik.free.fr/db_STRESS.html) to check databases performance and scalability. Until now I was very happy with results it gave me as it stress very well each database engine internals an put on light some things I should probably skip on other workloads. What do you want, with a time the "fast" query executed before in 500ms now runs within 1-2ms - not only hardware was improved but also database engines increased their performance a lot! :-)) In 2007 I've published the first public results with PostgreSQL, and it was 2 times faster on that time comparing to MySQL (http://dimitrik.free.fr/db_STRESS_BMK_Part1.html) Last month for the launching of MySQL 5.4 I've done a long series of tests and at the end for my curiosity I've executed the same load against PostgreSQL 8.3.7 to see if MySQL is more close now. For my big surprise, MySQL was faster! As well observations on PG processing bring me a lot of questions - I supposed something was abnormal on PG side, but I did not have too much time to understand what it was exactly (http://dimitrik.free.fr/db_STRESS_MySQL_540_and_others_Apr2009.html#note_5443) What I'm trying to do now is to understand what exactly is the problem. What I discovered so far with all your help: - the impact of a planner - the impact of the analyze target - the impact of prepare / execute - scalability limit on 32 cores I'll also try to adapt prepare/execute solution to see how much it improves performance and/or scalability. As well helping from the other thread I was able to improve a lot the TPS stability on read+write workload! :-) Any other comments are welcome! Rgds, -Dimitri On 5/12/09, Dimitri Fontaine wrote: > Hi, > > Dimitri writes: > >>>> So, why I don't use prepare here: let's say I'm testing the worst >>>> stress case :-) Imagine you have thousands of such kind of queries - >>>> you cannot prepare all of them! :-) >>> >>> Thousands? Surely there'll be a dozen or three of most common queries, >>> to which you pass different parameters. You can prepare thoseu >> >> Ok, and if each client just connect to the database, execute each kind >> of query just *once* and then disconnect?.. - cost of prepare will >> kill performance here if it's not reused at least 10 times within the >> same session. > > In a scenario which looks like this one, what I'm doing is using > pgbouncer transaction pooling. Now a new connection from client can be > served by an existing backend, which already has prepared your > statement. > > So you first SELECT name FROM pg_prepared_statements; to know if you > have to PREPARE or just EXECUTE, and you not only maintain much less > running backends, lower fork() calls, but also benefit fully from > preparing the statements even when you EXECUTE once per client > connection. > >> Well, I know, we always can do better, and even use stored procedures, >> etc. etc. > > Plain SQL stored procedure will prevent PostgreSQL to prepare your > queries, only PLpgSQL functions will force transparent plan caching. But > calling this PL will cost about 1ms per call in my tests, so it's not a > good solution. > > It's possible to go as far as providing your own PostgreSQL C module > where you PREPARE at _PG_init() time and EXECUTE in a SQL callable > function, coupled with pgbouncer it should max out the perfs. But maybe > you're not willing to go this far. > > Anyway, is hammering the server with always the same query your real > need or just a simplified test-case? If the former, you'll see there are > good ways to theorically obtain better perfs than what you're currently > reaching, if the latter I urge you to consider some better benchmarking > tools, such as playr or tsung. > > https://area51.myyearbook.com/trac.cgi/wiki/Playr > http://tsung.erlang-projects.org/ > http://pgfouine.projects.postgresql.org/tsung.html > http://archives.postgresql.org/pgsql-admin/2008-12/msg00032.php > > Regards, > -- > dim > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] What is the most optimal config parameters to keep stable write TPS ?..
It's just one of the test conditions - "what if there 2000 users?" - I know I may use pgpool or others, but I also need to know the limits of the database engine itself.. For the moment I'm limiting to 256 concurrent sessions, but config params are kept like for 2000 :-) Rgds, -Dimitri On 5/12/09, Laurent Laborde wrote: > On Mon, May 11, 2009 at 6:31 PM, Dimitri wrote: >> Hi Kevin, >> >> PostgreSQL: 8.3.7 & 8.4 >> Server: Sun M5000 32cores >> OS: Solaris 10 >> >> current postgresql.conf: >> >> # >> max_connections = 2000 # (change requires restart) > > Are you sure about the 2000 connections ? > Why don't you use a pgbouncer or pgpool instead ? > > > -- > F4FQM > Kerunix Flan > Laurent Laborde > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi, Dimitri writes: >>> So, why I don't use prepare here: let's say I'm testing the worst >>> stress case :-) Imagine you have thousands of such kind of queries - >>> you cannot prepare all of them! :-) >> >> Thousands? Surely there'll be a dozen or three of most common queries, >> to which you pass different parameters. You can prepare thoseu > > Ok, and if each client just connect to the database, execute each kind > of query just *once* and then disconnect?.. - cost of prepare will > kill performance here if it's not reused at least 10 times within the > same session. In a scenario which looks like this one, what I'm doing is using pgbouncer transaction pooling. Now a new connection from client can be served by an existing backend, which already has prepared your statement. So you first SELECT name FROM pg_prepared_statements; to know if you have to PREPARE or just EXECUTE, and you not only maintain much less running backends, lower fork() calls, but also benefit fully from preparing the statements even when you EXECUTE once per client connection. > Well, I know, we always can do better, and even use stored procedures, > etc. etc. Plain SQL stored procedure will prevent PostgreSQL to prepare your queries, only PLpgSQL functions will force transparent plan caching. But calling this PL will cost about 1ms per call in my tests, so it's not a good solution. It's possible to go as far as providing your own PostgreSQL C module where you PREPARE at _PG_init() time and EXECUTE in a SQL callable function, coupled with pgbouncer it should max out the perfs. But maybe you're not willing to go this far. Anyway, is hammering the server with always the same query your real need or just a simplified test-case? If the former, you'll see there are good ways to theorically obtain better perfs than what you're currently reaching, if the latter I urge you to consider some better benchmarking tools, such as playr or tsung. https://area51.myyearbook.com/trac.cgi/wiki/Playr http://tsung.erlang-projects.org/ http://pgfouine.projects.postgresql.org/tsung.html http://archives.postgresql.org/pgsql-admin/2008-12/msg00032.php Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Nice to know. But again, if this cache is kept only on the client side it'll be always lost on disconnect. And if clients are "short-lived" it'll not help. BTW, is there an option to say "do execution plan as simple as possible"? If you're sure about your data and your indexes - don't need to spend so much time. Rgds, -Dimitri On 5/12/09, Heikki Linnakangas wrote: > Dimitri wrote: >> Now, as you see from your explanation, the Part #2 is the most >> dominant - so why instead to blame this query not to implement a QUERY >> PLANNER CACHE??? - in way if any *similar* query is recognized by >> parser we simply *reuse* the same plan?.. > > At least in JDBC, there's several open source prepared statement cache > implementations out there that people use. I don't know about other > client libraries, but it certainly is possible to do in the client. > > -- >Heikki Linnakangas >EnterpriseDB http://www.enterprisedb.com > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
>> So, why I don't use prepare here: let's say I'm testing the worst >> stress case :-) Imagine you have thousands of such kind of queries - >> you cannot prepare all of them! :-) > > Thousands? Surely there'll be a dozen or three of most common queries, > to which you pass different parameters. You can prepare thoseu Ok, and if each client just connect to the database, execute each kind of query just *once* and then disconnect?.. - cost of prepare will kill performance here if it's not reused at least 10 times within the same session. Well, I know, we always can do better, and even use stored procedures, etc. etc. > >> Now, as you see from your explanation, the Part #2 is the most >> dominant - so why instead to blame this query not to implement a QUERY >> PLANNER CACHE??? - in way if any *similar* query is recognized by >> parser we simply *reuse* the same plan?.. > > This has been discussed in the past, but it turns out that a real > implementation is a lot harder than it seems. Ok. If I remember well, Oracle have it and it helps a lot, but for sure it's not easy to implement.. Rgds, -Dimitri -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Aidan, thanks a lot for this detailed summary! So, why I don't use prepare here: let's say I'm testing the worst stress case :-) Imagine you have thousands of such kind of queries - you cannot prepare all of them! :-) or you'll maybe prepare it once, but as I showed previously in this thread prepare statement itself takes 16ms, so for a single shot there is no gain! :-) Stressing with such kind of short and simple queries (and again, they have joins, it may be even more simple :-)) will give me a result to show with guarantee my worst case - I know then if I have to deploy a bombarding OLTP-like application my database engine will be able to keep such workload, and if I have performance problems they are inside of application! :-) (well, it's very simplistic, but it's not far from the truth :-)) Now, as you see from your explanation, the Part #2 is the most dominant - so why instead to blame this query not to implement a QUERY PLANNER CACHE??? - in way if any *similar* query is recognized by parser we simply *reuse* the same plan?.. Rgds, -Dimitri On 5/11/09, Aidan Van Dyk wrote: > * Dimitri [090511 11:18]: >> Folks, it's completely crazy, but here is what I found: >> >> - if HISTORY table is analyzed with target 1000 my SELECT response >> time is jumping to 3ms, and the max throughput is limited to 6.000 TPS >> (it's what happenned to 8.3.7) >> >> -if HISTORY table is analyzed with target 5 - my SELECT response time >> is decreasing to 1.2ms (!) and then my max TPS level is ~12.000 ! >> and CPU is used up to 95% even by 8.3.7 :-) and 8.4 performed better >> just because I left its analyze target to default 100 value. >> >> Anyone may explain me why analyze target may have so huge negative >> secondary effect?.. > > It's actually pretty straight forward. > > The PostgreSQL query planner is a "smart planner". It takes into > consideration all the statistics available on the columns/tables, > expected outputs based on inputs, etc, to choose what it thinks will be > the best plan. The more data you have in statistics (the larger > statistics target you have), the more CPU time and longer it's going to > take to "plan" your queries. The tradeoff is hopefully better plans. > > But, in your scenario, where you are hitting the database with the > absolute worst possible way to use PostgreSQL, with small, repeated, > simple queries, you're not getting the advantage of "better" plans. In > your case, you're throwing absolutely simple queries at PG as fast as > you can, and for each query, PostgreSQL has to: > > 1) Parse the given "query string" > 2) Given the statistics available, plan the query and pick the best one > 3) Actually run the query. > > Part 2 is going to dominate the CPU time in your tests, more so the more > statistics it has to evaluate, and unless the data has to come from the > disks (i.e. not in shared buffers or cache) is thus going to dominate the > time before you get your results. More statistics means more time > needed to do the planning/picking of the query. > > If you were to use prepared statements, the cost of #1 and #2 is done > once, and then every time you throw a new execution of the query to > PostgreSQL, you get to just do #3, the easy quick part, especially for > small simple queries where all the data is in shared buffers or the cache. > > a. > > -- > Aidan Van Dyk Create like a god, > ai...@highrise.ca command like a king, > http://www.highrise.ca/ work like a slave. > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Simon, it's too early yet to speak about MySQL scalability... :-) it's only since few months there is *no more* regression on MySQL performance while moving from 8 to 16 cores. But looking how quickly it's progressing now things may change very quickly :-) For the moment on my tests it gives: - on 8 cores: 14.000 TPS - on 16 cores: 17.500 TPS - on 32 cores: 15.000 TPS (regression) Rgds, -Dimitri On 5/11/09, Simon Riggs wrote: > > On Mon, 2009-05-11 at 17:18 +0200, Dimitri wrote: > >> Yes, forget, MySQL is reaching 17.500 TPS here. > > Please share your measurements of MySQL scalability also. > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] What is the most optimal config parameters to keep stable write TPS ?..
Hi Scott, good point - the current checkpoint completion target is a default 0.5, and it makes sense to set it to 0.8 to make writing more smooth, great! yes, data and xlog are separated, each one is sitting on an independent storage LUN RAID1, and storage box is enough performant Thank you! Rgds, -Dimitri On 5/11/09, Scott Marlowe wrote: > On Mon, May 11, 2009 at 10:31 AM, Dimitri wrote: >> Hi Kevin, >> >> PostgreSQL: 8.3.7 & 8.4 >> Server: Sun M5000 32cores >> OS: Solaris 10 >> >> current postgresql.conf: >> >> # >> max_connections = 2000 # (change requires restart) >> effective_cache_size = 48000MB >> shared_buffers = 12000MB >> temp_buffers = 200MB >> work_mem = 100MB# min 64kB >> maintenance_work_mem = 600MB# min 1MB >> >> max_fsm_pages = 2048000 >> fsync = on # turns forced synchronization on >> or off >> synchronous_commit = off# immediate fsync at commit >> wal_sync_method = fdatasync >> wal_buffers = 2MB >> wal_writer_delay = 400ms# 1-1 milliseconds >> >> checkpoint_segments = 128 >> checkpoint_timeout = 30s > > What's your checkpoint completion target set to? Crank that up a bit > ot 0.7, 0.8 etc and make the timeout more, not less. That should > help. > > Also, look into better hardware (RAID controller with battery backed > cache) and also putting pg_xlog on a separate RAID-1 set (or RAID-10 > set if you've got a lot of drives under the postgres data set). > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] What is the most optimal config parameters to keep stable write TPS ?..
OK, it'll be better to avoid a such improvement :-) Performance - yes, but not for any price :-) Thank you! Rgds, -Dimitri On 5/11/09, Kevin Grittner wrote: > Dimitri wrote: > >> What about "full_page_writes" ? seems it's "on" by default. Does it >> makes sense to put if off?.. > > It would probably help with performance, but the description is a > little disconcerting in terms of crash recovery. We tried running > with it off for a while (a year or so back), but had problems with > corruption. I think the specific cause of that has since been fixed, > it's left us a bit leery of the option. > > Maybe someone else can speak to how safe (or not) the current > implementation of that option is. > > -Kevin > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] What is the most optimal config parameters to keep stable write TPS ?..
Thanks a lot, I'll try them all! Yes, I have a good external storage box with battery backed cache enabled. There are 64GB of RAM so I expected it'll help little bit to increase a buffer cache, but ok, will see if with 256MB it'll be better :-) What about "full_page_writes" ? seems it's "on" by default. Does it makes sense to put if off?.. Rgds, -Dimitri On 5/11/09, Kevin Grittner wrote: > Dimitri wrote: > >> PostgreSQL: 8.3.7 & 8.4 >> Server: Sun M5000 32cores >> OS: Solaris 10 > > Does that have a battery backed RAID controller? If so, is it > configured for write-back? These both help a lot with smoothing > checkpoint I/O gluts. > > We've minimized problems by making the background writer more > aggressive. 8.3 and later does a better job in general, but we've > still had to go with: > > bgwriter_lru_maxpages = 1000 > bgwriter_lru_multiplier = 4.0 > >> shared_buffers = 12000MB > > You might want to test with that set to something much lower, to see > what the checkpoint delays look like. We've found it best to use a > small (256MB) setting, and leave caching to the OS; in our > environment, it seems to do a better job of scheduling the disk I/O. > YMMV, of course. > > -Kevin > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] What is the most optimal config parameters to keep stable write TPS ?..
Hi Kevin, PostgreSQL: 8.3.7 & 8.4 Server: Sun M5000 32cores OS: Solaris 10 current postgresql.conf: # max_connections = 2000 # (change requires restart) effective_cache_size = 48000MB shared_buffers = 12000MB temp_buffers = 200MB work_mem = 100MB# min 64kB maintenance_work_mem = 600MB# min 1MB max_fsm_pages = 2048000 fsync = on # turns forced synchronization on or off synchronous_commit = off# immediate fsync at commit wal_sync_method = fdatasync wal_buffers = 2MB wal_writer_delay = 400ms# 1-1 milliseconds checkpoint_segments = 128 checkpoint_timeout = 30s archive_mode = off track_counts = on autovacuum = on log_autovacuum_min_duration = 0 autovacuum_max_workers = 4 autovacuum_naptime = 20 # time between autovacuum runs autovacuum_vacuum_threshold = 50 autovacuum_analyze_threshold = 50 autovacuum_vacuum_scale_factor = 0.001 lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' # Rgds, -Dimitri On 5/11/09, Kevin Grittner wrote: > Dimitri wrote: > >> what may you suggest as the most optimal postgresql.conf to keep >> writing as stable as possible?.. >> >> What I want is to avoid "throughput waves" - I want to keep my >> response times stable without any activity holes. I've tried to >> reduce checkpoint timeout from 5min to 30sec - it helped, throughput >> is more stable now, but instead of big waves I have now short waves >> anyway.. >> >> What is the best options combination here?.. > > What version of PostgreSQL? What operating system? What hardware? > > The answers are going to depend on the answers to those questions. > > It would also be good to show all lines from postgresql.conf which are > not commented out. > > -Kevin > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Tom, it was not willing :-) it just stayed so after various probes with a query plan. Anyway, on 8.4 the default target is 100, an just by move it to 5 I reached on 16cores 10.500 TPS instead of 8.000 initially. And I think you have a good reason to keep it equal to 100 by default, isn't it? ;-) And what about scalability on 32cores?.. Any idea? Rgds, -Dimitri On 5/11/09, Tom Lane wrote: > Dimitri writes: >> Anyone may explain me why analyze target may have so huge negative >> secondary effect?.. > > If these are simple queries, maybe what you're looking at is the > increase in planning time caused by having to process 10x as much > statistical data. Cranking statistics_target to the max just because > you can is not necessarily a good strategy. > > regards, tom lane > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] What is the most optimal config parameters to keep stable write TPS ?..
Hi, what may you suggest as the most optimal postgresql.conf to keep writing as stable as possible?.. What I want is to avoid "throughput waves" - I want to keep my response times stable without any activity holes. I've tried to reduce checkpoint timeout from 5min to 30sec - it helped, throughput is more stable now, but instead of big waves I have now short waves anyway.. What is the best options combination here?.. Rgds, -Dimitri -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Folks, it's completely crazy, but here is what I found: - if HISTORY table is analyzed with target 1000 my SELECT response time is jumping to 3ms, and the max throughput is limited to 6.000 TPS (it's what happenned to 8.3.7) -if HISTORY table is analyzed with target 5 - my SELECT response time is decreasing to 1.2ms (!) and then my max TPS level is ~12.000 ! and CPU is used up to 95% even by 8.3.7 :-) and 8.4 performed better just because I left its analyze target to default 100 value. Anyone may explain me why analyze target may have so huge negative secondary effect?.. Next point: SCALABILITY ISSUE Now both 8.3.7 and 8.4 have similar performance levels, but 8.3.7 is always slightly better comparing to 8.4, but well. The problem I have: - on 8 cores: ~5.000 TPS / 5.500 MAX - on 16 cores: ~10.000 TPS / 11.000 MAX - on 32 cores: ~10.500 TPS / 11.500 MAX What else may limit concurrent SELECTs here?.. Yes, forget, MySQL is reaching 17.500 TPS here. Rgds, -Dimitri On 5/7/09, Simon Riggs wrote: > > On Thu, 2009-05-07 at 20:36 +0200, Dimitri wrote: > >> I've simply restarted a full test with hashjoin OFF. Until 32 >> concurrent users things are going well. Then since 32 users response >> time is jumping to 20ms, with 64 users it's higher again, and with 256 >> users reaching 700ms, so TPS is dropping from 5.000 to ~200.. >> >> With hashjoin ON it's not happening, and I'm reaching at least 11.000 >> TPS on fully busy 32 cores. > > Much better to stick to the defaults. > > Sounds like a problem worth investigating further, but not pro bono. > >> About scalability issue - there is one on 8.3.7, because on 32 cores >> with such kind of load it's using only 50% CPU and not outpassing >> 6.000 TPS, while 8.4 uses 90% CPU and reaching 11.000 TPS.. > > Yeh, small changes make a big difference. Thanks for the info. > > How does MySQL perform? > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
I've simply restarted a full test with hashjoin OFF. Until 32 concurrent users things are going well. Then since 32 users response time is jumping to 20ms, with 64 users it's higher again, and with 256 users reaching 700ms, so TPS is dropping from 5.000 to ~200.. With hashjoin ON it's not happening, and I'm reaching at least 11.000 TPS on fully busy 32 cores. I should not use prepare/execute as the test conditions should remain "generic". About scalability issue - there is one on 8.3.7, because on 32 cores with such kind of load it's using only 50% CPU and not outpassing 6.000 TPS, while 8.4 uses 90% CPU and reaching 11.000 TPS.. On the same time while I'm comparing 8.3 and 8.4 - the response time is 2 times lower in 8.4, and seems to me the main gain for 8.4 is here. I'll publish all details, just need a time :-) Rgds, -Dimitri On 5/7/09, Merlin Moncure wrote: > On Thu, May 7, 2009 at 4:20 AM, Dimitri wrote: >> Hi Simon, >> >> may you explain why REINDEX may help here?.. - database was just >> created, data loaded, and then indexes were created + analyzed.. What >> may change here after REINDEX?.. >> >> With hashjoin disabled was a good try! >> Running this query "as it" from 1.50ms we move to 0.84ms now, >> and the plan is here: >> >> QUERY >> PLAN >> -- >> Sort (cost=4562.83..4568.66 rows=2329 width=176) (actual >> time=0.225..0.229 rows=20 loops=1) >> Sort Key: h.horder >> Sort Method: quicksort Memory: 30kB >> -> Merge Join (cost=4345.89..4432.58 rows=2329 width=176) (actual >> time=0.056..0.205 rows=20 loops=1) >> Merge Cond: (s.ref = h.ref_stat) >> -> Index Scan using stat_ref_idx on stat s >> (cost=0.00..49.25 rows=1000 width=45) (actual time=0.012..0.079 >> rows=193 loops=1) >> -> Sort (cost=4345.89..4351.72 rows=2329 width=135) (actual >> time=0.041..0.043 rows=20 loops=1) >> Sort Key: h.ref_stat >> Sort Method: quicksort Memory: 30kB >> -> Index Scan using history_ref_idx on history h >> (cost=0.00..4215.64 rows=2329 width=135) (actual time=0.013..0.024 >> rows=20 loops=1) >> Index Cond: (ref_object = '01'::bpchar) >> Total runtime: 0.261 ms >> (12 rows) >> >> Curiously planner expect to run it in 0.26ms >> >> Any idea why planner is not choosing this plan from the beginning?.. >> Any way to keep this plan without having a global or per sessions >> hashjoin disabled?.. > > can you work prepared statements into your app? turn off hash join, > prepare the query, then turn it back on. > > merlin > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
The problem with "gprof" - it'll profile all stuff from the beginning to the end, and a lot of function executions unrelated to this query will be profiled... As well when you look on profiling technology - all such kind of solutions are based on the system clock frequency and have their limits on time resolution. On my system this limit is 0.5ms, and it's too big comparing to the query execution time :-) So, what I've done - I changed little bit a reference key criteria from = '01' to < '51', so instead of 20 rows I have 1000 rows on output now, it's still slower than InnoDB (12ms vs 9ms), but at least may be profiled (well, we also probably moving far from the problem as time may be spent mostly on the output traffic now, but I've tried anyway) - I've made a loop of 100 iterations of this query which is reading but not printing data. The total execution time of this loop is 1200ms, and curiously under profiling was not really changed. Profiler was able to catch 733ms of total execution time (if I understand well, all functions running faster than 0.5ms are remain un-profiled). The top profiler output is here: Excl. Incl. Name User CPU User CPU sec. sec. 0.733 0.733 0.103 0.103 memcpy 0.045 0.045 slot_deform_tuple 0.037 0.040 AllocSetAlloc 0.021 0.021 AllocSetFree 0.018 0.037 pfree 0.018 0.059 appendBinaryStringInfo 0.017 0.031 heap_fill_tuple 0.017 0.017 _ndoprnt 0.016 0.016 nocachegetattr 0.015 0.065 heap_form_minimal_tuple 0.015 0.382 ExecProcNode 0.015 0.015 strlen 0.014 0.037 ExecScanHashBucket 0.014 0.299 printtup 0.013 0.272 ExecHashJoin 0.011 0.011 enlargeStringInfo 0.011 0.086 index_getnext 0.010 0.010 hash_any 0.009 0.076 FunctionCall1 0.009 0.037 MemoryContextAlloc 0.008 0.008 LWLockAcquire 0.007 0.069 pq_sendcountedtext 0.007 0.035 ExecProject 0.007 0.127 ExecScan ... Curiously "memcpy" is in top. Don't know if it's impacted in many cases, but probably it make sense to see if it may be optimized, etc.. Rgds, -Dimitri On 5/7/09, Euler Taveira de Oliveira wrote: > Dimitri escreveu: >> BTW, is there already an integrated profiled within a code? or do I >> need external tools?.. >> > Postgres provides support for profiling. Add --enable-profiling flag. Use > gprof to get the profile. > > > -- > Euler Taveira de Oliveira > http://www.timbira.com/ > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Simon, may you explain why REINDEX may help here?.. - database was just created, data loaded, and then indexes were created + analyzed.. What may change here after REINDEX?.. With hashjoin disabled was a good try! Running this query "as it" from 1.50ms we move to 0.84ms now, and the plan is here: QUERY PLAN -- Sort (cost=4562.83..4568.66 rows=2329 width=176) (actual time=0.225..0.229 rows=20 loops=1) Sort Key: h.horder Sort Method: quicksort Memory: 30kB -> Merge Join (cost=4345.89..4432.58 rows=2329 width=176) (actual time=0.056..0.205 rows=20 loops=1) Merge Cond: (s.ref = h.ref_stat) -> Index Scan using stat_ref_idx on stat s (cost=0.00..49.25 rows=1000 width=45) (actual time=0.012..0.079 rows=193 loops=1) -> Sort (cost=4345.89..4351.72 rows=2329 width=135) (actual time=0.041..0.043 rows=20 loops=1) Sort Key: h.ref_stat Sort Method: quicksort Memory: 30kB -> Index Scan using history_ref_idx on history h (cost=0.00..4215.64 rows=2329 width=135) (actual time=0.013..0.024 rows=20 loops=1) Index Cond: (ref_object = '01'::bpchar) Total runtime: 0.261 ms (12 rows) Curiously planner expect to run it in 0.26ms Any idea why planner is not choosing this plan from the beginning?.. Any way to keep this plan without having a global or per sessions hashjoin disabled?.. Rgds, -Dimitri On 5/6/09, Simon Riggs wrote: > > On Wed, 2009-05-06 at 10:31 +0200, Dimitri wrote: > >> I've already tried a target 1000 and the only thing it changes >> comparing to the current 100 (default) is instead of 2404 rows it says >> 240 rows, but the plan remaining the same.. > > Try both of these things > * REINDEX on the index being used in the query, then re-EXPLAIN > * enable_hashjoin = off, then re-EXPLAIN > > You should first attempt to get the same plan, then confirm it really is > faster before we worry why the optimizer hadn't picked that plan. > > We already know that MySQL favors nested loop joins, so turning up a > plan that on this occasion is actually better that way is in no way > representative of general performance. Does MySQL support hash joins? > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Hi Ken, yes, I may do it, but I did not expect to come into profiling initially :-) I expected there is just something trivial within a plan that I just don't know.. :-) BTW, is there already an integrated profiled within a code? or do I need external tools?.. Rgds, -Dimitri On 5/6/09, Kenneth Marshall wrote: > On Wed, May 06, 2009 at 09:38:59AM +0200, Dimitri wrote: >> Hi, >> >> any idea if there is a more optimal execution plan possible for this >> query: >> >> select S.REF as stref, S.NAME as stnm, H.HORDER as hord, H.BEGIN_DATE as >> hbeg, >> H.END_DATE as hend, H.NOTE as hnote >> from HISTORY H, STAT S >> where S.REF = H.REF_STAT >> and H.REF_OBJECT = '01' >> order by H.HORDER ; >> >> EXPLAIN ANALYZE output on 8.4: >>QUERY >> PLAN >> >> Sort (cost=4549.75..4555.76 rows=2404 width=176) (actual >> time=1.341..1.343 rows=20 loops=1) >>Sort Key: h.horder >>Sort Method: quicksort Memory: 30kB >>-> Hash Join (cost=33.50..4414.75 rows=2404 width=176) (actual >> time=1.200..1.232 rows=20 loops=1) >> Hash Cond: (h.ref_stat = s.ref) >> -> Index Scan using history_ref_idx on history h >> (cost=0.00..4348.20 rows=2404 width=135) (actual time=0.042..0.052 >> rows=20 loops=1) >>Index Cond: (ref_object = '01'::bpchar) >> -> Hash (cost=21.00..21.00 rows=1000 width=45) (actual >> time=1.147..1.147 rows=1000 loops=1) >>-> Seq Scan on stat s (cost=0.00..21.00 rows=1000 >> width=45) (actual time=0.005..0.325 rows=1000 loops=1) >> Total runtime: 1.442 ms >> (10 rows) >> >> Table HISTORY contains 200M rows, only 20 needed >> Table STAT contains 1000 rows, only 20 needed to be joined to HISTORY >> values. >> >> Table definitions: >> """"""""""""""""""""""""""""""""""""""""""""""""""" >> create table STAT >> ( >> REF CHAR(3)not null, >> NAMECHAR(40) not null, >> NUMBINTnot null >> ); >> >> create table HISTORY >> ( >> REF_OBJECT CHAR(10) not null, >> HORDER INT not null, >> REF_STATCHAR(3) not null, >> BEGIN_DATE CHAR(12) not null, >> END_DATECHAR(12) , >> NOTECHAR(100) >> ); >> >> create unique index stat_ref_idx on STAT( ref ); >> create index history_ref_idx on HISTORY( ref_object, horder ); >> """"""""""""""""""""""""""""""""""""""""""""""""""" >> >> NOTE: The same query runs 2 times faster on MySQL. >> >> Any idea?.. >> >> Rgds, >> -Dimitri >> > Dimitri, > > Is there any chance of profiling the postgres backend to see > where the time is used? > > Just an idea, > Ken > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
I supposed in case with prepare and then execute a query optimizer is no more coming in play on "execute" phase, or did I miss something?.. Forget to say: query cache is disabled on MySQL side. Rgds, -Dimitri On 5/6/09, Craig Ringer wrote: > Dimitri wrote: >> Hi Chris, >> >> the only problem I see here is it's 2 times slower vs InnoDB, so >> before I'll say myself it's ok I want to be sure there is nothing else >> to do.. :-) > > Can the genetic query optimizer come into play on small queries? > > -- > Craig Ringer > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
Folks, first of all: - I used a fixed reference value just to simplify the case analyzing and isolate it as max as possible, of course during my tests all values are random :-) - final goal of the test is to analyze scalability, so yes, concurrent sessions with random keys are growing from 1 to 256 (I run it on 32cores server, no think time, just stressing), and the result is still not yet better comparing to InnoDB - I'm analyzing this query running in memory to understand what's blocking while all main bottlenecks are avoided (no I/O anymore nor network, etc.) - initial explain analyze and table details were posted in the first message Now, let's go more further: - so "as it" query execution took 1.50ms - after removing "order by" it took 1.19ms - select count(*) instead of columns and with removed "order by" took 0.98ms - execute of the same prepared "select count(*) ..." took 0.68ms So, where the time is going?... Rgds, -Dimitri On 5/6/09, Ries van Twisk wrote: > > On May 6, 2009, at 7:53 AM, Richard Huxton wrote: > >> Dimitri wrote: >>> I'll try to answer all mails at once :-)) >>> - query is running fully in RAM, no I/O, no network, only CPU time >>> - looping 100 times the same query gives 132ms total time (~1.32ms >>> per >>> query), while it's 44ms on InnoDB (~0.44ms per query) >> >> Well, assuming you're happy that PG is tuned reasonably for your >> machine and that MySQL's query cache isn't returning the results >> here it looks like MySQL is faster for this particular query. >> >> The only obvious place there could be a big gain is with the hashing >> algorithm. If you remove the ORDER BY and the query-time doesn't >> fall by much then it's the hash phase. >> >> The other thing to try is to alter the query to be a SELECT count(*) >> rather than returning rows - that will let you measure the time to >> transfer the result rows. >> >> -- >> Richard Huxton >> Archonet Ltd >> > > > Do you expect to run this query 100 times per second during your > application? > or is this just a test to see how fast the query is for optimalisation. > > I always get scared myself with such a test as 'runs out of memory', > reason > given is that usually this is not really the case in a production > environment. > > Try to make a little test case where you give the query random > parameters > so different result sets are returned. This will give you a better > idea on how > fast the query really is and might give you better comparison results. > > instead of count(*) I isusallt do explain analyze to see how fast > PostgreSQL handles to query. > > Ries > > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
The story is simple: for the launching of MySQL 5.4 I've done a testing comparing available on that time variations of InnoDB engines, and at the end by curiosity started the same test with PostgreSQL 8.3.7 to see if MySQL performance level is more close to PostgreSQL now (PG was a strong true winner before). For my big surprise MySQL 5.4 outpassed 8.3.7... However, analyzing the PostgreSQL processing I got a feeling something goes wrong on PG side.. So, now I've installed both 8.3.7 and 8.4beta1 to see more in depth what's going on. Currently 8.4 performs much better than 8.3.7, but there is still a room for improvement if such a small query may go faster :-) Rgds, -Dimitri On 5/6/09, Albe Laurenz wrote: > Dimitri wrote: >> I've run several tests before and now going in depth to understand if >> there is nothing wrong. Due such a single query time difference InnoDB >> is doing 2-3 times better TPS level comparing to PostgreSQL.. > > Why don't you use MySQL then? > Or tune PostgreSQL? > > Yours, > Laurenz Albe > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any better plan for this query?..
I'll try to answer all mails at once :-)) - query is running fully in RAM, no I/O, no network, only CPU time - looping 100 times the same query gives 132ms total time (~1.32ms per query), while it's 44ms on InnoDB (~0.44ms per query) - disabling seq scan forcing a planner to use an index scan, and finally it worse as gives 1.53ms per query.. - prepare the query helps: prepare statement takes 16ms, but execute runs in 0.98ms = which make me think it's not only a planner overhead... And it's still 2 times lower vs 0.44ms. Also, generally prepare cannot be used in this test case as we suppose any query may be of any kind (even if it's not always true :-)) - char or varchar should be used here because the reference code is supposed to accept any characters (alphanumeric) - it also reminds me that probably there are some extra CPU time due locale setting - but all my "lc_*" variables are set to "C"... Rgds, -Dimitri On 5/6/09, Merlin Moncure wrote: > On Wed, May 6, 2009 at 7:46 AM, Merlin Moncure wrote: >> prepare history_stat(char(10) as > > typo: > prepare history_stat(char(10)) as > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
