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)
You must be logged in to reply to this topic.