Re: [PERFORM] Any advice tuning this query ?
Hi, On Fri, 2016-11-11 at 16:19 +0100, Henrik Ekenberg wrote: > Sort Method: external merge Disk: 16782928kB This query is generating 16GB temp file on disk. Is this the amount of data you want to sort? Regards, -- Devrim GÜNDÜZ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Error install -pgmemcache
Hi, On Wed, 2013-11-20 at 10:36 -0200, Rogerio Pereira wrote: I'm trying install pgmemcache and occurs error: pgmemcache.h:29:23: error: sasl/sasl.h: No such file or directory Exists solution exists to fix to problem? You will need to install cyrus-sasl-devel package. Still, I just uploaded the RPM package to yum repo: http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/repoview/pgmemcache-93.html Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
Re: [PERFORM] HT on or off for E5-26xx ?
Hi, On Tue, 2012-11-06 at 20:31 -0700, David Boreham wrote: Was assuming it was 6-core but I just noticed it has HT which is currently enabled since I see 12 cores in /proc/cpuinfo Question for the performance experts : is it better to have H enabled or disabled for this generation of Xeon ? Workload will be moderately concurrent, small OLTP type transactions. We'll also run a few low-load VMs (using KVM) and big Java application. HT should be good for file servers, or say many of the app servers, or small web/mail servers. PostgreSQL relies on the CPU power, and since the HT CPUs don't have the same power as the original CPU, when OS submits a job to that particular HTed CPU, query will run significantly slow. To avoid issues, I would suggest you to turn HT off on all PostgreSQL servers. If you can throw some more money, another 6-core CPU would give more benefit. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[PERFORM] Cost of creating an emply WAL segment
Hi, Has anyone measured the cost of creating empty WAL segments while the database is running? Actually, when is the new file created? Just after one segment is filled up, or some time before then? What happens during WAL segment creation? If there are pending transactions to be committed, do we see a delay? I was looking at how Oracle manages this, and I was told that you can create empty segments during installation, so I'm wondering whether it might be a good addition to initdb or not: initdb -S 30 -- create 30 empty segments during initdb Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] The good, old times
On Wed, 2011-01-12 at 08:49 -0500, Mladen Gogala wrote: snip 7 kilobytes per second??? That brings back the times of the good, old 9600 USR modems and floppy disks. The machine is serving 40-50 Mbit/sec, and 90% of its traffic is for pgrpms.org. I'm hosting the server in Turkey, and it is my own dedicated machine -- but eventually it will be moved to a machine under postgresql.org infrastructure soon, so it will be faster, I believe. Sorry for the current setup -- it is the only machine that I can host RPMs safely. We are also *considering* to use FTP mirrors as RPM mirrors, too, but I won't promise that now. Please keep looking at http://yum.pgrpms.org for updates. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences
On Fri, 2010-11-05 at 11:59 +0100, A B wrote: If you just wanted PostgreSQL to go as fast as possible WITHOUT any care for your data (you accept 100% dataloss and datacorruption if any error should occur), what settings should you use then? You can initdb to ramdisk, if you have enough RAM. It will fast, really. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Slow count(*) again...
On Wed, 2010-10-13 at 09:02 -0400, Greg Smith wrote: XFS support is available as an optional module starting in RHEL 5.5. In CentOS, you just grab it, so that's what I've been doing. My understanding is that you may have to ask your sales rep to enable access to it under the official RedHat Network channels if you're using a subscription from them. I'm not sure exactly what the support situation is with it, but it's definitely available as an RPM from RedHat. Right. It is called Red Hat Scalable File System, and once paid, it is available via RHN. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
On Fri, 2010-06-25 at 15:25 +0530, Rajesh Kumar Mallah wrote: shared_buffers = 10GB # min 128kB work_mem = 512MB# min 64kB These are still pretty high IMHO. How many *concurrent* connections do you have? -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
On Wed, 2010-06-09 at 21:51 -0400, Robert Haas wrote: On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com wrote: Any input? I can reproduce these numbers consistently. If you need more information then just let me know. By the way, I am a new postgresql user so my experience is limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... Given that there are 2 recent reports on the same issue, I wonder if the new packages were built with debugging options or not. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] File system choice for Red Hat systems
On Wed, 2010-06-02 at 15:06 +1200, Mark Kirkwood wrote: What are Red Hat using people choosing for a good performing filesystem? ext2 (xlogs) and ext3 (data). For xfs, you may want to read this: http://blog.2ndquadrant.com/en/2010/04/the-return-of-xfs-on-linux.html Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Testing FusionIO
On Mon, 2010-03-08 at 09:38 -0800, Ben Chobot wrote: We've enjoyed our FusionIO drives very much. They can do 100k iops without breaking a sweat. Yeah, performance is excellent. I bet we could get more, but CPU was bottleneck in our test, since it was just a demo server :( -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[PERFORM] Testing FusionIO
Hi, I have a FusionIO drive to test for a few days. I already ran iozone and bonnie++ against it. Does anyone have more suggestions for it? It is a single drive (unfortunately). Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] bgwriter, checkpoints, curious (seeing delays)
On Thu, 2010-02-25 at 22:12 -0800, Tory M Blue wrote: shared_buffers = 1500MB Some people tend to increase this to 2.2GB(32-bit) or 4-6 GB (64 bit), if needed. Please note that more shared_buffers will lead to more pressure on bgwriter, but it also has lots of benefits, too. work_mem = 100MB This is too much. Since you have 300 connections, you will probably swap because of this setting, since each connection may use this much work_mem. The rule of the thumb is to set this to a lower general value (say, 1-2 MB), and set it per-query when needed. checkpoint_segments = 100 checkpoint_warning = 3600s What about checkpoint_timeout? Please note that even if checkpoint_segments = 100, if timeout value is low (say 5 mins), PostgreSQL will probably checkpoint every checkpoint_timeout minutes (unless PostgreSQL creates $checkpoint_segments xlogs before checkpoint_timeout value). Depending on your workload, this may not be intended, and it may cause spikes -- which will lead to the issues you complain. I'll stop here, and suggest you read this: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm for details about this subject. As noted there, if you are running 8.3+, pg_stat_bgwriter will help you to tune checkpoint bgwriter settings. -HTH. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] bgwriter, checkpoints, curious (seeing delays)
On Thu, 2010-02-25 at 23:01 -0800, Tory M Blue wrote: Checkpoint_timeout is the default and that looks like 5 mins (300 seconds). And is obviously why I have such a discrepancy between time reached and requested. If you have a high load, you may want to start tuning with 15 minutes, and bump it to 30 mins if needed. Also you may want to decrease segments value based on your findings, since increasing only one of them won't help you a lot. As I wrote before, pg_stat_bgwriter is your friend here. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] New server to improve performance on our large and busy DB - advice?
On Thu, 2010-01-21 at 13:44 -0300, Alvaro Herrera wrote: I think Devrim publishes debuginfo packages which you need to install separately. Right. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Best suiting OS
On Sun, 2009-10-04 at 15:51 -0400, Mark Mielke wrote: How do you provide effective support for a kernel that has 3000 back ported patches against it? This is again nonsense. Red Hat employs top kernel hackers. They do maintain vanilla kernel. It is not hard for Red Hat to maintain their own version ;) -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Best suiting OS
On Sun, 2009-10-04 at 15:51 -0400, Mark Mielke wrote: If somebody were to come to you with a *new* deployment request, what would you recommend? Would you really recommend RHEL 5 *today*? Well, I would, and I do recommend people. RHEL5 is well-tested, and stable. Many hardware vendors support RHEL 5. The list goes on. If I would want to live with bleeding edge, I'd use Fedora in my servers. Otherwise, linux 2.6.31 is not *that much* better than Red Hat's 2.6.18. Actually the point is: Red Hat's 2.6.18 is not actually 2.6.18. I also want to state that Red Hat is adding new features to each point release, as you know. It is not that old. We have a customer that run ~ 1 hundred million transaction/hour , and they run RHEL. We also have another one that runs about that one, and guess which OS they are running? If I weren't using RHEL, I'd use Ubuntu. Nothing else. ...and disclaimer: I don't work for Red Hat. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Best suiting OS
On Sun, 2009-10-04 at 10:05 -0400, Mark Mielke wrote: RHEL and CentOS are particular bad *right now*. See here: http://en.wikipedia.org/wiki/RHEL http://en.wikipedia.org/wiki/CentOS For RHEL, look down to Release History and RHEL 5.3 based on Linux-2.6.18, released March, 2007. On the CentOS page you'll see it is dated April, 2007. CentOS is identical to RHEL on purpose, but always 1 to 6 months after the RHEL, since they take the RHEL source, re-build it, and then re-test it. Linux is up to Linux-2.6.31.1 right now: http://www.kernel.org/ So any comparisons between operating system *distributions* should be fair. Comparing a 2007 release to a 2009 release, for example, is not fair. RHEL / CentOS are basically out of the running right now, because they are so old. Some people call these stability . -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Hot Issue
On Wed, 2008-07-02 at 18:01 +0530, Gauri Kanekar wrote: Checked out the performance of Hot on 30th June : relname n_tup_ins n_tup_upd n_tup_del n_tup_hot_upd n_live_tup n_dead_tup *table1* *15509156* *2884653* *0* *2442823* * 15509158* *68046* table2 434585 718472 0 642336 434703 16723 table3 105546 252143 0 233078 105402 4922 snip Checked the performance of Hot on 2nd July : relname n_tup_ins n_tup_upd n_tup_del n_tup_hot_upd n_live_tup n_dead_tup *table1**15577724* *6237242* *0* *2924948* * 18460676* *131103* table2 435558 1579613 0 763397 1171497 42476 Maybe those updates were not qualified for HOT between these days? Regards, -- Devrim GÜNDÜZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Hot Issue
On Wed, 2008-07-02 at 18:41 +0530, Gauri Kanekar wrote: hot have a limitation that it do not work if, the index column is updated. It is one of the conditions -- it also needs to fit in the same block. Regards, -- Devrim GÜNDÜZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [PERFORM] [QUESTION]Concurrent Access
On Wed, 2008-07-02 at 15:31 -0300, Leví Teodoro da Silva wrote: we want to use PostGree like the DataBase system, but i got some questions. First of all: Please learn the correct spelling: It is PostgreSQL, or Postgres. I want to know if the PostGree has limitations about the concurrent access, because a lot of people will access this database at the same time. PostgreSQL does not force a limit on concurrent access, but it is dependent on your hardware, network, etc. I want to know about the limitations, like how much memory do i have to use !? It depends on the size of your database. See the link below. How big could be my database Depends on your disk ;) There is no PostgreSQL limitation for that. Well, there is a limit for tables, etc: http://www.postgresql.org/docs/faqs.FAQ.html#item4.4 -HTH. -- Devrim GÜNDÜZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Replication
Hello, On Thu, 2007-06-14 at 16:14 -0700, Craig James wrote: Cluster Seems pretty good, but web site is not current, http://www.pgcluster.org is a bit up2date, also http://pgfoundry.org/projects/pgcluster is up2date (at least downloads page :) ) Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3
Hi, On Wed, 2007-04-11 at 13:25 -0400, Michael Dengler wrote: Thanks for the info. One more thingI am in rpm hell. When I try to # rpm -Uvh postgresql-libs-8.2.3-1PGDG.i686.rpm I get: error: Failed dependencies: libpq.so.3 is needed by (installed) perl-DBD-Pg-1.31-6.i386 libpq.so.3 is needed by (installed) postgresql-python-7.4.13-2.RHEL4.1.i386 libpq.so.3 is needed by (installed) php-pgsql-4.3.9-3.15.i386 and when I try: # rpm -ivh compat-postgresql-libs-3-3PGDG.i686.rpm I get: error: Failed dependencies: postgresql-libs 8.0.2 conflicts with compat-postgresql-libs-3-3PGDG.i686 It seems that you already have PostgreSQL installed on your server. Tı install 8.2.3: * Take a dump using pg_dump(all). * Remove existing RPMS, ignore warnings about libpq.so* * Install compat-3 package * Install 8.2.3 packages. * Reload your dump. Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3
Hi, On Tue, 2007-04-10 at 22:55 +0200, Guillaume Smet wrote: See http://developer.postgresql.org/~devrim/rpms/compat/ and choose the correct package for your architecture. ... or better, each RHEL4 directory in our FTP site has compat package (that directory is not up2date now). Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
