Jump to content

 

VerDan

Member Since 13 Nov 2018
Offline Last Active Yesterday, 08:57 PM
-----

Posts I've Made

In Topic: Admin Logs - Lock_Keys Lot Of Duplicates

30 April 2019 - 01:33 PM

Thank You !    I  posted what I "believe" is a better solution/addition to what was provided in your link.   Question I don't know since I don't really know the deep level guts of the code is if it is a negative change.

 

Hopefully others will add in -- especially the cs-cart team.


In Topic: Best Server Configuration For Cs-Cart V4.7.x

15 February 2019 - 08:47 PM

 

If you really desire pure performance I would recommend ditching MariaDB and installing the latest general release of MySQL 8 along with the innodb memcached extension. We tested it on our platforms and were able to load categories (in a store with 50.000 products) within the 100ms mark reliably.

 

 

 

I am looking to squeeze every ounce of performance out of the current architecture and platform.    The newest version of MySQL 8 has got some of my attention; however, i really believe that the ultimate goal of Oracle is to position MySQL such that MariaDB is no longer an "easy" drop in replacement.   I know the MariaDB team will work to get compatible with the latest MySQL but will be curious when they will ultimately throw in the towel on this -- so I'm starting to get very deliberate in my considerations of the long term database.

 

Ultimately, it would be good to see the platform back out of any proprietary usage such that the system can then easily support other database engines.

 

But, i really want to see how that memcached extension helps out.


In Topic: Bad Query On Admin Page

14 February 2019 - 08:11 PM

The memory item is interesting -- from what I can tell the database is able to load up into memory with the only time i actually see it do anything with disk is some of the sorting -- been doing what I can to tune that out (not a dba either).    But, watching things indicates it is just heavily compute intensive as it walks the tables.

 

500K products was not a big issue for us, it is when we jumped over 1M, that with many of the products having 5-10 features or more, plus about 8K categories... things started popping up.

 

I want to take advantage of the Serverless infrastructure due to the "scaling", replication, fault tolerance, etc -- just really could be a nice option.   Then i would just need to hopefully focus on the scaling of the app server.

 

Right now the majority of the work in the database is browsing, not a lot of writes going on.   So, i'm really focused on the read performance.   I really believe it should be much better than it is.    I'm capturing the slow queries and reconstructing and doing many other things to work out EXPLAIN plans -- just a lot of data.     Coming up with a good partitioning strategy is actually harder than it should be with this dataset, but, that is on the plan.

 

So, i also did change out the SQL_CALC_FOUND_ROWS for the one main offender... still took AWS Serverless 1 hour and 22 minutes to return the count (just over 3.6M was the count value).   11 seconds on MariaDB and 22 seconds on MySQL.    So, i have a forum note up on AWS and will probably pay for support to get some resolution there.   Other option is the full RDS implementation.


In Topic: Bad Query On Admin Page

14 February 2019 - 02:08 PM

Back to the original issue -- what prompted this was an attempted run on AWS using a Serverless setup for the database in preparation for a potential migration to that platform.   Making use of the Aurora database which is essentially at a Version 5.6 for MySQL, the query essentially hangs.   I decided to let it run its course since the CPU was running.   Ultimately, it took just over an HOUR to return all of the "1"s.    Running on the database on a standard AWS instance, the query returns in 23-26 seconds -- Still WAY too long.   We suffer that query every time we go to the main Admin landing page.

 

But, the problem with the query and the AWS Serverless environment makes the whole thing NOT usable.

 

For those that have not looked into Serverless, it really does provide some nice options -- we just need to get the application to actually make good use of it.


In Topic: Bad Query On Admin Page

14 February 2019 - 12:11 PM

We have built up our product base using many vendors - and this has not always been easy as well.

 

I know we can ultimately get almost any system to perform with millioins of records, i've done that before in years gone by.  The issue we have is working to stay in the boundaries of the main cart platform without the need to completely diverge from the baseline.   We are slowly having custom add-ons built and doing other things with configuration and layouts that reduce some of the "extra" features that cause tremendous drag on the system when there are a large number of products, prices, categories, and features.

 

Right now i'm putting some focus on partitioning to determine if there is an opportunity to increase performance here.   Biggest issue we have when you start reviewing explain plans for the queries is the need to continuously scan over the millions of records, throw in the joins and things can get really large.... but, got sidetracked on this response...posting a response regardless.  :)