Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
Manage Customers admin page extremely slow to load
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

I currently have magento 1.1.3 installed on a dedicated server and the manage customers admin page takes 15+ seconds to load and every time I want to do a process like edit or create a new customer it is about 10 seconds to load. I have 48,000 customers in the database.

I have made some of the modifications mentioned in this thread http://www.magentocommerce.com/boards/viewthread/4168/.

I’ve also doubled our ram on our dedicated server.

I imported all the customers from os commerce. Anyone else have this issue or some suggestions to increase the manage customer page?

All the other admin and front end pages seem to work relatively good.

Thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

is any one else having this issue or is this only related to my install?

 
Magento Community Magento Community
Magento Community
Magento Community
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

so here is the query that is really slow. when loading the customer page in the admin section

ime                 Id Command    Argument
# Time: 080829  0:01:02
# User@Host: #########[########] @ localhost []
# Query_time: 9  Lock_time: 0  Rows_sent: 1  Rows_examined: 3160882
use magento;
select count(*) from `customer_entity` AS `e`
 
LEFT JOIN `customer_entity_varchar` AS `_table_prefixON
(_table_prefix.entity_id e.entity_id) AND
(
_table_prefix.attribute_id='585')
 
LEFT JOIN `customer_entity_varchar` AS `_table_firstnameON
(_table_firstname.entity_id e.entity_id) AND
(
_table_firstname.attribute_id='4')
 
LEFT JOIN `customer_entity_varchar` AS `_table_middlenameON
(_table_middlename.entity_id e.entity_id) AND
(
_table_middlename.attribute_id='586')
 
LEFT JOIN `customer_entity_varchar` AS `_table_lastnameON
(_table_lastname.entity_id e.entity_id) AND
(
_table_lastname.attribute_id='5')
 
LEFT JOIN `customer_entity_varchar` AS `_table_suffixON
(_table_suffix.entity_id e.entity_id) AND
(
_table_suffix.attribute_id='587')
 
LEFT JOIN `customer_entity_int` AS `_table_default_billingON
(_table_default_billing.entity_id e.entity_id) AND
(
_table_default_billing.attribute_id='9')
 
