I'm guessing that you are actually locking on the cscart_orders table since it is JOINed when updates occur to cscart_shipments. And cscart_shipments are usually joined when order details are captured.
When you say 200 users, where is that stat coming from? What is your server configuration?
You might want to consider changing they type of the cscart_orders table to InnoDB which will do record locking versus table locking. The down side is that while it may help with locked tables blocking processing, it will somewhat slow your SELECT queries because each row retrieved must be check and possibly wait for the lock.
If your 200 users is actually 200 active users on your site, then you probably want to spend the $$ to hire a professional DBA who can really assess the details of your DB usage and recommend configuration changes that might help. If you're running 200 concurrent users on a single server, then you might want to re-evaluate your server architecture.
Thank you for your reply. But real time visitor number isn't important. Because website has full http cache.For example website has 50 real time visitors. when 2 personnels quickly have a look at order details page on admin panel and click new shipment button, sql locks itself again. and then MySQLProcesses.txt list we added in previous message is created. But when we don't do any process on admin panel not even 500-1000 real time visitors become problem.
Hello,
You could change all tables in CS-Cart to 'InnoDB'. It will eat up more memory but the locking mechanism is way more efficient and your chance for data corruption will also drop drastically. You could generate the queries for altering those tables the following way:
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'mydatabase';
For the change in server architecture I'd recommend you to take a look at Amazon hosting. We can provide such services and have multiple certified advanced architects. You may contact us here: info@poppedweb.com
Kind regards,
Thank you for your reply. But real time visitor number isn't important. Because website has full http cache.For example website has 50 real time visitors. when 2 personnels quickly have a look at order details page on admin panel and click new shipment button, sql locks itself again. and then MySQLProcesses.txt list we added in previous message is created. But when we don't do any process on admin panel not even 500-1000 real time visitors become problem.
There are many things that have nothing to do with http (and any page caching) that will impact your server performance and are a function of number of simultaneous users. There are very few real "static" pages in cs-cart due to catalog promotions, usergroups, bulk pricing, etc. Even if these things are not active, they can have a performance impact because they are always evaluated.