finding optimum tables placement in 2-tablespace situation

just recently we got another array for out main production database. this means – we will be able to add new tablespace, thus making everything go faster.

in theory – it's nice. but which tables to move to the other?

the basic assumption is simple – index on table should not be on the same tablespace as the table itself. that's easy. but – should we really put all tables on one tablespace, and all indexes on another?

we decided that the important things that should be “boosted" are seeks and writes. sequential reads are (in our situation) more or less irrelevant.

this is example output from my jabber database. it shows that the most seeks (which are related to idx_tup_fetch) are in users table. as for writes – i have to sumammarize inserted, updated and deleted rows, but it's not really complicated:

now, the best option to split all these tables would be to do so in a way that summarized seeks on both tablespaces will be the same (it would mean that we had splitthe seeks load exactly 50/50). same goes for writes.

for example:

let's assume that tablespace “primary" will get tables browse, last and users. while new, secondary tablespace will get rest of them.

as this can be seen, after 1000 iterations it did found a nice layout – with score 1.76, and really close numbers of seeks and writes.

after printing out results, program checks if it didn't reach the target for current check – in this case, target was 1.99. if the target is not reached, it makes next swaps.

if 21000 swaps happened, and the target is still not reached, program starts from scratch – gets new data from database, randomizes layout, and starts random swaps.

when target score is reached – program stops.

since every time new line is printed, program outputs current layout (in form of ready sql script) – after finishing the program i have nice sql which can be used to put the tables to their respective tablespaces (assuming they are named primary and secondary).

simple. yet, doing it “by hand" for 300 tables is not something i would call “fun".

additionally the program can be told not to put 2 tables on the same tablespace. this is useful for example if you have triggers that on every insert/update/delete to table “a", automatically update table “b". in such a case putting them (tables “a" and “b") on separate tablespaces will be a good idea.

now, you can get the program from my GitHub (it is named: find.best.tablespace.split.pl).

as usual there is no manual, and the code is ugly, but it should be easily customizable.

important parts:

database connection info is in get_data_from_db() function (last one in code)

list of which tables should not be on the same tablespace as another is at the very beginning, and is stored in %cant_be_together (hash, dictionary).

sql script with commands to move tables to tablespaces is saved as tablespace.split.out in current directory

if you have any questions about it – feel free to mail or comment on blog 🙂