Re: [PERFORM] MySQL HandlerSocket - Is this possible in PG?
Do you mean I should use PREPARE? Currently I use PHP to access the DB which use libpq. Is that cosidered a fast call API ? if not, can you please refer me to the right info. PHP pg_pconnect command open a persistent PostgreSQL connection. Is it enough or I better use PgPool2 or something similar? Considering the points above, will I be able to get such high QPS from PostgreSQL ? If so, it will be my pleasure to dump Reddis and work solely with PG :) I suppose you already have a web server like lighttpd, zeus, or nginx, using php as fastcgi, or apache behind a proxy ? In that case, since the number of php processes is limited (usually to something like 2x your number of cores), the number of postgres connections a web server generates is limited, and you can do without pgpool and use pg_pconnect. Be wary of the pg_pconnect bugs though (like if you restart pg, you also have to restart php, I suppose you know that). Here are some timings (Core 2 Q6600) for a simple SELECT on PK query : using tcp (localhost) 218 µs / query : pg_query 226 µs / query : pg_query_params 143 µs / query : pg_execute using unix sockets 107 µs / query : pg_query 122 µs / query : pg_query_params 63 µs / query : pg_execute query inside plpgsql function 17 µs / query Don't use PDO, it is 2x-3x slower. TCP overhead is quite large... If you have a named prepared statement (created with PREPARE) use pg_execute(), which is much faster than pg_query (for very simple queries). Of course you need to prepare the statements... you can do that with pg_pool which can execute a script upon connection initialization. -- 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?
On Tue, Dec 21, 2010 at 2:09 AM, Michael Ben-Nes mich...@epoch.co.il wrote: Hi, 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 * FROM table WHERE id=num; No it does not. They use an interface that bypasses SQL and is much more primitive. -- 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] Query uses incorrect index
Thanks for reply. I tested random changes and query runs fastest after: set seq_page_cost = 0.1; set random_page_cost = 0.1; cpu_operator_cost = 0.01 pasman -- 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] Query uses incorrect index
pasman pasmański pasman.p 'at' gmail.com writes: Thanks for reply. I tested random changes and query runs fastest after: set seq_page_cost = 0.1; set random_page_cost = 0.1; cpu_operator_cost = 0.01 If I'm correct, you're basically telling postgresql that your disk is unusually fast compared to your CPU. Even if some queries will run faster from a side-effect of these settings, you're likely to create other random problems... -- Guillaume Cottenceau -- 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?
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 queries like: SELECT * FROM table WHERE id=num;
And I wonder if:
1. Currently, is it possbile to achive the same using PG 9.0.x
2. Is it possible at all?
I was curious what could be done currently, without any modifications
to PostgreSQL itself, so I ran a simple benchmark.
Table:
create table usr (user_id int primary key not null, user_name text not
null, user_email text not null, created timestamp not null);
insert into usr select generate_series(1, 100), 'Yukari Takeba',
'yukari.tak...@dena.jp', '2010-02-03 11:22:33';
?php
$db = pg_connect('');
$res = pg_prepare($db, 'get_user', 'select user_name, user_email,
created from usr where user_id=$1');
$res = pg_query($db, 'begin');
$args = array();
for($i = 0; $i 25; $i++)
{
$args[0] = rand(1, 100);
$res = pg_execute($db, 'get_user', $args);
$row = pg_fetch_row($res);
}
?
Each process does 250k queries, so when I run 4 in parallel it's 1M
queries total.
I'm running PostgreSQL 9.1alpha2, PHP 5.3.4, kernel 2.6.36.2 on Arch
Linux; AMD Phenom II X4 955.
The only tuning I did was setting shared_buffers=256M
Results:
% time php pg.php time php pg.php time php pg.php time php pg.php sleep 11
[1] 29792
[2] 29793
[3] 29795
[4] 29797
php pg.php 1,99s user 0,97s system 30% cpu 9,678 total
[2]done time php pg.php
php pg.php 1,94s user 1,06s system 30% cpu 9,731 total
[3] - done time php pg.php
php pg.php 1,92s user 1,07s system 30% cpu 9,746 total
[1] - done time php pg.php
php pg.php 2,00s user 1,04s system 31% cpu 9,777 total
[4] + done time php pg.php
So around 10 seconds to run the test in total.
These numbers aren't directly comparable to their test -- I tested
over a local UNIX socket, with PHP client on the same machine -- but
it's a datapoint nevertheless.
Bottom line, you can expect up to 100 000 QPS using pg_execute() on a
cheap quad-core gamer CPU. You won't be beating memcached with current
PostgreSQL, but I think it's a respectable result.
Regards,
Marti
--
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 9.0 x64 bit pgbench TPC very low question?
As far as i know, Pgbouncer can help to minimum connect to postgresql, I want tool can open and keep 200 connect to postgresql (be cause start new connect to postgresql in windows very slow, i want it open 200 connect in first time and my application connect to this tool) Is there any tool like that in windows. Thanks for you help. Tuan Hoang ANh. On Tue, Dec 21, 2010 at 3:43 PM, Magnus Hagander mag...@hagander.netwrote: On Tue, Dec 21, 2010 at 04:35, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Dec 20, 2010 at 8:31 PM, tuanhoanganh hatua...@gmail.com wrote: Is there any tool work on windows can open 200 connect to postgresql and application connect to this tool to decrease time connect to PostgreSQL (because PostgreSQL start new process when have a new connect, I want this tool open and keep 200 connect to postgreSQL, my application connect to this tool instead of postgreSQL). Sure, that's what any good pooler can do. Have it open and hold open 200 connections, then have your app connect to the pooler. The pooler keeps the connects open all the time. The app connects to a much faster mechanism, the pooler each time. You need to make sure your connections are clean when you disconnect, i.e. no idle transactions left over, or you'll get weird errors about failed transactions til rollback etc. Yeah, AFAIK pgbouncer works fine on Windows, and is a very good pooler for PostgreSQL. I haven't run it on Windows myself, but it should support it fine... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [PERFORM] PostgreSQL 9.0 x64 bit pgbench TPC very low question?
On Wed, Dec 22, 2010 at 6:28 AM, tuanhoanganh hatua...@gmail.com wrote:
As far as i know, Pgbouncer can help to minimum connect to postgresql, I
want tool can open and keep 200 connect to postgresql (be cause start new
connect to postgresql in windows very slow, i want it open 200 connect in
first time and my application connect to this tool)
Is there any tool like that in windows.
Thanks for you help.
As Magnus said, pgBouncer does what you are asking for.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [PERFORM] Query uses incorrect index
Guillaume Cottenceau wrote: If I'm correct, you're basically telling postgresql that your disk is unusually fast compared to your CPU. Even if some queries will run faster from a side-effect of these settings, you're likely to create other random problems... If this is set globally and the active portion of the database is not highly cached, yes. If the example query is typical of the level of caching for frequently-run queries, it might provide an overall performance boost to set these in postgresql.conf. The original problem was that the optimizer was grossly over-estimating the cost of returning a tuple through the index, which was taking about 0.01 ms per tuple. -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] Query uses incorrect index
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: Thanks for reply. I tested random changes and query runs fastest after: set seq_page_cost = 0.1; set random_page_cost = 0.1; cpu_operator_cost = 0.01 As a general rule, optimizing those settings on the basis of testing a single query is a great way to send your overall performance into the tank --- especially since repeating a single query will be heavily biased by cache effects. You need to look at a representative sample of all your queries across all your data. 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] Query uses incorrect index
Hi. I install auto_explain module for monitoring queries. By the way, is any tool to tune planner automatically ? pasman -- 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?
On Wed, 22 Dec 2010 14:17:21 +0100, Michael Ben-Nes mich...@epoch.co.il wrote: Thanks, it is most interesting -- Michael Ben-Nes - Internet Consultant and Director. http://www.epoch.co.il - weaving the Net. Cellular: 054-4848113 -- In fact, it would be possible to implement something like MySQL HandlerSocket, using the following Very Ugly Hack : This would only work for ultra simple SELECT 1 row WHERE primary key = constant queries. - a pooler (separate process) waits for connections - clients connect to the pooler and send queries - pooler accumulates enough queries to justify the overhead of what's going to come next - pooler takes a bunch of queries and encodes them in some custom ad-hoc format (not SQL) - pooler says to postgres SELECT do_my_queries( serialized data ) - do_my_queries() is a user function (in C) which uses postgres access methods directly (like index access method on primary key), processes queries, and sends results back as binary data - repeat for next batch Nested Loop Index Scan processes about 400.000 rows/s which is 2.5 us/query, maybe you could get into that ballpark (per core). Of course it's a rather extremely ugly hack. --- Note that you could very possibly have almost the same benefits with almost none of the ugliness by doing the following : same as above : - a pooler (separate process) waits for connections - clients connect to the pooler and send queries in the format query + parameters (which libpq uses if you ask) - pooler accumulates enough queries to justify the overhead of what's going to come next different : - pooler looks at each query, and if it has not seen it yet on this particular pg connection, issues a PREPARE on the query - pooler sends, in one TCP block, a begin, then a bunch of execute named prepared statement with parameters commands, then a rollback - postgres executes all of those and returns all replies in one TCP block (this would need a modification) - pooler distributes results back to clients This would need a very minor change to postgres (coalescing output blocks). It would make the pooler pay TCP overhead instead of postgres, and greatly improve cache locality in postgres. Since error handling would be problematic (to say the least...) and expensive it would only work on simple selects. -- 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?
I think this might be a game changing feature. For the first time after 10 years I have reason to consider MySQL, as the cost per performance in such scenario is amazing. Morever I wont have to run it in single mod or loose other functionality by using this feautre. as I can access the ordinary interface on port 3306 and the fast interface on other port. 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. Thanks, Miki -- Michael Ben-Nes - Internet Consultant and Director. http://www.epoch.co.il - weaving the Net. Cellular: 054-4848113 -- On Tue, Dec 21, 2010 at 11:07 PM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Dec 21, 2010 at 10:50 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/12/21 Michael Ben-Nes mich...@epoch.co.il: Hi Pavel, Thanks for your quick answer. Can you please elaborate a bit more about the points bellow. On Tue, Dec 21, 2010 at 1:31 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello you can emulate it now. a) try to do a simple stored procedure, where you can wrap your query Do you mean I should use PREPARE? yes b) use a FAST CALL API to call this procedure Currently I use PHP to access the DB which use libpq. Is that cosidered a fast call API ? if not, can you please refer me to the right info. sorry it is a fast-path interface http://www.postgresql.org/docs/8.1/static/libpq-fastpath.html but php hasn't a adequate API :( I don't think fastpath interface is going to get you there. What they are doing with mysql is bypassing both the parser and the protocol. As soon as you use libpq, you've lost the battle...you can't see anywhere close to to that performance before you become network bottlenecked. If you want to see postgres doing this in action, you could fire up the database in single user mode and run raw queries against the backend. Another way to do it is to hack tcop/postgres.c and inject protocol messages manually. Right now, the only way to get that close to the metal using standard techniques is via SPI (plpgsql, etc). A proper transaction free stored procedure implementation would open a lot of doors for fast query execution. 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] BBU Cache vs. spindles
Bruce Momjian wrote: Greg Smith wrote: Kevin Grittner wrote: I assume that we send a full 8K to the OS cache, and the file system writes disk sectors according to its own algorithm. With either platters or BBU cache, the data is persisted on fsync; why do you see a risk with one but not the other I'd like a 10 minute argument please. I started to write something to refute this, only to clarify in my head the sequence of events that leads to the most questionable result, where I feel a bit less certain than I did before of the safety here. Here is the worst case I believe you're describing: 1) Transaction is written to the WAL and sync'd; client receives COMMIT. Since full_page_writes is off, the data in the WAL consists only of the delta of what changed on the page. 2) 8K database page is written to OS cache 3) PG calls fsync to force the database block out 4) OS writes first 4K block of the change to the BBU write cache. Worst case, this fills the cache, and it takes a moment for some random writes to process before it has space to buffer again (makes this more likely to happen, but it's not required to see the failure case here) 5) Sudden power interruption, second half of the page write is lost 6) Server restarts 7) That 4K write is now replayed from the battery's cache At this point, you now have a torn 8K page, with 1/2 old and 1/2 new Based on this report, I think we need to update our documentation and backpatch removal of text that says that BBU users can safely turn off full-page writes. Patch attached. I think we have fallen into a trap I remember from the late 1990's where I was assuming that an 8k-block based file system would write to the disk atomically in 8k segments, which of course it cannot. My bet is that even if you write to the kernel in 8k pages, and have an 8k file system, the disk is still accessed via 512-byte blocks, even with a BBU. Doc patch applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml index a2724fa..1e67bbd 100644 *** /tmp/pgrevert.14281/7sLqTb_wal.sgml Tue Nov 30 21:57:17 2010 --- doc/src/sgml/wal.sgml Tue Nov 30 21:56:49 2010 *** *** 164,173 productnamePostgreSQL/ periodically writes full page images to permanent WAL storage emphasisbefore/ modifying the actual page on disk. By doing this, during crash recovery productnamePostgreSQL/ can !restore partially-written pages. If you have a battery-backed disk !controller or file-system software that prevents partial page writes !(e.g., ZFS), you can turn off this page imaging by turning off the !xref linkend=guc-full-page-writes parameter. /para /sect1 --- 164,175 productnamePostgreSQL/ periodically writes full page images to permanent WAL storage emphasisbefore/ modifying the actual page on disk. By doing this, during crash recovery productnamePostgreSQL/ can !restore partially-written pages. If you have file-system software !that prevents partial page writes (e.g., ZFS), you can turn off !this page imaging by turning off the xref !linkend=guc-full-page-writes parameter. Battery-Backed unit !(BBU) disk controllers do not prevent partial page writes unless !they guarantee that data is written to the BBU as full (8kB) pages. /para /sect1 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
