Thread: Postgres DB crashing

On 18/06/13 18:31, bhanu udaya wrote:
> Hello,
> Greetings.
>
> My PostgresSQL (9.2) is crashing after certain load tests. Currently,
> postgressql is crashing when simulatenously 800 to 1000 threads are run
> on a 10 million records schema. Not sure, if we have to tweak some more
> parameters of postgres. Currently, the postgressql is configured as
> below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres
> limitation to support only 800 threads or any other configuration
> required. Please look at the log as below with errors. Please reply
>
>
> max_connections    5000
> shared_buffers    2024 MB
> synchronous_commit     off
> wal_buffers     100 MB
> wal_writer_delays     1000ms
> checkpoint_segments     512
> checkpoint_timeout     5 min
> checkpoint_completion_target     0.5
> checkpoint_warning     30s
> work_memory     1G
> effective_cache_size     5 GB

Just to point out, your memory settings are set to allow *at least*

  shared-buffers 2GB + (5000 * 1GB) = 5TB+

You don't have that much memory. You probably don't have that much disk.
This is never going to work.

As has been said, there's no way you can do useful work simultaneously
with 1000 threads if you only have 4 cores - use a connection pooler.
You'll also need to reduce work_mem to 1MB or so.

--
   Richard Huxton
   Archonet Ltd

On Thu, Jun 20, 2013 at 5:17 AM, Richard Huxton <dev@archonet.com> wrote:
> On 18/06/13 18:31, bhanu udaya wrote:
>>
>> Hello,
>> Greetings.
>>
>> My PostgresSQL (9.2) is crashing after certain load tests. Currently,
>> postgressql is crashing when simulatenously 800 to 1000 threads are run
>> on a 10 million records schema. Not sure, if we have to tweak some more
>> parameters of postgres. Currently, the postgressql is configured as
>> below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres
>> limitation to support only 800 threads or any other configuration
>> required. Please look at the log as below with errors. Please reply
>>
>>
>> max_connections 5000
>> shared_buffers  2024 MB
>> synchronous_commit      off
>> wal_buffers     100 MB
>> wal_writer_delays       1000ms
>> checkpoint_segments     512
>> checkpoint_timeout      5 min
>> checkpoint_completion_target    0.5
>> checkpoint_warning      30s
>> work_memory     1G
>> effective_cache_size    5 GB
>
>
> Just to point out, your memory settings are set to allow *at least*
>
>  shared-buffers 2GB + (5000 * 1GB) = 5TB+
>
> You don't have that much memory. You probably don't have that much disk.
> This is never going to work.
>
> As has been said, there's no way you can do useful work simultaneously with
> 1000 threads if you only have 4 cores - use a connection pooler. You'll also
> need to reduce work_mem to 1MB or so.

aside: if you have particular query that needs extra work_mem, you can
always temporarily raise it at run time (unlike shared buffers).

OP needs to explore use of connection pooler, in particular pgbouncer.
 Anyways none of this explains why the server is actually crashing.

merlin

On Jun 29, 2013, at 3:59, bhanu udaya <udayabhanu1984@hotmail.com> wrote:

> Thanks. But, I do not want to convert into upper and show the result.

Why not? It won't modify your results, just the search condition:

SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val;

Or:

SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val;


> Example, if I have records as below:
> id  type
> 1. abcd
> 2. Abcdef
> 3. ABcdefg
> 4. aaadf
>
> The below query should report all the above
>
> select * from table where type like 'ab%'. It should get all above 3 records.  Is there a way the database itself can
bemade case-insensitive with UTF8 characterset. I tried with character type & collation POSIX, but it did not really
help.

I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the
OScollation mechanics). So, what OS are you running the server on? 

> > From: laurenz.albe@wien.gv.at
> > To: udayabhanu1984@hotmail.com; pgsql-general@postgresql.org
> > Subject: RE: Postgres case insensitive searches
> > Date: Fri, 28 Jun 2013 12:32:00 +0000

Please do not top-post on this list.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On Jun 29, 2013, at 15:02, bhanu udaya <udayabhanu1984@hotmail.com> wrote:

> I agree that it is just search condition. But, in a 2.5 million record table search, upper function is not that fast.
 

