Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  closed
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:  fixed
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Tim Graham ):

 In [changeset:"f7b0532ec0cf703849ef50e513e3dc6b5483a6b0" f7b0532]:
 {{{
 #!CommitTicketReference repository=""
 revision="f7b0532ec0cf703849ef50e513e3dc6b5483a6b0"
 Refs #28334 -- Fixed crash in hstore/citext oid caching during test db
 creation.
 }}}

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.35f22d1fd1968c9caef35666d5e1841a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  closed
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:  fixed
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham ):

 * status:  assigned => closed
 * resolution:   => fixed


Comment:

 In [changeset:"86a18dc46aae3a4a6410e3f3d864fa0ec4e5b2cd" 86a18dc]:
 {{{
 #!CommitTicketReference repository=""
 revision="86a18dc46aae3a4a6410e3f3d864fa0ec4e5b2cd"
 Fixed #28334 -- Added caching for hstore/citext OIDs.
 }}}

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.71960c5e0c6cd9ebf5f9572847e077d1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * needs_better_patch:  1 => 0
 * stage:  Accepted => Ready for checkin


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.d90fec3c1fe3513cb742fb603886a986%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * needs_better_patch:  0 => 1


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.25e7de13a7f146d6eebb8554c2631e1e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Igor Gumenyuk):

 * needs_tests:  1 => 0


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.cb9a30d8eab17496bfa8cc566450e44f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Claude Paroz):

 * needs_tests:  0 => 1


--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.fc8af1d1f91062f048580b8766545b23%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Igor Gumenyuk):

 All issues resolved, there are no failed tests

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.03a5aa37232c1c1754951213ced93852%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Igor Gumenyuk):

 * needs_better_patch:  1 => 0


--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.2dedaa80120b4665c5e4207e6d892720%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * needs_better_patch:  0 => 1


Comment:

 There are test failures on the pull request.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.89b02aa7fcef7db2f50177a93176c98d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Igor Gumenyuk):

 * has_patch:  0 => 1


--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.e433d4b896c78a1cd239e2548464a598%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  Igor
 Type:   |  Gumenyuk
  Cleanup/optimization   |   Status:  assigned
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Igor Gumenyuk):

 * status:  new => assigned
 * owner:  (none) => Igor Gumenyuk


--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.5019e6e717f4a46a59151fb46bfc9b29%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
--+
 Reporter:  Igor Gumenyuk |Owner:  (none)
 Type:  Cleanup/optimization  |   Status:  new
Component:  contrib.postgres  |  Version:  1.11
 Severity:  Normal|   Resolution:
 Keywords:| Triage Stage:  Accepted
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+
Changes (by Claude Paroz):

 * stage:  Unreviewed => Accepted


--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.34703ed19de79e3b6202b3c97ef231f1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
-+-
 Reporter:  Igor Gumenyuk|Owner:  (none)
 Type:   |   Status:  new
  Cleanup/optimization   |
Component:  contrib.postgres |  Version:  1.11
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Description changed by Igor Gumenyuk:

Old description:

> On each newly created database connection `django.contrib.postgresql`
> tries to register hstore type for this connection via
> `connection_created` signal and `register_hstore` function.
>
> https://github.com/django/django/blob/master/django/contrib/postgres/apps.py#L20
> https://github.com/django/django/blob/stable/1.11.x/django/contrib/postgres/signals.py#L16
>
> Behind the scenes `register_hstore` runs sql query to get `oid` of
> `hstore` type:
> https://github.com/psycopg/psycopg2/blob/master/lib/extras.py#L885
>
> This happens on every(!) request (unless persistent connections enabled)
> SELECT t.oid, %s
> FROM pg_type t JOIN pg_namespace ns
> ON typnamespace = ns.oid
> WHERE typname = 'hstore';
>
> This is just huge overhead, since `register_hstore` accepts `oid`
> argument to avoid hitting database every call.
> We have seen significant latency spikes because of this.
>
> Possible solution would be have configurable `GET_HSTORE_OID_FUNC` in
> `DATABASES->OPTIONS` setting which can be cached in any way.
> This will also maintain backwards compatibility.
>
> I can send patch for this if proposed solution is acceptable to be merged
> in master.

New description:

 On each newly created database connection `django.contrib.postgresql`
 tries to register hstore type for this connection via `connection_created`
 signal and `register_hstore` function.

 
https://github.com/django/django/blob/master/django/contrib/postgres/apps.py#L20
 
https://github.com/django/django/blob/stable/1.11.x/django/contrib/postgres/signals.py#L16

 Behind the scenes `register_hstore` runs sql query to get `oid` of
 `hstore` type:
 https://github.com/psycopg/psycopg2/blob/master/lib/extras.py#L885

 This happens on every(!) request (unless persistent connections enabled)
 SELECT t.oid, %s
 FROM pg_type t JOIN pg_namespace ns
 ON typnamespace = ns.oid
 WHERE typname = 'hstore';

 This is just huge overhead, since `register_hstore` accepts `oid` argument
 to avoid hitting database every call.
 We have seen significant latency spikes because of this.

 Possible solution would be to have configurable `GET_HSTORE_OID_FUNC` in
 `DATABASES->OPTIONS` setting which can be cached in any way.
 This will also maintain backwards compatibility.

 I can send patch for this if proposed solution is acceptable to be merged
 in master.

--

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.5ba32aff8c0e066e634c6937ade4f493%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #28334: contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Django]

#28334: contrib.postgresql overwhelms database with "select from pg_type" 
queries
on each request
+
   Reporter:  Igor Gumenyuk |  Owner:  (none)
   Type:  Cleanup/optimization  | Status:  new
  Component:  contrib.postgres  |Version:  1.11
   Severity:  Normal|   Keywords:
   Triage Stage:  Unreviewed|  Has patch:  0
Needs documentation:  0 |Needs tests:  0
Patch needs improvement:  0 |  Easy pickings:  0
  UI/UX:  0 |
+
 On each newly created database connection `django.contrib.postgresql`
 tries to register hstore type for this connection via `connection_created`
 signal and `register_hstore` function.

 
https://github.com/django/django/blob/master/django/contrib/postgres/apps.py#L20
 
https://github.com/django/django/blob/stable/1.11.x/django/contrib/postgres/signals.py#L16

 Behind the scenes `register_hstore` runs sql query to get `oid` of
 `hstore` type:
 https://github.com/psycopg/psycopg2/blob/master/lib/extras.py#L885

 This happens on every(!) request (unless persistent connections enabled)
 SELECT t.oid, %s
 FROM pg_type t JOIN pg_namespace ns
 ON typnamespace = ns.oid
 WHERE typname = 'hstore';

 This is just huge overhead, since `register_hstore` accepts `oid` argument
 to avoid hitting database every call.
 We have seen significant latency spikes because of this.

 Possible solution would be have configurable `GET_HSTORE_OID_FUNC` in
 `DATABASES->OPTIONS` setting which can be cached in any way.
 This will also maintain backwards compatibility.

 I can send patch for this if proposed solution is acceptable to be merged
 in master.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/049.66f085d27e9733819ee2c950e8356fb4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.