Hi pgsql-performance,
I was doing mass insertions on my desktop machine and getting at most
1 MB/s disk writes (apart from occasional bursts of 16MB). Inserting 1
million rows with a single integer (data+index 56 MB total) took over
2 MINUTES! The only tuning I had done was shared_buffers=256MB. S
On Sun, Oct 31, 2010 at 21:59, Greg Smith wrote:
> open_datasync support was just added to Linux itself very recently.
Oh I didn't realize it was a new feature. Indeed O_DSYNC support was
added in 2.6.33
It seems like bad behavior on PostgreSQL's part to default to new,
untested features.
I hav
On Mon, Nov 1, 2010 at 14:49, Divakar Singh wrote:
> I am trying to tune my libpq program for insert performance.
> When I tried inserting 1M rows into a table with a Primary Key, it took
> almost 62 seconds.
> After adding a composite index of 2 columns, the performance degrades to 125
> seconds.
On Mon, Nov 1, 2010 at 14:56, Divakar Singh wrote:
> Thanks for your tips. i will try those.
> I am on Solaris Sparc 5.10
Sorry, I assumed you were running Linux. But still it could be the
same problem as I had.
Be careful changing your wal_sync_method, as it has the potential to
corrupt your da
Just some ideas that went through my mind when reading your post.
On Wed, Nov 3, 2010 at 17:52, Nick Matheson wrote:
> than observed raw disk reads (5 MB/s versus 100 MB/s). Part of this is
> due to the storage overhead we have observed in Postgres. In the
> example below, it takes 1 GB to stor
On Fri, Nov 5, 2010 at 13:32, A B wrote:
> I was just thinking about the case where I will have almost 100%
> selects, but still needs something better than a plain key-value
> storage so I can do some sql queries.
> The server will just boot, load data, run, hopefully not crash but if
> it would
On Fri, Nov 5, 2010 at 13:11, Guillaume Cottenceau wrote:
> Don't use PostgreSQL, just drop your data, you will end up with
> the same results and be even faster than any use of PostgreSQL.
> If anyone needs data, then just say you had data corruption, and
> that since 100% dataloss is accepted, t
On Fri, Nov 5, 2010 at 23:10, Greg Smith wrote:
>> Not having a real O_DSYNC on linux until recently makes it even more
>> dubious to have it as a default...
>>
>
> If Linux is now defining O_DSYNC
Well, Linux always defined both O_SYNC and O_DSYNC, but they used to
have the same value. The defau
On Sat, Nov 6, 2010 at 00:06, Greg Smith wrote:
> Please refrain from making changes to popular documents like the
> tuning guide on the wiki based on speculation about what's happening.
I will grant you that the details were wrong, but I stand by the conclusion.
I can state for a fact that Pos
On Mon, Nov 8, 2010 at 01:35, Greg Smith wrote:
> Yes; it's supposed to, and that logic works fine on some other platforms.
No, the logic was broken to begin with. Linux technically supported
O_DSYNC all along. PostgreSQL used fdatasync as the default. Now,
because Linux added proper O_SYNC suppo
On Mon, Nov 8, 2010 at 02:05, Greg Smith wrote:
> Where's your benchmarks proving it then? If you're right about this, and
> I'm not saying you aren't, it should be obvious in simple bechmarks by
> stepping through various sizes for wal_buffers and seeing the
> throughput/latency situation improv
On Mon, Nov 8, 2010 at 20:40, Tom Lane wrote:
>> The latter choice is the one that requires testing to prove that it is the
>> proper and preferred default from the performance and data reliability POV.
>
> And, in fact, the game plan is to do that testing and see which default
> we want. I thin
On Sat, Nov 13, 2010 at 07:53, Craig Ringer wrote:
> Oracle's MVCC approach has its own costs. Like Pg's, those costs increase
> with update/delete frequency. Instead of table bloat, Oracle suffers from
> redo log growth (or redo log size management issues). Instead of increased
> table scan costs
On Thu, Nov 11, 2010 at 20:25, Kyriacos Kyriacou
wrote:
> By definition of MVCC, when an UPDATE is performed, PostgreSQL creates a
> new copy of the row in a new location.
> result is to have huge fragmentation on table space, unnecessary updates
> in all affected indexes, unnecessary costly I/O
On Sat, Nov 13, 2010 at 20:01, Tom Lane wrote:
> What's your basis for asserting he's uninterested? Please have a little
> patience.
My apologies, I was under the impression that he hadn't answered your
request, but he did in the -hackers thread.
Regards,
Marti
--
Sent via pgsql-performance m
On Wed, Nov 17, 2010 at 01:31, Tom Lane wrote:
> Well, we're not going to increase the default to gigabytes, but we could
> very probably increase it by a factor of 10 or so without anyone
> squawking. It's been awhile since I heard of anyone trying to run PG in
> 4MB shmmax. How much would a ch
On Sat, Dec 4, 2010 at 13:56, Mathieu De Zutter wrote:
> I have no idea why in some cases the index scan is not considered.
> Does anyone have an idea?
I guess that it's because the currval() function is volatile -- its
value has to be tested for again each row.
Try this instead:
SELECT user_id
On Thu, Dec 9, 2010 at 01:26, Andy wrote:
> If you are IO-bound, you might want to consider using SSD.
>
> A single SSD could easily give you more IOPS than 16 15k SAS in RAID 10.
Are there any that don't risk your data on power loss, AND are cheaper
than SAS RAID 10?
Regards,
Marti
--
Sent vi
On Thu, Dec 9, 2010 at 04:28, Scott Marlowe wrote:
> On Wed, Dec 8, 2010 at 5:03 PM, Benjamin Krajmalnik
> wrote:
>> My biggest concern with SSD drives is their life expectancy,
>
> Generally that's not a big issue, especially as the SSDs get larger.
> Being able to survive a power loss without
On Wed, Dec 15, 2010 at 08:56, AI Rumman wrote:
> My question is why "crmentity_setype_idx" index is being used only.
> "crmentity_deleted_idx" index is not using.
> Any idea please.
Because the planner determined that the cost of scanning *two* indexes
and combining the results is more expensive
2010/12/17 Filip Rembiałkowski :
> regarding clustering: it does not help with index bloat.
I'm almost sure it does, CLUSTER re-creates all indexes from scratch
after copying the tuples.
Regards,
Marti
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes
On Thu, Dec 16, 2010 at 14:33, selvi88 wrote:
> I have a requirement for running more that 15000 queries per second.
> Can you please tell what all are the postgres parameters needs to be changed
> to achieve this.
You have not told us anything about what sort of queries they are or
you're
On Tue, Dec 21, 2010 at 11:09, Michael Ben-Nes wrote:
> Just stumbled on the following post:
> http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
>
> The post claim that MySQL can do more qps then MemCahed or any other NoSQL
> when doing simple queries like: SELECT
On Thu, Dec 23, 2010 at 22:52, Tom Lane wrote:
> Ben writes:
>> i have a schema similar to the following
>
>> create index foo_s_idx on foo using btree (s);
>> create index foo_e_idx on foo using btree (e);
>
>> i want to do queries like
>
>> select * from foo where 150 between s and e;
>
> That
On Fri, Jan 14, 2011 at 23:19, Chris Browne wrote:
> 2. In 9.1, there will be a new answer, as there's a GUC to indicate the
> "application_name".
Actually this was already introduced in PostgreSQL 9.0 :)
You can add application_name to your log_line_prefix with %a. For
pg_dump it will display
On Mon, Feb 7, 2011 at 05:03, Craig Ringer wrote:
> What would possibly help would be if Pg could fall back to lower
> shared_buffers automatically, screaming about it in the logs but still
> launching. OTOH, many people don't check the logs, so they'd think their
> new setting had taken effect an
On Mon, Feb 7, 2011 at 00:03, Mikkel Lauritsen wrote:
>>> SELECT * FROM table t1 WHERE 0 = (SELECT COUNT(*) FROM table t2 WHERE
>>> t2.type = t1.type AND t2.timestamp > t1.timestamp)
>>
>> I suspect that *any* database is going to have trouble optimizing that.
> Just out of curiosity I've bee
On Tue, Feb 8, 2011 at 18:36, Kevin Grittner
wrote:
> Yeah, current behavior with that shutdown option is the opposite of
> smart for any production environment I've seen. (I can see where it
> would be handy in development, though.) What's best in production
> is the equivalent of the fast opti
On Tue, Feb 8, 2011 at 22:09, Greg Smith wrote:
> Kevin and I both suggested a "fast plus timeout then immediate" behavior is
> what many users seem to want. My comments were at
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01145.php ; for an
> example of how fast shutdown can fail se
On Tue, Feb 15, 2011 at 21:33, Kim A. Brandt wrote:
> removing the ORDER BY worked. But I am afraid to ask this. How can I order
> by partition? It seams that the planner has picked a random(!?) order of
> partition to select from. The returned records, from the selected partition,
> are correctly
On Tue, Feb 15, 2011 at 20:01, Scott Marlowe wrote:
> run htop and look for red. if youi've got lots of red bar on each CPU
> but no io wait then it's waiting for memory access.
I don't think this is true. AFAICT the red bar refers to "system
time", time that's spent in the kernel -- either in s
On Fri, Feb 25, 2011 at 05:02, Sam Wong wrote:
> * But if I do this - using binding:
> $dbh->selectall_arrayref("SELECT * from shipment_lookup WHERE (UPPER(lookup)
> LIKE ?)", undef, '0GURG5YGVQA9%');
> It took 10 seconds to finish the query, just like it was using full table
> scan instead! Even
On Thu, Mar 10, 2011 at 17:40, fork wrote:
> The data is not particularly sensitive; if something happened and it rolled
> back, that wouldnt be the end of the world. So I don't know if I can use
> "dangerous" setting for WAL checkpoints etc. There are also aren't a lot of
> concurrent hits on
On Fri, Mar 11, 2011 at 21:06, fork wrote:
> Like the following? Will it rebuild the indexes in a sensical way?
Don't insert data into an indexed table. A very important point with
bulk-loading is that you should load all the data first, then create
the indexes. Running multiple (different) CREA
On Sun, Mar 13, 2011 at 18:36, runner wrote:
> Tried removing the indexes and other constraints just for
> the import but for a noob like me, this was too much to ask. Maybe
> when I get more experience.
pgAdmin should make it pretty easy. Choose each index and constraint,
save the code from the
2011/3/23 Laszlo Nagy :
> "GroupAggregate (cost=5553554.25..5644888.17 rows=2283348 width=50)"
> " -> Sort (cost=5553554.25..5559262.62 rows=2283348 width=50)"
> " Sort Key: pph.hid, ppoh.merchantid, pph.hdate"
> " -> Nested Loop (cost=0.00..5312401.66 rows=2283348 width=50)"
>
On Thu, Mar 24, 2011 at 11:11, Achilleas Mantzios
wrote:
> My problem had to do with the speed of gettimeofday. You might want to do
> some special setting regarding
> your box's way of reading time for the hw clock.
Just for extra info, on x86, TSC is usually the "fast" timeofday
implementation
2011/3/25 DM :
> gettimeofday() on my new box is slow, after further research we found that,
> when we set ACPI=Off, we got a good clock performance even the explain
> analyze gave approximately gave the right values, but the hyperthreading is
> off.
Disabling ACPI also disables most CPU power man
On Wed, Mar 30, 2011 at 01:16, Samuel Gendler wrote:
> I've got some functionality that necessarily must scan a relatively large
> table
> Is there any performance benefit to revamping the workload such that it issues
> a single:
> insert into (...) select ... UNION select ... UNION select
> as
Qiang Wang wrote:
> We have PostgreSQL 8.3 running on Debian Linux server. We built an
> applicantion using PHP programming language and Postgres database. There are
> appoximatly 150 users using the software constantly. We had some performance
> degration before and after some studies we figured
On Thu, Jun 9, 2011 at 13:03, muthu krishnan
wrote:
> Thank you for suggesting the valuable URL, we are getting 3 "floating point
> assist fault" error for every second, will it impact the performance for
> postgresql?
Probably.
The kernel throttles these messages, so you're probably performing
On Wed, Jun 8, 2011 at 07:19, bakkiya wrote:
> We have a postgresql 8.3.8 DB which consumes 100% of the CPU whenever we run
> any query. We got vmstat output Machine details are below:
Any query? Does even "SELECT 1" not work? Or "SELECT * FROM sometable LIMIT 1"
Or are you having problems with
On Thu, Jun 16, 2011 at 21:36, Shaun Thomas wrote:
> You can call that instead of max, and it'll be much faster. You can create
> an analog for min if you need it. So for this, you'd call:
Cool, I've needed this function sometimes but never bothered enough to
write it myself. Now I created a wiki
On Sun, Sep 11, 2011 at 17:23, Andy Colson wrote:
> On 09/11/2011 08:59 AM, Igor Chudov wrote:
>> By the way, does that INSERT UPDATE functionality or something like this
>> exist in Postgres?
> You have two options:
> 1) write a function like:
> create function doinsert(_id integer, _value text)
On Mon, Sep 12, 2011 at 23:04, Shaun Thomas wrote:
> I was alluding to the fact that if a DBA had his system running for a week
> at our transaction level, and PG didn't have forced auto vacuum, and their
> maintenance lapsed even slightly, they could end up with a corrupt database.
It doesn't ac
On Tue, Sep 13, 2011 at 00:26, Robert Klemme wrote:
> In the case of PG this particular example will work:
> 1. TX inserts new PK row
> 2. TX tries to insert same PK row => blocks
> 1. TX commits
> 2. TX fails with PK violation
> 2. TX does the update (if the error is caught)
That goes against th
On Tue, Sep 13, 2011 at 19:34, Robert Klemme wrote:
> I don't think so. You only need to catch the error (see attachment).
> Or does this create a sub transaction?
Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a
SAVEPOINT it can roll back to in case of an error.
> Yes, I men
On Fri, Sep 16, 2011 at 17:50, Claudio Freire wrote:
> It's not an issue for me (it's not really impacting performance), but
> since it was odd I thought I might ask.
>
> I have this supermegaquery:
>
> SELECT
> t.date AS status_date, lu.id AS memberid, lu.username AS
> username, u.url AS ur
On Mon, Sep 26, 2011 at 15:51, Antonio Rodriges wrote:
>> What is read_cluster() ? Are you talking about some kind of async and/or
>
> I meant that if you want to read a chunk of data from file you (1)
> might not call traditional fseek but rather memorize hard drive
> cluster numbers to boost di
1. First things first: vacuum cannot delete tuples that are still
visible to any old running transactions. You might have some very long
queries or transactions that prevent it from cleaning properly:
select * from pg_stat_activity where xact_start < now()-interval '10 minutes';
2. On 8.3 and ear
On Fri, Oct 28, 2011 at 14:02, Albe Laurenz wrote:
> Without SSL the SELECT finished in about a second,
> with SSL it took over 23 seconds (measured with
> \timing in psql).
When you query with psql, it requests columns in text format. Since
bytea hex-encodes its value if output is text, this mea
On Tue, Nov 8, 2011 at 12:25, Albe Laurenz wrote:
> I can't get oprofile to run on this RHEL6 box, it doesn't record
> anything, so all I can test is total query duration.
Maybe this helps you with OProfile?
http://people.planetpostgresql.org/andrew/index.php?/archives/224-The-joy-of-Vx.html
Re
On Mon, Dec 5, 2011 at 14:14, Thiago Godoi wrote:
> My original query :
>
> select table1.id
> from table1, (select function(12345) id) table2
> where table1.kind = 1234
> and table1.id = table2.id
>
> "Nested Loop (cost=0.00..6.68 rows=1 width=12)"
> " Join Filter: ()"
> " -> Seq Scan on reco
On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk wrote:
> Let me guess, debian squeeze, with data and xlog on both on a single
> ext3 filesystem, and the fsync done by your commit (xlog) is flushing
> all the dirty data of the entire filesystem (including PG data writes)
> out before it can return...
2011/12/14 Rural Hunter :
> for example, the where condition is: where '' ~ col1. I created a normal
> index on col1 but seems it is not used.
I assume you want to search values that match one particular pattern,
that would be col1 ~ ''
The answer is, only very simple patterns that start
On Sun, Dec 18, 2011 at 16:52, Roxanne Reid-Bennett wrote:
> Is there an index type that can check "not equal"?
> This specific column has a limited number of possible values - it is
> essentially an enumerated list.
Instead of writing WHERE foo<>3 you could rewrite it as WHERE foo IN
(1,2,4,...)
On Mon, Dec 19, 2011 at 17:04, Rafael Martinez wrote:
> * Sudden decrease of swap when running backup/vacuum+analyze jobs
Do you know for certain that this memory use is attributed to
vacuum/analyze/backup, or are you just guessing? You should isolate
whether it's the vacuum or a backup process/b
On Fri, Jan 27, 2012 at 06:31, sridhar bamandlapally
wrote:
> --
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> --
> | 0
On Sat, Jan 28, 2012 at 19:11, Jayashankar K B
wrote:
> But we are stumped by the amount of CPU Postgres is eating up.
You still haven't told us *how* slow it actually is and how fast you
need it to be? What's your database layout like (tables, columns,
indexes, foreign keys)? What do the queries
On Wed, Feb 8, 2012 at 20:03, David Yeu wrote:
> * Š WHERE group_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET ?;
> * Pages of twenty rows.
A good improvement for this sort of queries is the "scalable paging"
trick. Instead of increasing the OFFSET argument -- which means that
Postgres has to
On Wed, Feb 15, 2012 at 20:33, Gabriel Biberian
wrote:
> Currently, i use the following query to update the filesystem table with the
> missing files :
> UPDATE filesystem SET dead=some_value WHERE dead=0 AND (SELECT 1 FROM
> temporary AS t WHERE t.hash=filesystem.hash LIMIT 1) IS NULL
I don't kn
On Thu, Mar 1, 2012 at 21:06, Kääriäinen Anssi wrote:
> The queries are "select * from the_table where id =
> ANY(ARRAY[list_of_numbers])"
> and the similar delete, too.
> [...] However, once you go into
> millions of items in the list, the query will OOM my Postgres server.
The problem with IN
On Wed, Mar 21, 2012 at 13:42, Rafael Martinez wrote:
> I am wondering why the time reported by \timing in psql is not the same
> as the time reported by duration in the log file when log_duration or
> log_min_duration_statement are on?
psql's \timing measures time on the client -- which includes
On Tue, Oct 16, 2012 at 10:47 PM, Karl Denninger wrote:
> Put an index on time_stamp (I assume there is one on id_signal already)
Well the optimal index for this particular query would include both columns:
(id_signal, time_stamp) -- in this order.
Additionally, if you want to take advantage of
On Mon, May 13, 2013 at 6:01 PM, ach wrote:
> what I'm wondering is, since
> the unique constraint already covers the whole table and all rows in
> entirety, is it really necessary for statistics to be set that high on
> those?
AFAIK if there are exact-matching unique constraints/indexes for a
qu
On Fri, May 31, 2013 at 7:32 PM, wrote:
> 1.) Is there any way to clear the cache so that we can ensure that when we
> run "explain analyze" on a query and make some minor adjustments to that
> query and re-execute, the plan is not cached.
PostgreSQL doesn't cache query plans if you do a normal
On Fri, Aug 8, 2014 at 4:05 AM, Evgeniy Shishkin wrote:
> select * from users join notifications on users.id=notifications.user_id
> ORDER BY users.priority desc ,notifications.priority desc limit 10;
In my understanding, i need to have two indexes
on users(priority desc, id)
On Tue, Aug 12, 2014 at 5:59 AM, 楊新波 wrote:
> why does the planer estimate 200 rows when i use order by and group by .
> evn:postgresql 8.4 and 9.3
> Can anybody suggest something or explain this behavior?
Because the table is empty, analyze doesn't store any stats for the
table, so the planner
On Thu, Aug 21, 2014 at 4:29 PM, Eli Naeher wrote:
> Clearly the bulk of the time is spent sorting the rows in the original
> table, and then again sorting the results of the subselect. But I'm afraid I
> don't really know what to do with this information. Is there any way I can
> speed this up?
On Thu, Aug 21, 2014 at 7:19 PM, Eli Naeher wrote:
> However, when I try to do a
> test self-join using it, Postgres does two seq scans across the whole table,
> even though I have indexes on both id and previous_stop_event:
> http://explain.depesz.com/s/ctck. Any idea why those indexes are not be
On Thu, Aug 28, 2014 at 11:50 AM, gmb wrote:
> Can somebody please confirm whether aggregate functions such as GROUP BY
> should use indexes ?
Yes, if the planner deems it faster than other approaches. It can make
wrong choices for many reasons, but usually when your planner tunables
like random_
On Fri, Oct 3, 2014 at 5:39 AM, Tom Lane wrote:
> Marc Slemko writes:
>> I ran into this oddity lately that goes against everything I thought I
>> understood and was wondering if anyone had any insight.
>
> SELECT * avoids a projection step ... see ExecAssignScanProjectionInfo.
It would be cool
On Fri, Oct 3, 2014 at 6:38 PM, Andrey Lizenko wrote:
> Is it possible to force optimizer choose the second plan without doing "set
> enable_hashjoin = off;" ?
>
> Increasing of 'effective_cache_size' leads to similar thing with mergejoin,
> other options (work_mem, shared_buffers. etc) do not c
73 matches
Mail list logo
