12 events
when toggle format what by license comment
Sep 6 '21 at 21:44 comment added Bill Karwin Yes, or see if an index could be added to optimize the sort. But that's a bit beyond the scope of this question.
Sep 6 '21 at 20:38 comment added Rick James I suggest: IF Sort_merge_passes / Uptime > 0.2 THEN Increase sort_buffer_size or make queries less complex.
Sep 6 '21 at 20:19 comment added Bill Karwin As for sort_buffer_size, this is not easy to answer. It depends on the queries you run, and whether your queries cause sorting without the aid of an index. Also depends on how many rows your query needs to sort. So there is no single answer for all cases. Here's an example of a blog trying to analyze the benefit of changing sort_buffer_size: percona.com/blog/2010/10/25/…
Sep 6 '21 at 20:16 comment added Bill Karwin Set query_cache_size=0 and query_cache_type=0. It's a bad feature, it causes more performance problems than benefits. They have removed the query cache in MySQL 8.0.
Sep 6 '21 at 20:13 comment added adrianTNT One more question: with my case and setting buffer_pool_size to ~48GB, can I rely on default values for things like query_cache_size, query_cache_limit, sort_buffer_size ? Or do I need to tune these too ?
Sep 6 '21 at 17:03 comment added Bill Karwin Thanks for the updated information, but my answer remains the same. Scanning data in RAM is still much better than scanning data on storage, so try to size your buffer pool to hold as much of your frequently-accessed data as possible. This probably does not need to be the whole dataset. Most applications access data in a non-uniform way; a minority of the data serves a majority of the requests (see the Pareto principle).
Sep 6 '21 at 14:45 comment added adrianTNT I turned back the processes and was very wrong about queries per seconds (I updated the question). Each server has 3k-4k queries per second non-stop (short spikes to 12k-14k). Just in case you think this new info requires any update on your answer. Thank you.
Sep 5 '21 at 17:41 comment added adrianTNT Yes, I created a full text index of column link_text
Sep 5 '21 at 2:27 comment added Bill Karwin Have you created a fulltext index? It sounds like you are using a fulltext search without creating a fulltext index.
Sep 5 '21 at 2:08 comment added adrianTNT Regarding slow queries, I don't have specific queries that run slowly, "normal" ones take a few seconds when table is not optimized, this is most important one: SELECT * FROM links WHERE match(link_text) against('blue socks' IN BOOLEAN MODE) LIMIT 2000 (against 35 million records). And I have 9 indexes (link_date, link_text, link_sharding, link_maintenance_date, etc). Tracking many things, maybe here is my problem.
Sep 5 '21 at 2:05 comment added adrianTNT Did both answers say that fragmentation do not happen when using SSD ? Could that be an index fragmentation rather than data fragmentation ? Because I do see faster results after OPTIMIZE table_name and before I run inserts/updates again.
Sep 4 '21 at 23:20 history answered Bill Karwin CC BY-SA 4.0