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.
On Sun, Oct 31, 2010 at 21:59, Greg Smith g...@2ndquadrant.com 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,
On Mon, Nov 1, 2010 at 14:49, Divakar Singh dpsma...@yahoo.com 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
On Mon, Nov 1, 2010 at 14:56, Divakar Singh dpsma...@yahoo.com 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
Just some ideas that went through my mind when reading your post.
On Wed, Nov 3, 2010 at 17:52, Nick Matheson nick.d.mathe...@noaa.gov 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,
On Fri, Nov 5, 2010 at 13:32, A B gentosa...@gmail.com 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
On Fri, Nov 5, 2010 at 13:11, Guillaume Cottenceau g...@mnc.ch 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
On Fri, Nov 5, 2010 at 23:10, Greg Smith g...@2ndquadrant.com 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
On Sat, Nov 6, 2010 at 00:06, Greg Smith g...@2ndquadrant.com 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
On Mon, Nov 8, 2010 at 01:35, Greg Smith g...@2ndquadrant.com 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
On Mon, Nov 8, 2010 at 02:05, Greg Smith g...@2ndquadrant.com 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
On Mon, Nov 8, 2010 at 20:40, Tom Lane t...@sss.pgh.pa.us 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
On Sat, Nov 13, 2010 at 07:53, Craig Ringer cr...@postnewspapers.com.au 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
On Thu, Nov 11, 2010 at 20:25, Kyriacos Kyriacou
kyriac...@prime-tel.com 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,
On Sat, Nov 13, 2010 at 20:01, Tom Lane t...@sss.pgh.pa.us 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
On Wed, Nov 17, 2010 at 01:31, Tom Lane t...@sss.pgh.pa.us 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
On Sat, Dec 4, 2010 at 13:56, Mathieu De Zutter math...@dezutter.org 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
On Thu, Dec 9, 2010 at 01:26, Andy angelf...@yahoo.com 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,
On Thu, Dec 9, 2010 at 04:28, Scott Marlowe scott.marl...@gmail.com wrote:
On Wed, Dec 8, 2010 at 5:03 PM, Benjamin Krajmalnik k...@servoyant.com
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
On Wed, Dec 15, 2010 at 08:56, AI Rumman rumman...@gmail.com 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
2010/12/17 Filip Rembiałkowski filip.rembialkow...@gmail.com:
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
On Thu, Dec 16, 2010 at 14:33, selvi88 selvi@gmail.com 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
On Tue, Dec 21, 2010 at 11:09, Michael Ben-Nes mich...@epoch.co.il 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
On Thu, Dec 23, 2010 at 22:52, Tom Lane t...@sss.pgh.pa.us wrote:
Ben midfi...@gmail.com 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
On Fri, Jan 14, 2011 at 23:19, Chris Browne cbbro...@acm.org 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
On Mon, Feb 7, 2011 at 05:03, Craig Ringer cr...@postnewspapers.com.au 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
On Mon, Feb 7, 2011 at 00:03, Mikkel Lauritsen ren...@tala.dk 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
On Tue, Feb 8, 2011 at 18:36, Kevin Grittner
kevin.gritt...@wicourts.gov 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
On Tue, Feb 8, 2011 at 22:09, Greg Smith g...@2ndquadrant.com 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
On Tue, Feb 15, 2011 at 21:33, Kim A. Brandt kimabra...@gmx.de 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,
On Tue, Feb 15, 2011 at 20:01, Scott Marlowe scott.marl...@gmail.com 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
On Fri, Feb 25, 2011 at 05:02, Sam Wong s...@hellosam.net 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
On Thu, Mar 10, 2011 at 17:40, fork forkandw...@gmail.com 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
On Fri, Mar 11, 2011 at 21:06, fork forkandw...@gmail.com 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
On Sun, Mar 13, 2011 at 18:36, runner run...@winning.com 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
2011/3/23 Laszlo Nagy gand...@shopzeus.com:
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
On Thu, Mar 24, 2011 at 11:11, Achilleas Mantzios
ach...@matrix.gatewaynet.com 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
On Wed, Mar 30, 2011 at 01:16, Samuel Gendler sgend...@ideasculptor.com 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
Qiang Wang forest_qi...@yahoo.com 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
On Thu, Jun 9, 2011 at 13:03, muthu krishnan
muthu.krishnan.li...@gmail.com 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
On Wed, Jun 8, 2011 at 07:19, bakkiya bakk...@gmail.com 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
On Thu, Jun 16, 2011 at 21:36, Shaun Thomas stho...@peak6.com 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
On Sun, Sep 11, 2011 at 17:23, Andy Colson a...@squeakycode.net 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,
On Mon, Sep 12, 2011 at 23:04, Shaun Thomas stho...@peak6.com 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.
On Tue, Sep 13, 2011 at 00:26, Robert Klemme shortcut...@googlemail.com 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)
On Tue, Sep 13, 2011 at 19:34, Robert Klemme shortcut...@googlemail.com 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
On Fri, Sep 16, 2011 at 17:50, Claudio Freire klaussfre...@gmail.com 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
On Mon, Sep 26, 2011 at 15:51, Antonio Rodriges antonio@gmail.com 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
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
On Fri, Oct 28, 2011 at 14:02, Albe Laurenz laurenz.a...@wien.gv.at 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
On Tue, Nov 8, 2011 at 12:25, Albe Laurenz laurenz.a...@wien.gv.at 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?
On Mon, Dec 5, 2011 at 14:14, Thiago Godoi thiagogodo...@gmail.com 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 Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca 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
2011/12/14 Rural Hunter ruralhun...@gmail.com:
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
On Sun, Dec 18, 2011 at 16:52, Roxanne Reid-Bennett r...@tara-lu.com 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 foo3 you could rewrite it as WHERE foo IN
On Mon, Dec 19, 2011 at 17:04, Rafael Martinez r.m.guerr...@usit.uio.no 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
On Fri, Jan 27, 2012 at 06:31, sridhar bamandlapally
sridhar@gmail.com wrote:
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
On Sat, Jan 28, 2012 at 19:11, Jayashankar K B
jayashankar...@lnties.com 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
On Wed, Feb 8, 2012 at 20:03, David Yeu david@skype.net 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
On Wed, Feb 15, 2012 at 20:33, Gabriel Biberian
ad...@beemotechnologie.com 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
On Thu, Mar 1, 2012 at 21:06, Kääriäinen Anssi anssi.kaariai...@thl.fi 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.
On Wed, Mar 21, 2012 at 13:42, Rafael Martinez r.m.guerr...@usit.uio.no 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
On Tue, Oct 16, 2012 at 10:47 PM, Karl Denninger k...@denninger.net 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
On Mon, May 13, 2013 at 6:01 PM, ach alanchi...@gmail.com 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
On Fri, May 31, 2013 at 7:32 PM, fburg...@radiantblue.com 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
On Fri, Aug 8, 2014 at 4:05 AM, Evgeniy Shishkin itparan...@gmail.com 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,
On Tue, Aug 12, 2014 at 5:59 AM, 楊新波 silent0...@gmail.com 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
On Thu, Aug 21, 2014 at 4:29 PM, Eli Naeher enae...@gmail.com 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
On Thu, Aug 21, 2014 at 7:19 PM, Eli Naeher enae...@gmail.com 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
On Thu, Aug 28, 2014 at 11:50 AM, gmb gmbou...@gmail.com 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
On Fri, Oct 3, 2014 at 5:39 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Marc Slemko ma...@znep.com 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
On Fri, Oct 3, 2014 at 6:38 PM, Andrey Lizenko lizenk...@gmail.com 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.
72 matches
Mail list logo
