Timeline for I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)
Current License: CC BY-SA 4.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 10, 2022 at 22:41 | vote | accept | adrianTNT | ||
| Sep 6, 2021 at 20:19 | comment | added | Rick James | @adrianTNT - In most production systems, it is better to turn off the Query cache. (That may be the default, depending on what version you are running.) Sort_buffer, etc -- yes, use defaults. | |
| Sep 6, 2021 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, 2021 at 19:05 | comment | added | Rick James | @adrianTNT - I added to my Answer. | |
| Sep 6, 2021 at 19:05 | history | edited | Rick James | CC BY-SA 4.0 |
added 435 characters in body
|
| Sep 6, 2021 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 6, 2021 at 0:48 | comment | added | Rick James | Fragmentation does happen in both the Data and Index BTrees. But it is not worth using the sledgehammer called "Optimize Table". | |
| Sep 5, 2021 at 2:05 | comment | added | adrianTNT |
autocommit was on in variables, so that is what I am using (default). 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, 2021 at 21:38 | history | answered | Rick James | CC BY-SA 4.0 |