OroCommerce Forums

Covering OroCommerce topics, including community updates and company announcements.

Forums Forums OroCommerce MySQL Optimizations for large product library (BNL/buffers)

This topic contains 2 replies, has 2 voices, and was last updated by Andrey Yatsenko Andrey Yatsenko 1 year, 8 months ago.

Starting from March 1, 2020 the forum has been switched to the read-only mode. Please head to StackOverflow for support.

  • Creator
  • #37794



    So I have had a lot of success getting a large product library and category imported (100k+) sucessfully and am now attempting to tweak Mysql/innodb to perform as best as possible..

    I’ve attempted the wide swath of changes in my.cnf (all the buffer_size and buffer_pool tweaks) [forum post ] settings as well as changing the BlockNestedLoop optimization on and off. [devguide link] But none of these actually increase the time mysql takes to perform the initial SELECT DISTINCT with the LEFT JOINs …

    Somewhere along the lines I was able to achieve a giant gain (100% / half the processing time)but have since lost this upon further config changes (I know, poor form on my part)…

    After further inspection I think it’s all about ‘ORDER BY’ and sort index buffers/management …
    If ORDER BY clause is removed the query drops tremendously (obviously) … I’ve tweaked sort_buffer_size (doesn’t help) and read_rnd_buffer_size with no major impact but max_sort_length has to be accomdated so I increased this to a large number as well (greater than the numbers of products (100k+)).. These have no helped much though…

    Same thing is kind of happening on the GROUP BY queries also, been reading the MySQL performance / Percona founders blog from earlier this year about it.. [percona blog link]

    If anybody has some good suggestions or experience with this large number of products, what config tweaks have helped?

    WITH ORDER BY 25 rows in set (8.29 sec)
    WITHOUT ORDER BY 25 rows in set (0.27 sec)

Viewing 2 replies - 1 through 2 (of 2 total)
Viewing 2 replies - 1 through 2 (of 2 total)

The forum ‘OroCommerce’ is closed to new topics and replies.

You will be redirected to [title]. Would you like to continue?

Yes No