Re: Admin broken with large data table

[Malcolm Box]

On Mar 29, 4:31 pm, Malcolm Box  wrote:
> On Mar 29, 2:48 pm, Jacob Kaplan-Moss  wrote:
>
> > On Tue, Mar 29, 2011 at 5:56 AM, Malcolm Box  wrote:
> > > On one of my models, the admin choice_list page is taking minutes to
> > > load, which makes it somewhat broken.
>
> > > The table has about 2M rows and about 2.6GB in size, on InnoDB/MySQL.
> > > As far as I can tell, what's breaking things is the paginator code
> > > that is doing a SELECT COUNT(*) which is known to be glacially slow on
> > > InnoDB with certain types of table.
>
> > Yup, this is a known problem: pagination in the admin isn't efficient
> > and breaks down past a certain point.
>
> OK, thanks. Is there a ticket tracking the problem, I couldn't spot
> one?

Looked some more and found several tickets that relate:

https://code.djangoproject.com/ticket/84088 asks for switching off
count(*)
https://code.djangoproject.com/ticket/4065 asks for disabling
pagination completely.

> > > Is there any way to suppress the pagination and/or change it so that
> > > it doesn't do the queryset.count()?
>
> > In 1.3 you should be able to override ModelAdmin.get_paginator
> > (http://docs.djangoproject.com/en/1.3/ref/contrib/admin/#django.contri...).
> > You'll have to subclass django.core.paginator.Paginator and provide an
> > interface that doesn't perform COUNTs.
>

It turns out you have to do a bunch more than that - the admin_list,
ChangeList and changelist_view code all make the assumption that
asking a Paginator for the number of items or number of pages is a
valid operation. On a non-counting paginator, that doesn't work.

> > [Yes, this is sorta tricky, and ideally it'd be something Django does
> > for you so if you'll share your code when you figure it out I'll try
> > to work it into the next release.]

I've attached a patch to https://code.djangoproject.com/ticket/8408
which is working for me on big tables where previously the admin
totally broke.

Any feedback on the patch would be welcome - if it looks reasonable
I'll put together the tests and documentation update as well.

Cheers,

Malcolm

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Admin broken with large data table

[Malcolm Box]

On Mar 29, 2:48 pm, Jacob Kaplan-Moss  wrote:
> On Tue, Mar 29, 2011 at 5:56 AM, Malcolm Box  wrote:
> > On one of my models, the admin choice_list page is taking minutes to
> > load, which makes it somewhat broken.
>
> > The table has about 2M rows and about 2.6GB in size, on InnoDB/MySQL.
> > As far as I can tell, what's breaking things is the paginator code
> > that is doing a SELECT COUNT(*) which is known to be glacially slow on
> > InnoDB with certain types of table.
>
> Yup, this is a known problem: pagination in the admin isn't efficient
> and breaks down past a certain point.
>

OK, thanks. Is there a ticket tracking the problem, I couldn't spot
one?

> > Is there any way to suppress the pagination and/or change it so that
> > it doesn't do the queryset.count()?
>
> In 1.3 you should be able to override ModelAdmin.get_paginator
> (http://docs.djangoproject.com/en/1.3/ref/contrib/admin/#django.contri...).
> You'll have to subclass django.core.paginator.Paginator and provide an
> interface that doesn't perform COUNTs.
>
> [Yes, this is sorta tricky, and ideally it'd be something Django does
> for you so if you'll share your code when you figure it out I'll try
> to work it into the next release.]

I'll have a look and see what I can come up with. I'll be happy to
share once it's done.

Malcolm

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Admin broken with large data table

[Jacob Kaplan-Moss]

On Tue, Mar 29, 2011 at 5:56 AM, Malcolm Box  wrote:
> On one of my models, the admin choice_list page is taking minutes to
> load, which makes it somewhat broken.
>
> The table has about 2M rows and about 2.6GB in size, on InnoDB/MySQL.
> As far as I can tell, what's breaking things is the paginator code
> that is doing a SELECT COUNT(*) which is known to be glacially slow on
> InnoDB with certain types of table.

Yup, this is a known problem: pagination in the admin isn't efficient
and breaks down past a certain point.

> Is there any way to suppress the pagination and/or change it so that
> it doesn't do the queryset.count()?

In 1.3 you should be able to override ModelAdmin.get_paginator
(http://docs.djangoproject.com/en/1.3/ref/contrib/admin/#django.contrib.admin.ModelAdmin.get_paginator).
You'll have to subclass django.core.paginator.Paginator and provide an
interface that doesn't perform COUNTs.

[Yes, this is sorta tricky, and ideally it'd be something Django does
for you so if you'll share your code when you figure it out I'll try
to work it into the next release.]

Jacob

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Admin broken with large data table

[Malcolm Box]

Hi,

On one of my models, the admin choice_list page is taking minutes to
load, which makes it somewhat broken.

The table has about 2M rows and about 2.6GB in size, on InnoDB/MySQL.
As far as I can tell, what's breaking things is the paginator code
that is doing a SELECT COUNT(*) which is known to be glacially slow on
InnoDB with certain types of table.

Is there any way to suppress the pagination and/or change it so that
it doesn't do the queryset.count()?

I've tried adding a separate index on the primary key as suggested
here: http://forums.mysql.com/read.php?22,90945,91110#msg-91110 which
improves things - from > 10 minutes to 134s - but not enough.

It seems this should bite anyone using admin with large tables on
InnoDB or Postgres, so perhaps this is a bug?

Cheers,

Malcolm

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.