Database Performance And Site Slowness

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.

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

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.

[quote name='JesseLeeStringer' timestamp='1392688333' post='177707']

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

[/quote]



I've seen it suggested that you use mysql or sqlite for backend_cache.

CS-Cart Documentation — CS-Cart 4.15.x documentation



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

[quote name='tbirnseth' timestamp='1392689409' post='177708']

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.

[/quote]



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.

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.

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

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.com/topic/40020-get-maximum-out-of-431-the-best-server-configuration/

and http://blog.cs-cart.com/2015/11/27/pay-5-for-hosting-on-digitalocean-and-get-92-google-pagespeed-points-for-your-cs-cart-store-out-of-the-box/

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