Website is running slow

[abhijeetkumar]

Hi,

 

We have designed an e-commerce website www.game4u.com using Prestashop. Website goes slow when total number of users increase. We have hosted it on cloud server having quad core processor and 8 GB ram. 

 

Any idea why prestashop is going slow when no of users is increasing. I have already allocated enough buffer for mysql. Mysql takes lot of memory when no of users increase. Is the my.cnf used below rightly configured? Queries at cart are taking long time to execute. We have 16152 records in ps_cart, 25121 in ps_customer, 33545 in ps_customer_group, 267153 records in ps_guest and 3586 records in ps_product tables. Admin panel is working fine but the website is running slow. 

 

What should we do to increase the query speed? Can we empty the table ps_guest if its not being used anywhere except analytics? Will that effect the performance?

 

Content of my.cnf file is included below. 

 

Query cache is enabled. Slow query log is also enabled and set for 10 seconds. Innodb buffer is also enabled. 

 

On an average, total threads connected are about 50-100 at any point of time. Total httpd connections opened at any point of time are in between 20-100 (or more)

 

_______________________________

 

[mysqld]

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

 

# Disabling symbolic-links is recommended to prevent assorted security risks;

# to do so, uncomment this line:

symbolic-links=0

 

innodb_buffer_pool_size=1024M

innodb_additional_mem_pool_size=500K

innodb_log_buffer_size=500K

innodb_thread_concurrency=2

innodb_lock_wait_timeout=30

 

key_buffer_size=256M

read_buffer_size=16M

sort_buffer_size=16M

join_buffer_size=16M

 

query_cache_type=1

query_cache_size=1024M

query_cache_limit=1M

 

log=/var/log/mysql.log

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

log-slow-queries=/var/log/mysqlslowqueries.log

long_query_time=10

 

max_connections=1000

max_connect_errors=10

max_user_connections=1000

 

interactive_timeout=60

wait_timeout=30

connect_timeout=10

delayed_insert_timeout=20

 

max_allowed_packet=10M

tmp_table_size=128M

 

table_cache=256

thread_cache_size=300

 

[mysqldump]

max_allowed_packet=200M

_________________________________________

 

 

Output of top command is attached. 

 

Please let me know if you need any more details.

 

Regards,

Abhijeet

 

[abhijeetkumar]

Below mentioned queries are taking the longest time and making all queries slow. Any idea where are these queries used??

 

SELECT cp.`id_product`

FROM `ps_category_group` cg

INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`

INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`

INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`

LEFT JOIN ps_product_shop product_shop

ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)

WHERE c.`active` = 1

AND product_shop.`active` = 1

AND product_shop.`visibility` IN ("both", "search")

AND product_shop.indexed = 1

 

AND cg.`id_group` IN (

SELECT id_group FROM ps_customer_group

WHERE id_customer = 34778

);

 

Above query fetches records from ps_customer_group and is slowing down the all other queries.

 

SELECT c.`id_parent`,cp.`id_product_attribute`, cp.`id_product`,p.id_category_default as prod_default_cat_id,temp_cl.name as root_cat_name, cp.`quantity` AS cart_quantity,cp.price_type,cp.price as type_price,cp.id_shop, pl.`name`, p.`is_virtual`,p.`freebie_title`,p.`a_cash`,cp.freebie_item,cp.freebie_text,

pl.`description_short`, pl.`available_now`, pl.`available_later`, p.`id_product`, p.`id_category_default`, p.`id_supplier`,cp.`power_ship`,

p.`id_manufacturer`, p.`on_sale`, p.`ecotax`, p.`additional_shipping_cost`, p.`available_for_order`, p.`price`, p.`weight`,

stock.`quantity` quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`active`, p.`freebie_title`, p.`date_add`,pss.`reduction` reductionamount,pss.`reduction_type` reductiontype,p.`price` pricefordiscount,

p.`date_upd`, t.`id_tax`, tl.`name` AS tax, t.`rate`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category,

CONCAT(cp.`id_product`, cp.`id_product_attribute`, cp.`id_address_delivery`) AS unique_id, cp.id_address_delivery,p.`ztorm_item_code`,p.ppr_value,p.ppr_percentage,p.ppr_currency,p.rrp_value,p.rrp_currency,p.exchange_rate,

p.`wholesale_price`, p.advanced_stock_management,0 AS customization_quantity, 0 AS id_customization,product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr,

IF (IFNULL(pa.`reference`, '') = '', p.`reference`, pa.`reference`) AS reference,

IF (IFNULL(pa.`supplier_reference`, '') = '', p.`supplier_reference`, pa.`supplier_reference`) AS supplier_reference,

(p.`weight`+ pa.`weight`) weight_attribute,

IF (IFNULL(pa.`ean13`, '') = '', p.`ean13`, pa.`ean13`) AS ean13,

IF (IFNULL(pa.`upc`, '') = '', p.`upc`, pa.`upc`) AS upc,

pai.`id_image` as pai_id_image, il.`legend` as pai_legend,

IFNULL(product_attribute_shop.`minimal_quantity`, p.`minimal_quantity`) as minimal_quantity

 

FROM `ps_cart_product` cp

LEFT JOIN `ps_product` `p` ON p.`id_product` = cp.`id_product`

LEFT JOIN `ps_specific_price` `pss` ON pss.`id_product` = p.`id_product`

LEFT JOIN `ps_product_lang` `pl` ON 

p.`id_product` = pl.`id_product`

AND pl.`id_lang` = 1 AND pl.id_shop = 1 

LEFT JOIN `ps_tax_rule` `tr` ON 

p.`id_tax_rules_group` = tr.`id_tax_rules_group`

AND tr.`id_country` = 110

AND tr.`id_state` = 0

AND tr.`zipcode_from` = 0

LEFT JOIN `ps_tax` `t` ON t.`id_tax` = tr.`id_tax`

LEFT JOIN `ps_tax_lang` `tl` ON 

t.`id_tax` = tl.`id_tax`

AND tl.`id_lang` = 1

LEFT JOIN `ps_category_lang` `temp_cl` ON 

p.`id_category_default` = temp_cl.`id_category` 

AND temp_cl.`id_lang` = 1 AND temp_cl.id_shop = 1 

LEFT JOIN `ps_category_lang` `cl` ON 

p.`id_category_default` = cl.`id_category`

AND cl.`id_lang` = 1 AND cl.id_shop = 1 

LEFT JOIN `ps_category` `c` ON 

p.`id_category_default` = c.`id_category`

 LEFT 

JOIN ps_stock_available stock

ON (stock.id_product = cp.id_product AND stock.id_product_attribute = IFNULL(`cp`.id_product_attribute, 0) AND stock.id_shop = 1  )

LEFT JOIN `ps_product_attribute` `pa` ON pa.`id_product_attribute` = cp.`id_product_attribute`

LEFT JOIN `ps_product_attribute_shop` `product_attribute_shop` ON product_attribute_shop.id_shop=1

LEFT JOIN `ps_product_attribute_image` `pai` ON pai.`id_product_attribute` = pa.`id_product_attribute`

LEFT JOIN `ps_image_lang` `il` ON il.id_image = pai.id_image AND il.id_lang = 1

WHERE (cp.`id_cart` = 7790) AND (p.`id_product` IS NOT NULL)

GROUP BY unique_id

ORDER BY p.id_product, cp.id_product_attribute, cp.date_add ASC;

 

Above query fetches records from ps_products where we have about 4000 records.

[Optimus_Sam]

try a vps