Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Database Performance And Site Slowness Rate Topic   - - - - -

 
  • hairsmize
  • Newbie
  • Members
  • Join Date: 25-Sep 13
  • 10 posts

Posted 18 February 2014 - 12:11 AM #1

We've launched our site a little prematurely and have been hit with some nasty performance issues. I am a MS SQL Server professional by trade but have been tasked to tune this MySQL database. As I understand MyISAM databases do not have row level locking and will lock up the entire table if you're editing data. Can someone please confirm or deny my assumptions....

We've spent a lot of money on Google Adwords and have been able to generate some "useless" traffic to the site... but at the same time, we have in house staff loading the site with new products throughout the day. In the evening when no one is inputting anything, I've seen the site load around 2-3 seconds.. but when there are people inputting, the load time drops to 8-9... sometimes 10+ seconds to load.

I've thought about changing the tables to InnoDB for a few months until we can get all our products added to the cart.. then switch back. Is this advisable?

Also, recently switched our cs-cart caching from "file" to "mysql".. this table, presumably, is also MyISAM.... can this impact performance as well since by nature you'll be writing to this table frequently.

 

Posted 18 February 2014 - 01:52 AM #2

If you don't have enough computing power for mysql, you are going to have a bad time.
It's suggested the cache method be file, but DB connection via mysqli
I've moved on from CS-Cart to WooC******** - If you need anything I can be of little help.

 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 11405 posts

Posted 18 February 2014 - 02:10 AM #3

Make sure you're using mySQLi. Check on percona.com and enter your config details related to your server. It will give you a mysql config to use. You can use innodb without issue. Suggest it for cscart_orders, cscart_order_details, cscart_user_sessions and (depending on version you are running) any of the statistics tables.

My experience is biggest issue is memory since cs-cart uses a lot of ajax requests which simply doubles and triples the connection rate through apache. So the more memory you can get the better and allocate at least 25% of it to mySQL.

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • hairsmize
  • Newbie
  • Members
  • Join Date: 25-Sep 13
  • 10 posts

Posted 18 February 2014 - 04:47 AM #4

If you don't have enough computing power for mysql, you are going to have a bad time.
It's suggested the cache method be file, but DB connection via mysqli


I've seen it suggested that you use mysql or sqlite for backend_cache.
http://kb.cs-cart.co...ing-performance

Is this not accurate? Can you share your source? Thanks for replying.

 
  • hairsmize
  • Newbie
  • Members
  • Join Date: 25-Sep 13
  • 10 posts

Posted 18 February 2014 - 04:49 AM #5

Make sure you're using mySQLi. Check on percona.com and enter your config details related to your server. It will give you a mysql config to use. You can use innodb without issue. Suggest it for cscart_orders, cscart_order_details, cscart_user_sessions and (depending on version you are running) any of the statistics tables.

My experience is biggest issue is memory since cs-cart uses a lot of ajax requests which simply doubles and triples the connection rate through apache. So the more memory you can get the better and allocate at least 25% of it to mySQL.


Looks like our current key_buffer_size is 8384512. Which I believe is way to low. I'm in the process of getting this updated. We are on a VPS with 4GB of system memory. Trying to set it to the suggested 1GB.

 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 11405 posts

Posted 18 February 2014 - 05:15 AM #6

Work with your hosting provider. There are a lot of mysql parameters that can affect performance. Check out percona.com. Seriously, they do this for a living and where I've used their configs, I rarely have DB performance issues.

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • FDGWEB
  • Junior Member
  • Authorized Reseller
  • Join Date: 20-Aug 10
  • 145 posts

Posted 22 February 2016 - 07:55 PM #7

Here's our my.cnf on a 24GB server: innodb_buffer_pool_size = 1000M is the value that brings the most improvement if your tables are using InnoDB. We've been switching most older systems over to use it.

 

[mysqld]

default-storage-engine=MyISAM
innodb_file_per_table=1
key_buffer_size = 64M
innodb_log_file_size = 128M
innodb_buffer_pool_size = 1000M
max_allowed_packet=268435456
open_files_limit=50000
max_connections = 200
table_open_cache = 3184
myisam_sort_buffer_size = 64M
query_cache_size= 32M
max_heap_table_size = 48M
tmp_table_size = 48M
thread_cache_size = 8
wait_timeout = 30
interactive_timeout = 300
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
 
[isamchk]
key_buffer = 96M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer = 96M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

FDG Web, Inc - Seattle Web Design : Custom CS-Cart Programming & Design | Toll-Free: 877.239.3083

Download Proposal Templates & Web Design Contract Samples

 
  • imac
  • Head of Product
  • CS-Cart Architects
  • Join Date: 22-Nov 05
  • 2074 posts

Posted 24 February 2016 - 07:11 AM #8

We've launched our site a little prematurely and have been hit with some nasty performance issues. I am a MS SQL Server professional by trade but have been tasked to tune this MySQL database. As I understand MyISAM databases do not have row level locking and will lock up the entire table if you're editing data. Can someone please confirm or deny my assumptions....

We've spent a lot of money on Google Adwords and have been able to generate some "useless" traffic to the site... but at the same time, we have in house staff loading the site with new products throughout the day. In the evening when no one is inputting anything, I've seen the site load around 2-3 seconds.. but when there are people inputting, the load time drops to 8-9... sometimes 10+ seconds to load.

I've thought about changing the tables to InnoDB for a few months until we can get all our products added to the cart.. then switch back. Is this advisable?

Also, recently switched our cs-cart caching from "file" to "mysql".. this table, presumably, is also MyISAM.... can this impact performance as well since by nature you'll be writing to this table frequently.

Probably you start with reading

http://forum.cs-cart...-configuration/

and http://blog.cs-cart....out-of-the-box/

to get the general idea on required resources and best configuration.


Ilya Makarov,
CS-Cart Architect Team
Suggest and vote for new features | Report a bug