Hi,
On Tue, Feb 23, 2010 at 12:51 AM, Yang Zhang wrote:
> When running the query in MySQL InnoDB:
>
> $ vmstat 10
> procs ---memory-- ---swap-- -io --system--
> -cpu--
> r b swpd free buff cache si so bi bo in cs us sy id wa
> st
> 0 13 137
On Tue, Feb 23, 2010 at 00:02, Yang Zhang wrote:
> Thing is, this is how I got here:
>
> - ran complex query that does SELECT INTO.
> - that never terminated, so killed it and tried a simpler SELECT (the
> subject of this thread) from psql to see how long that would take.
You might have better lu
On Tue, Feb 23, 2010 at 9:48 AM, Yeb Havinga wrote:
> Greg Stark wrote:
>>
>> You can do \set FETCH_COUNT to have psql use a cursor automatically.
>>
>
> It seems like a big win in this case. What would be the downside of having a
> fetch_count set default in psql?
They were mentioned previously
Greg Stark wrote:
You can do \set FETCH_COUNT to have psql use a cursor automatically.
It seems like a big win in this case. What would be the downside of
having a fetch_count set default in psql?
regards
Yeb Havinga
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
I am under the impression that MySQL does not have anything resembling
Postgres' support for regular expressions. Though some might think
that regular expressions are a sort of poor man's SQL, in any
application which manages large amounts of text they are crucial.
Postgres definitely doe
On Tue, Feb 23, 2010 at 6:48 AM, Scott Marlowe wrote:
>> I'm relieved that Postgresql itself does not, in fact, suck, but
>> slightly disappointed in the behavior of psql. I suppose it needs to
>> buffer everything in memory to properly format its tabular output,
>> among other possible reasons I
On Tue, Feb 23, 2010 at 1:48 AM, Scott Marlowe wrote:
> On Mon, Feb 22, 2010 at 10:51 PM, Yang Zhang wrote:
>> nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
>> wrote:
>>>
>>> What do things like vmstat 10 say while the query is running on each
>>> db? First time, second time, things like
On Mon, Feb 22, 2010 at 22:51, Yang Zhang wrote:
> vmstat showed no swapping-out for a while, and then suddenly it
> started spilling a lot. Checking psql's memory stats showed that it
> was huge -- apparently, it's trying to store its full result set in
> memory. As soon as I added a LIMIT 1,
On Mon, Feb 22, 2010 at 10:51 PM, Yang Zhang wrote:
> nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
> wrote:
>>
>> What do things like vmstat 10 say while the query is running on each
>> db? First time, second time, things like that.
>
> Awesome -- this actually led me to discover the prob
Yang Zhang writes:
> I'm relieved that Postgresql itself does not, in fact, suck, but
> slightly disappointed in the behavior of psql. I suppose it needs to
> buffer everything in memory to properly format its tabular output,
> among other possible reasons I could imagine.
That's half of it, and
Yang Zhang writes:
>> # select count(1) from (SELECT * from metarelcould_transactionlog
>> order by transactionid) as foo;
> Does it strike anyone else that the query optimizer/rewriter should be
> able to toss out the sort from such a query altogether?
It could, if it knew that the aggregate fu
On Mon, Feb 22, 2010 at 9:30 PM, Alex Hunsaker wrote:
> On Mon, Feb 22, 2010 at 11:10, Yang Zhang wrote:
>> I have the exact same table of data in both MySQL and Postgresql. In ?>
>> Postgresql:
>
> FWIW on a stock (unchanged postgresql.conf) 8.3.9 I get (best of 3
> runs) 79 seconds, 26 using a
nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
wrote:
> On Mon, Feb 22, 2010 at 12:53 PM, Yang Zhang wrote:
>> On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe
>> wrote:
>>> On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote:
I have the exact same table of data in both MySQL and Postgr
On Mon, Feb 22, 2010 at 11:10, Yang Zhang wrote:
> I have the exact same table of data in both MySQL and Postgresql. In ?>
> Postgresql:
FWIW on a stock (unchanged postgresql.conf) 8.3.9 I get (best of 3
runs) 79 seconds, 26 using an index and 27 seconds with it clustered.
Now yes it goes a lot
On Mon, Feb 22, 2010 at 3:33 PM, Yang Zhang wrote:
> On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman wrote:
>> When in doubt - test.
>> Why not remove index in MySQL (or create index in PostgreSQL) and see
>> what happens.
>> Why trying compare "apples and oranges"?
>
> Continue reading this thread
On Mon, Feb 22, 2010 at 3:33 PM, Yang Zhang wrote:
> On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman wrote:
>> When in doubt - test.
>> Why not remove index in MySQL (or create index in PostgreSQL) and see
>> what happens.
>> Why trying compare "apples and oranges"?
>
> Continue reading this thread
On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman wrote:
> When in doubt - test.
> Why not remove index in MySQL (or create index in PostgreSQL) and see
> what happens.
> Why trying compare "apples and oranges"?
Continue reading this thread -- I also tried using an index in Postgresql.
--
Yang Zhang
When in doubt - test.
Why not remove index in MySQL (or create index in PostgreSQL) and see
what happens.
Why trying compare "apples and oranges"?
Igor Neyman
> -Original Message-
> From: Yang Zhang [mailto:yanghates...@gmail.com]
> Sent: Monday, February 22, 2010 1:37 PM
> To: Richard
Yeb Havinga writes:
> Just reading up on this interesting thread. WFIW, 2 years ago I and a
> collegue of mine did a hardware comparison of early Intel and AMD
> desktop quadcore processors to run postgres database, with most other
> parts comparable. The intel processor was 20 to 30 % faster i
On Mon, Feb 22, 2010 at 3:44 PM, Tom Lane wrote:
> Yang Zhang writes:
>> On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule
>> wrote:
>>> the speed depends on setting of working_memory. Try to increase a
>>> working_memory
>
>> It's already at
>> 2kB
>
> According to your original posting, yo
On Mon, Feb 22, 2010 at 12:53 PM, Yang Zhang wrote:
> On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe
> wrote:
>> On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote:
>>> I have the exact same table of data in both MySQL and Postgresql. In
>>> Postgresql:
>>
>> Just wondering, are these on the s
Yang Zhang writes:
> On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule
> wrote:
>> the speed depends on setting of working_memory. Try to increase a
>> working_memory
> It's already at
> 2kB
According to your original posting, you're trying to sort something like
a gigabyte of data. 20MB i
Scott Marlowe wrote:
On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote:
I have the exact same table of data in both MySQL and Postgresql. In Postgresql:
Just wondering, are these on the same exact machine?
Just reading up on this interesting thread. WFIW, 2 years ago I and a
col
On Mon, Feb 22, 2010 at 2:41 PM, Frank Heikens wrote:
>
> Op 22 feb 2010, om 20:28 heeft Yang Zhang het volgende geschreven:
>>
>>
>>>
>>>
> If your work-mem is too low there's a good chance that Postgres has to
> use your disks for sorting, which will obviously be quite slow.
Re
On Mon, Feb 22, 2010 at 12:50 PM, Yang Zhang wrote:
> On Mon, Feb 22, 2010 at 2:39 PM, Scott Marlowe
> wrote:
>> On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang wrote:
>>> This isn't some microbenchmark. This is part of our actual analytical
>>> application. We're running large-scale graph partiti
On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe wrote:
> On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote:
>> I have the exact same table of data in both MySQL and Postgresql. In
>> Postgresql:
>
> Just wondering, are these on the same exact machine?
>
Yes, on the same disk.
--
Yang Zhang
htt
On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote:
> I have the exact same table of data in both MySQL and Postgresql. In
> Postgresql:
Just wondering, are these on the same exact machine?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscripti
On Mon, Feb 22, 2010 at 2:39 PM, Scott Marlowe wrote:
> On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang wrote:
>> This isn't some microbenchmark. This is part of our actual analytical
>> application. We're running large-scale graph partitioning algorithms.
>
> It's important to see how it runs if yo
Op 22 feb 2010, om 20:28 heeft Yang Zhang het volgende geschreven:
If your work-mem is too low there's a good chance that Postgres
has to
use your disks for sorting, which will obviously be quite slow.
Relative to the non-terminating 80-minute-so-far sort, Unix sort
runs
much faster
On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang wrote:
> This isn't some microbenchmark. This is part of our actual analytical
> application. We're running large-scale graph partitioning algorithms.
It's important to see how it runs if you can fit more / most of the
data set into memory by cranking
On Mon, Feb 22, 2010 at 2:27 PM, Alvaro Herrera
wrote:
> Yang Zhang escribió:
>> I have the exact same table of data in both MySQL and Postgresql. In
>> Postgresql:
>
> I just noticed two things:
>
> [snip lots of stuff]
>
> 1.
>
>> ) ENGINE=MyISAM AUTO_INCREMENT=50410166 DEFAULT CHARSET=latin1
>
Yang Zhang escribió:
> I have the exact same table of data in both MySQL and Postgresql. In
> Postgresql:
I just noticed two things:
[snip lots of stuff]
1.
> ) ENGINE=MyISAM AUTO_INCREMENT=50410166 DEFAULT CHARSET=latin1
You're doing a comparison to MyISAM.
2.
> select * from metarelclo
On Mon, Feb 22, 2010 at 2:15 PM, Frank Heikens wrote:
>
> Op 22 feb 2010, om 20:07 heeft Yang Zhang het volgende geschreven:
>
>> On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
>> wrote:
>>>
>>> On 22 Feb 2010, at 19:35, Yang Zhang wrote:
>>>
I also wouldn't have imagined an external merge-
Op 22 feb 2010, om 20:07 heeft Yang Zhang het volgende geschreven:
On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
wrote:
On 22 Feb 2010, at 19:35, Yang Zhang wrote:
I also wouldn't have imagined an external merge-sort as being very
Where's that external merge-sort coming from? Can you s
On Mon, Feb 22, 2010 at 2:03 PM, Alvaro Herrera
wrote:
> Yang Zhang escribió:
>
>> I'm running:
>>
>> select * from metarelcloud_transactionlog order by transactionid;
>>
>> It takes MySQL 6 minutes, but Postgresql is still running after 70
>> minutes. Is there something like a glaring misconfig
On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
wrote:
> On 22 Feb 2010, at 19:35, Yang Zhang wrote:
>
>> I also wouldn't have imagined an external merge-sort as being very
>
>
> Where's that external merge-sort coming from? Can you show an explain analyze?
I just assumed that the "Sort" in the E
Yang Zhang escribió:
> I'm running:
>
> select * from metarelcloud_transactionlog order by transactionid;
>
> It takes MySQL 6 minutes, but Postgresql is still running after 70
> minutes. Is there something like a glaring misconfiguration that I'm
> overlooking? Thanks in advance.
How large i
On 22 Feb 2010, at 19:35, Yang Zhang wrote:
> I also wouldn't have imagined an external merge-sort as being very
Where's that external merge-sort coming from? Can you show an explain analyze?
If your work-mem is too low there's a good chance that Postgres has to use your
disks for sorting, whi
Op 22 feb 2010, om 19:30 heeft Richard Broersma het volgende geschreven:
On Mon, Feb 22, 2010 at 10:17 AM, Frank Heikens
wrote:
There is no index on the column transactionid in your PostgreSQL-
table, as
there is in your MySQL-table. This explains the difference.
CREATE INDEX i_transacti
On Mon, Feb 22, 2010 at 1:30 PM, Richard Broersma
wrote:
> On Mon, Feb 22, 2010 at 10:17 AM, Frank Heikens wrote:
>
>> There is no index on the column transactionid in your PostgreSQL-table, as
>> there is in your MySQL-table. This explains the difference.
>>
>> CREATE INDEX i_transactionid ON pu
On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule wrote:
> hello
>
> the speed depends on setting of working_memory. Try to increase a
> working_memory
>
> set working_memory to '10MB';
It's already at
tpcc=# show work_mem;
work_mem
--
2kB
(1 row)
I also wouldn't have imagined an ex
On Mon, Feb 22, 2010 at 10:17 AM, Frank Heikens wrote:
> There is no index on the column transactionid in your PostgreSQL-table, as
> there is in your MySQL-table. This explains the difference.
>
> CREATE INDEX i_transactionid ON public.metarelcloud_transactionlog
> (transactionid);
Does an inde
There is no index on the column transactionid in your PostgreSQL-
table, as there is in your MySQL-table. This explains the difference.
CREATE INDEX i_transactionid ON public.metarelcloud_transactionlog
(transactionid);
Op 22 feb 2010, om 19:10 heeft Yang Zhang het volgende geschreven:
I h
hello
the speed depends on setting of working_memory. Try to increase a working_memory
set working_memory to '10MB';
Regards
Pavel Stehule
2010/2/22 Yang Zhang :
> I have the exact same table of data in both MySQL and Postgresql. In
> Postgresql:
>
> tpcc=# \d metarelcloud_transactionlog
>
I have the exact same table of data in both MySQL and Postgresql. In Postgresql:
tpcc=# \d metarelcloud_transactionlog
Table
"public.metarelcloud_transactionlog"
Column| Type |
Modifiers
-+---
45 matches
Mail list logo
