Timeline for Which MySQL server parameters should be adjusted to improve INSERT performance for a large MyISAM table?
Current License: CC BY-SA 3.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 27, 2017 at 21:06 | vote | accept | mstrap | ||
| Mar 24, 2017 at 14:21 | history | edited | mstrap | CC BY-SA 3.0 |
additional details on further experiments
|
| Mar 24, 2017 at 10:40 | history | edited | mstrap | CC BY-SA 3.0 |
details on further experiments
|
| Mar 24, 2017 at 8:18 | comment | added | mstrap |
It's a standard Ubuntu Software RAID (mdadm) with RAID level 1. I agree that SSDs are expected to be faster, still I think the server database is not tuned well.
|
|
| Mar 24, 2017 at 4:21 | comment | added | Rick James | Is that "software raid"? RAID-0/1/5/10/what? SSDs are likely to be a lot faster; isn't this the answer? | |
| Mar 23, 2017 at 16:38 | answer | added | RolandoMySQLDBA | timeline score: 1 | |
| Mar 23, 2017 at 15:34 | history | edited | mstrap | CC BY-SA 3.0 |
deleted 48 characters in body
|
| Mar 23, 2017 at 8:27 | comment | added | mstrap | CPU and RAM are similar, on the test machine I have an SSD, on the server it's a HDD (Software) RAID. Both tables have almost the same content (just on the server every 10 minutes roughly 1000 rows are added, so a bit more rows there). I've updated the question with fragmentation details (am I correct, that the table isn't fragmented at all?) Thanks for pointing to the redundant index. | |
| Mar 23, 2017 at 8:23 | history | edited | mstrap | CC BY-SA 3.0 |
Added information on fragmentation
|
| Mar 22, 2017 at 22:39 | answer | added | Rick James | timeline score: 2 | |
| Mar 22, 2017 at 19:01 | comment | added | RMathis | Do both environments have similar capabilities (cpu, ram, swap space, disk speed, etc...). Have you checked for differences in the my.cnf files? If any, have you tried the windows settings in Ubuntu? Does the windows table have the same rows as the Ubuntu table (it is much more efficient to insert into an empty index than one with 20M rows). Is the Ubuntu table fragmented? It might be time to rebuild it. One last thing, the id1 column is covered by two indexes (id1 & id1_2) - the id1_2 index is not needed and could be dropped. | |
| Mar 22, 2017 at 16:58 | review | First posts | |||
| Mar 22, 2017 at 21:03 | |||||
| Mar 22, 2017 at 16:54 | history | asked | mstrap | CC BY-SA 3.0 |