8 ms·
A few of these tips apply to mysql also. Copy table from a tab delimited file. In mysql you can use load data infile which will do the exact same thing. Index
by chuhnk 15y ago
A few of these tips apply to mysql also.
Copy table from a tab delimited file. In mysql you can use load data infile which will do the exact same thing.
Indexes in memory obviously very important in mysql land too. Also when using joins make sure the columns being joined on are indexed.
Using innodb o_direct will prevent linux from swapping out the mysql process.
Mysql's default in memory table tables are very small which usually results in creating on disk tables, to prevent this increase tmp_table_size and max_heap_table_size. Alternatively you can specify mysql engine memory for in memory tables if you know how big they are going to be.
- spudlyo 15y agoUsing innodb o_direct will prevent linux from swapping out the mysql process. This is close, but not exactly true. Linux is loathe to discard buffer cache (even with swappiness at zero) it will often choose to swap out MySQL code/data instead when faced with downward memory pressure. Using O_DIRECT helps to not swap out MySQL because it doesn't load up the buffer pool with a bunch of already-cached-by-mysql data, but it doesn't prevent it. A big rsync job for example will still load every copied file into the buffer cache and potentially cause swapping. If you want prevent the InnoDB buffer pool (or Oracle SGA) from ever being swapped out use huge pages; they can't be swapped out. I've read that the split-LRU kernel patch (kernels >= 2.6.28) fixes a number of these bad swapping behaviors, but I haven't tried it myself.