Suit yourself, the solution is there.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On 06/29/2013 09:02 AM, bhanu udaya wrote:<br /><blockquote cite="mid:COL127-W1529DEA2A427BDB5A28C9FD3770@phx.gbl"
type="cite"><style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style><div dir="ltr">Hello,<br /> I agree that it is just search condition. But, in a 2.5 million record table
search,upper function is not that fast.  The expectation is to get the query retrieved in 100 ms...with all indexes
used.<br/>  <br /> I tried with upper, Citext, but the result set was more than a second.<br />  <br /> The OS server
weare using is Linux 64 bit.<br />  <br /> Thanks and Regards<br /> Radha Krishna<br />  <br /><div>> Subject: Re:
[pgadmin-support][GENERAL] Postgres case insensitive searches<br /> > From: <a class="moz-txt-link-abbreviated"
href="mailto:haramrae@gmail.com">haramrae@gmail.com</a><br/> > Date: Sat, 29 Jun 2013 09:37:51 +0200<br /> > CC:
<aclass="moz-txt-link-abbreviated" href="mailto:laurenz.albe@wien.gv.at">laurenz.albe@wien.gv.at</a>; <a
class="moz-txt-link-abbreviated"href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>; <a
class="moz-txt-link-abbreviated"href="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a><br />
>To: <a class="moz-txt-link-abbreviated" href="mailto:udayabhanu1984@hotmail.com">udayabhanu1984@hotmail.com</a><br
/>> <br /> > On Jun 29, 2013, at 3:59, bhanu udaya <a class="moz-txt-link-rfc2396E"
href="mailto:udayabhanu1984@hotmail.com"><udayabhanu1984@hotmail.com></a>wrote:<br /> > <br /> > >
Thanks.But, I do not want to convert into upper and show the result. <br /> > <br /> > Why not? It won't modify
yourresults, just the search condition:<br /> > <br /> > SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BYval;<br /> > <br /> > Or:<br /> > <br /> > SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY
upper(val),val;<br /> > <br /> > <br /> > > Example, if I have records as below:<br /> > > id type<br
/>> > 1. abcd<br /> > > 2. Abcdef<br /> > > 3. ABcdefg<br /> > > 4. aaadf<br /> > > <br
/>> > The below query should report all the above <br /> > > <br /> > > select * from table where
typelike 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with
UTF8characterset. I tried with character type & collation POSIX, but it did not really help.<br /> > <br /> >
Iwas under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS
collationmechanics). So, what OS are you running the server on?<br /> > <br /></div></div></blockquote><br />
Duplicatethe column with an upper or lowercase version and run all queries against that.<br /><br /> CREATE TABLE foo
(<br/>     id serial PRIMARY KEY,<br />     val text,<br />     val_lower text<br /> );<br /><br /> Index val_lower.
Usetriggers to keep val and val_lower in sync and discard all attempts to write directly to val_lower. Then all queries
wouldbe of the form<br /><br /> SELECT id, val<br /> FROM foo<br /> WHERE val_lower LIKE 'ab%';<br /><br /> Wouldn't
wantto write every table like this, but if (a) query speed trumps all other requirements and (b) functional index,
CITEXT,etc. have all been rejected as not fast enough…<br /><br /> --Lee<br /><br /><br /><pre class="moz-signature"
cols="72">--
 
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
<a class="moz-txt-link-freetext" href="http://freecity.commons.gc.cuny.edu">http://freecity.commons.gc.cuny.edu</a>
</pre>

On 06/28/2013 03:21 AM, bhanu udaya wrote:
> Hello,
>
> Grettings,
>
> What is the best way of doing case insensitive searches in postgres
> using Like.
>
> Ilike - does not use indexes
> function based indexes are not as fast as required.
> CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... does
> not use index
> Collation Indexes creation with POSIX - does not really work.
> GIST/GIN indexes are faster when using like, but not case insenstive.
>
> Is there a better way of resolving this case insenstive searches with
> fast retrieval.

O.k. there is not anywhere near enough information here to provide you
with a proper answer but here are the two things you should look at:

CITEXT: You said it takes 600ms - 1 second. Is that a first run or is
the relation cached? Second how do you know it isn't using the index?
Have you ran an explain analyze? In order for CITEXT to use an index it
the value being searched must be the PRIMARY KEY, is your column the
primary key?

Second, you have provided us with zero information on your hardware
configuration. 2.2 million rows is a low of rows to seqscan, if they
aren't cached or if you don't have reasonable hardware it is going to
take time no matter what you do.

Third, have you tried this with unlogged tables (for performance)?

Fourth, there was another person that suggested using UPPER() that is a
reasonable suggestion. The docs clearly suggest using lower(), I don't
actually know if there is a difference but that is the common way to do
it and it will use an index IF you make a functional index on the column
using lower.

JD




>
> Thanks and Regards
> Radha Krishna
>


--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
    a rose in the deeps of my heart. - W.B. Yeats

On 06/29/2013 09:24 AM, bhanu udaya wrote:

> Upper and Lower functions are not right choice when the table is > 2.5
> million and where we also have heavy insert transactions.

Prove it. Seriously, just run a test case against it. See how it works
for you. Inserts are generally a very inexpensive operation with Postgres.

>
> I doubt, if we can cache the table if there are frequent
> inserts/updates.  The good idea would be to get the DB to case
> insenstive configuration like SQL Server. I would go for this solution,
> if postgres supports.

Postgres does not.

And as Jon said, maybe Postgres isn't the right solution for you. That
would be a bummer but we can't be all things to all people.


JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
    a rose in the deeps of my heart. - W.B. Yeats

bhanu udaya wrote:
>>> What is the best way of doing case insensitive searches in postgres using Like.
>>
>> Table "laurenz.t"
>> Column | Type | Modifiers
>> --------+---------+-----------
>> id | integer | not null
>> val | text | not null
>> Indexes:
>> "t_pkey" PRIMARY KEY, btree (id)
>>
>>
>> CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);
>>
>> ANALYZE t;
>>
>> EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';
>>
>> QUERY PLAN
>> ------------------------------------------------------------------------------
>> Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
>> Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
>> Filter: (upper(val) ~~ 'AB%'::text)
>> (3 rows)

> Thanks. But, I do not want to convert into upper and show the result.
> Example, if I have records as below:
> id  type
> 1. abcd
> 2. Abcdef
> 3. ABcdefg
> 4. aaadf
> 
> The below query should report all the above

No, it shouldn't :^)

> select * from table where type like 'ab%'. It should get all above 3 records.  Is there a way the
> database itself can be made case-insensitive with UTF8 characterset. I tried with character type &
> collation POSIX, but it did not really help.

My solution is fast and efficient, it will call upper() only once
per query.  I don't see your problem.  Different database systems
do things in different ways, but as long as you can do what you need
to do, that should be good enough.

Yours,
Laurenz Albe