Magento Forum

   
Why is my store so slow? Fast server, lots of resources…
 
XPC Design
Member
 
Avatar
Total Posts:  40
Joined:  2008-08-19
 

The store I’ve been setting up for a client has always been really slow. I’ve seen benchmarks up to 10 seconds on first load and roughly 4.5 seconds after that.

phpMyAdmin reports complain about tables being improperly indexed, etc. I understand that the database isn’t the fastest and best designed thing in town at the moment (see below), but I’ve seen other Magento sites that run plenty faster than mine.

I imported all of the products and didn’t enter anything manually. I’m wondering if this is the issue. Maybe the importer is junk and creates a bunch of extra table records and such. Anyone care to elaborate?

The server I’m running on is a dual core 3GHz Xeon machine with plenty ram/hard drive space. We’ve even enabled a php accelerator, enabled gzip and tweaked some mysql config settings for caching and have seen some performance improvements, just not like some of the other stores I’ve seen.

------------------------------------------------------------------------------------------------------------------------------------------------
phpMyAdmin Complaints:
(Note this info is global and there are other clients on the server, but no other Magento sites or any big heavy duty sites like it.)
------------------------------------------------------------------------------------------------------------------------------------------------

Slow_queries: 269
The number of queries that have taken more than long_query_time seconds.

Innodb_buffer_pool_pages_dirty: 3
The number of pages currently dirty.

Innodb_buffer_pool_reads: 1 M
The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.

Handler_read_rnd: 57 M
The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use keys properly.

Handler_read_rnd_next: 3.83 G
The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Slow_launch_threads: 28
The number of threads that have taken more than slow_launch_time seconds to create.

Select_full_join: 150 k
The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Select_range_check: 56
The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)

Table_locks_waited: 6 k
The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

------------------------------------------------------------------------------------------------------------------------------------------------
Probably fixable by me but I have no idea how much of a performance gain it would be:
------------------------------------------------------------------------------------------------------------------------------------------------

Opened_tables: 390 k
The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

Created_tmp_disk_tables: 446 k
The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

Qcache_lowmem_prunes: 2 M
The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.

 
Magento Community Magento Community
Magento Community
Magento Community
 
nikefido
Guru
 
Avatar
Total Posts:  481
Joined:  2008-07-11
New Haven, CT
 

Have you set the sql configuration as per the Blog article which addresses Magento speed issues? SQL queries are the largest offender in terms of speed in Magento. I don’t see those settings mentioned in your post.

http://www.magentocommerce.com/blog/comments/performance-is-key-notes-on-magentos-performance/

I’m not sure about the phpmyadmin showing an indexing problem. I have not seen in any install I have seen.

 
Magento Community Magento Community
Magento Community
Magento Community
 
XPC Design
Member
 
Avatar
Total Posts:  40
Joined:  2008-08-19
 

Good find.. I’ve been through a few articles like that, just not that one specifically. I wrote out my server vars next to their recommendations. Looks like there’s a few settings that need to be changed here.

key_buffer:  268,435,456 (Rec. 512M) ***
max_allowed_packet:  1,048,576 (Rec. 64M) ***
table_cache:  1,024 (Rec. 512)
sort_buffer_size:  16,777,216 (Rec. 4M)
read_buffer_size:  4,194,304 (Rec. 4M)
read_rnd_buffer_size:  262,144 (Rec. 2M)
myisam_sort_buffer_size:8,388,608 (Rec. 64M) ***
tmp_table_size:  33,554,432 (Rec. 128M) ***
query_cache_size:  67,108,864 (Rec. 96M) ***
query_cache_type:  1 (Rec. 1)
thread_cache_size:  0 (Rec. 8) ***
max_connections:  100 (Rec. 400) ***
wait_timeout:  28,800 (Rec. 300)

 
Magento Community Magento Community
Magento Community
Magento Community
 
mrtech
Sr. Member
 
Total Posts:  87
Joined:  2008-06-30
 

hi

can you post the Server os
and do a php -v and post here what you get so we can try to help you

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top