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.
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_prefix` ON (_table_prefix.entity_id = e.entity_id) AND (_table_prefix.attribute_id='585') LEFT JOIN `customer_entity_varchar` AS `_table_firstname` ON (_table_firstname.entity_id = e.entity_id) AND (_table_firstname.attribute_id='4') LEFT JOIN `customer_entity_varchar` AS `_table_middlename` ON (_table_middlename.entity_id = e.entity_id) AND (_table_middlename.attribute_id='586') LEFT JOIN `customer_entity_varchar` AS `_table_lastname` ON (_table_lastname.entity_id = e.entity_id) AND (_table_lastname.attribute_id='5') LEFT JOIN `customer_entity_varchar` AS `_table_suffix` ON (_table_suffix.entity_id = e.entity_id) AND (_table_suffix.attribute_id='587') LEFT JOIN `customer_entity_int` AS `_table_default_billing` ON (_table_default_billing.entity_id = e.entity_id) AND (_table_default_billing.attribute_id='9') LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_postcode` ON (_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_city` ON (_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_telephone` ON (_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_id` ON (_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_id` FROM `customer_entity` AS `e` LEFT JOIN `customer_entity_varchar` AS `_table_prefix` ON (_table_prefix.entity_id = e.entity_id) AND (_table_prefix.attribute_id='585') LEFT JOIN `customer_entity_varchar` AS `_table_firstname` ON (_table_firstname.entity_id = e.entity_id) AND (_table_firstname.attribute_id='4') LEFT JOIN `customer_entity_varchar` AS `_table_middlename` ON (_table_middlename.entity_id = e.entity_id) AND (_table_middlename.attribute_id='586') LEFT JOIN `customer_entity_varchar` AS `_table_lastname` ON (_table_lastname.entity_id = e.entity_id) AND (_table_lastname.attribute_id='5') LEFT JOIN `customer_entity_varchar` AS `_table_suffix` ON (_table_suffix.entity_id = e.entity_id) AND (_table_suffix.attribute_id='587') LEFT JOIN `customer_entity_int` AS `_table_default_billing` ON (_table_default_billing.entity_id = e.entity_id) AND (_table_default_billing.attribute_id='9') LEFT JOIN `customer_address_entity_varchar` AS `_table_billing_postcode` ON (_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_city` ON (_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_telephone` ON (_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_id` ON (_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?
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. :(
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.
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.
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.
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.
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.
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…
Sales: Call 877.832.5289 (N America)|310.295.4144 (International)