LEFT JOIN `customer_address_entity_varchar` AS
`
_table_billing_postcodeON (_table_billing_postcode.entity_id =
_table_default_billing.value) AND
(
_table_billing_postcode.attribute_id='19')
 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_cityON
(_table_billing_city.entity_id _table_default_billing.value) AND
(
_table_billing_city.attribute_id='15')
 
LEFT JOIN `customer_address_entity_varchar` AS
`
_table_billing_telephoneON (_table_billing_telephone.entity_id =
_table_default_billing.value) AND
(
_table_billing_telephone.attribute_id='20')
 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_regione`
ON (_table_billing_regione.entity_id _table_default_billing.value) AND
(
_table_billing_regione.attribute_id='17')
 
LEFT JOIN `customer_address_entity_varchar` AS
`
_table_billing_country_idON (_table_billing_country_id.entity_id =
_table_default_billing.value) AND
(
_table_billing_country_id.attribute_id='16'WHERE (e.entity_type_id =
'1');

Here it is again loading all the data

# Time: 080829  0:01:14
# User@Host: ######### @ localhost []
# Query_time: 10  Lock_time: 0  Rows_sent: 46484  Rows_examined: 3160882
SELECT `e`.`entity_idFROM `customer_entity` AS `e`
 
LEFT JOIN `customer_entity_varchar` AS `_table_prefixON
(_table_prefix.entity_id e.entity_id) AND
(
_table_prefix.attribute_id='585')
 
LEFT JOIN `customer_entity_varchar` AS `_table_firstnameON
(_table_firstname.entity_id e.entity_id) AND
(
_table_firstname.attribute_id='4')
 
LEFT JOIN `customer_entity_varchar` AS `_table_middlenameON
(_table_middlename.entity_id e.entity_id) AND
(
_table_middlename.attribute_id='586')
 
LEFT JOIN `customer_entity_varchar` AS `_table_lastnameON
(_table_lastname.entity_id e.entity_id) AND
(
_table_lastname.attribute_id='5')
 
LEFT JOIN `customer_entity_varchar` AS `_table_suffixON
(_table_suffix.entity_id e.entity_id) AND
(
_table_suffix.attribute_id='587')
 
LEFT JOIN `customer_entity_int` AS `_table_default_billingON
(_table_default_billing.entity_id e.entity_id) AND
(
_table_default_billing.attribute_id='9')
 
LEFT JOIN `customer_address_entity_varchar` AS
`
_table_billing_postcodeON (_table_billing_postcode.entity_id =
_table_default_billing.value) AND
(
_table_billing_postcode.attribute_id='19')
 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_cityON
(_table_billing_city.entity_id _table_default_billing.value) AND
(
_table_billing_city.attribute_id='15')
 
LEFT JOIN `customer_address_entity_varchar` AS
`
_table_billing_telephoneON (_table_billing_telephone.entity_id =
_table_default_billing.value) AND
(
_table_billing_telephone.attribute_id='20')
 
LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_regione`
ON (_table_billing_regione.entity_id _table_default_billing.value) AND
(
_table_billing_regione.attribute_id='17')
 
LEFT JOIN `customer_address_entity_varchar` AS
`
_table_billing_country_idON (_table_billing_country_id.entity_id =
_table_default_billing.value) AND
(
_table_billing_country_id.attribute_id='16'WHERE (e.entity_type_id =
'1');

as you can see it is taking 9 -10 seconds to run this query every time it is called.

So it looks like there are over 3 million rows that it is searching and returning 46000+ customers

Why are there 3 million rows for only 46000 customers? Is that correct?? or am I reading it wrong?

Any thoughts?

 
Magento Community Magento Community
Magento Community
Magento Community
 
rvelton
Sr. Member
 
Total Posts:  82
Joined:  2007-09-07
 

I have some of the same issues and this site has no customers and maybe 75 products. Admin is PAINFULLY slow. I have posted about this before with no response and no help.  :(

 
Magento Community Magento Community
Magento Community
Magento Community
 
SimpleHelixcom
Enthusiast
 
Avatar
Total Posts:  906
Joined:  2007-08-31
Huntsville, AL
 

perhaps i can help out a bit. just pm me and i’ll see what i can do.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mikej165
Sr. Member
 
Total Posts:  89
Joined:  2008-07-31
 

I’ve seen this sort of thing before - not under Magento, but on other systems. The solution is usually to add an index so that table scans are no longer necessary.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

Good idea mikej165.

I’m not sure how to do this at all. anyone have any mysql experience that could recommend how to do this to get this query a little faster

 
Magento Community Magento Community
Magento Community
Magento Community
 
mikej165
Sr. Member
 
Total Posts:  89
Joined:  2008-07-31
 

I’m sorry to be so brief. I can only give generalities here, since I haven’t looked at the server in question and have only a basic familiarity with the table structures of Magento. That said, you want to begin by using the EXPLAIN directive to get a better picture on what is going on; i.e. which column(s) are slowing the query; from there, using CREATE INDEX to add the new index.

I’ve seen databases slowed to a crawl from a query suddenly come back to life just by adding a simple index. It usually happens when the table size goes beyond that anticipated by the designers. When that happens, mysql has to scan the entire table to find the rows it needs, which means it has to read through the whole thing on disk. The larger the table, the bigger the penalty.

 
Magento Community Magento Community
Magento Community
Magento Community
 
creative_dave
Jr. Member
 
Total Posts:  11
Joined:  2008-09-22
 

Anybody found a way to speed it up yet?

Mine is incredibly slow too. It’s extremely annoying, especially when you just want to make a “quick” change to test something!!!

 
Magento Community Magento Community
Magento Community
Magento Community
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

I am also still looking for someone that either has figured this our or would like to get paid to do this. Please PM if you have experience optimizing mysql and more specifically Magento.

Thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
mikej165
Sr. Member
 
Total Posts:  89
Joined:  2008-07-31
 

Have you updated to more recent version of Magento? I updated to 1.1.6 and have seen some fairly solid gains in performance, including the admin interface. Presumably part of the update was to add indices to the tables, which would account for some of the speedup, but of course I have no way of knowing for certain.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

I have upgraded to 1.1.6 and although some things are a little faster managing customers is still very slow and painful

 
Magento Community Magento Community
Magento Community
Magento Community
 
mikej165
Sr. Member
 
Total Posts:  89
Joined:  2008-07-31
 

Are you running your site on a shared hosting account? In my experience this isn’t going to work very well, and usually because the database is far too loaded to properly cache your data, which means disk I/O for most queries. If you can go with a VPS you’ll be much better off.

I’ve seen installation running on shared webhosting and those running on VPS and even a dedicated server. Of course, the dedicated server and even a reasonably configured VPS will blow away the shared hosting. The difference is really night and day.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

I am running on a dedicated server with 4 GB of ram. I’ve tweaked our database to run faster and still no luck. when it queries the manage customer page it appears that it runs through 49,000 customer records and over 3 million rows of data…

 
Magento Community Magento Community
Magento Community
Magento Community
 
Georg Ringer
Member
 
Avatar
Total Posts:  63
Joined:  2008-07-04
Linz, Austria
 

Hi,

do you got some updates?
I am experiencing the same issue with 55k customers :/

 
Magento Community Magento Community
Magento Community
Magento Community
 
DDMAN
Sr. Member
 
Total Posts:  179
Joined:  2008-01-08
 

Georg,

I’m still running slow in the admin area due to the large amount of customers. I doubled the CPU to a dual - dual core processors and it helped a little. It appears that because the query is poorly written for customers when you query the customers it maxes out the CPU on the server.

I have been told by Magento support that there are some significant changes coming for backend admin in the 1.2 release.

I do not know for sure if this will be addressed or not

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2