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]
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/
to get the general idea on required resources and best configuration.