Optimising Tips for MySQL Databases running on large systems

Optimizing OS
* No swap; If you have memory problems, add more RAM instead or configure your system to use less memory.
* Don't use NFS disks for data (you will have problems with NFS locking).
* Increase number of open files for system and for the SQL server. (add ulimit -n # in the safe_mysqld script).
* Increase the number of processes and threads for the system.
* If you have relatively few big tables, tell your file system to not break up the file on different cylinders (Solaris).
* Use file systems that support big files (Solaris).
* Choose which file system to use; Reiserfs on Linux is very fast for open, read and write. File checks take just a couple of seconds.



Optimizing disks

Have one dedicated disk for the system, programs and for temporary files. If you do very many changes, put the update logs and transactions logs on dedicated disks.
Low seek time is important for the database disk; For big tables you can estimate that you will need: log(row_count) / log(index_block_length/3*2/(key_length + data_ptr_length))+1 seeks to find a row. For a table with 500,000 rows indexing a medium int: log(500,000)/log(1024/3*2/(3+4)) +1 = 4 seeks The above index would require: 500,000 * 7 * 3/2 = 5.2M. In real life, most of the blocks will be buffered, so probably only 1-2 seeks are needed.
For writes you will need (as above) 4 seek requests, however, to find where to place the new key, and normally 2 seeks to update the index and write the row.
For REALLY big databases, your application will be bound by the speed of your disk seeks, which increase by N log N as you get more data.
Split databases and tables over different disks. In MySQL you can use symbolic links for this.
Striping disks (RAID 0) will increase both read and write throughput.
Striping with mirroring (RAID 0+1) will give you safety and increase the read speed. Write speed will be slightly lower.
Don't use mirroring or RAID (except RAID 0) on the disk for temporary files or for data that can be easily re-generated..
On Linux use hdparm -m16 -d1 on the disks on boot to enable reading/writing of multiple sectors at a time, and DMA. This may increase the response time by 5-50 %.
On Linux, mount the disks with async (default) and noatime.
For some specific application, one may want to have a ram disk for some very specific tables, but normally this is not needed.

Choosing API
PERL
Portable programs between OS and databases
Good for quick prototyping
One should use the DBI/DBD interface
PHP
Simpler to learn than PERL.
Uses less resources than PERL, which makes it good for embedding in Web servers.
One can get more speed by upgrading to PHP4.
C
The native interface to MySQL.
Faster and gives more control
Lower level, so you have to work more.
C++
Higher level gives you more time to code your application.
Is still in development.
ODBC
Works on Windows and Unix
Almost portable between different SQL servers.
Slow; MyODBC, which is a simple pass-through driver is 19 % slower than using a native interface.
Many ways to do the same thing; Hard to get things to work as many ODBC drivers have different bugs in different areas.
Problematic; Microsoft changes the interface once in a while.
Insecure future (Microsoft pushes more for OLE than for ODBC).
JDBC
In theory portable between OS and databases.
Can be run in the web client.
Python + others
May be fine, but we don't use them.

For a more exhaustive list see next post

Regards
BollywoodSARGAM Database Developer Team