Tweaking MySql for Patstat

When dealing with Patstat under Mysql you should expect some slowdown to queries and table accessions due to the huge number of records of some tables.

So someway we should be more careful in the setup of Mysql DB engine and in table design ...

Let's start from one MYsql environment setup that is Buffers' size.

From Mysql administrator choose STARTUP VARIABLES, then in GENERAL PARAMETERS you will find the MEMORY USAGE slot.
(Or if you preferr edit the file MY.INI under programs\MYSQL\MySql server 5.1\)

Buffer size helps to execute operations in memory instead of using disk; performance may differ of 100-1.000 times depending from OS, table structure and so on.

On the other hands increasing buffer size will decrease preformance for other concurrent applications (If you, however, make this too big your system may start to page and become extremely slow....)

KEY buffer of 512 Mb and Sort Buffer of 1 Mb may be a good compromise (but I use a double amount for both when building some heavy tables like citations).

Also monitorying memory health status (MYSQL ADMINISTRATOR --> HEALTH --> MEMORY HEALTH)  may be a good issue for more precise setting of buffers.

Some more issues I'll discuss more in details in some next posts are about type of DB engine (InnoDB vs MyIsam) and type of indexes (HASH vs BTREE).

One last thing: when making some joins where one of the tables is very small, it may be helpful to load it into memory:

CREATE TABLE new_table ENGINE=MEMORY AS SELECT * FROM small_table;
Then recreate the index (using HASH) and make the join...