Answer Posted / frank
Optimizing MySQL
What one can and should optimize
* Hardware
* OS / libraries
* SQL server (setup and queries)
* API
* Application
Optimizing hardware for MySQL
* If you need big tables ( > 2G), you should consider
using 64 bit hardware like Alpha, Sparc or the upcoming
IA64. As MySQL uses a lot of 64 bit integers internally, 64
bit CPUs will give much better performance.
* For large databases, the optimization order is
normally RAM, Fast disks, CPU power.
* More RAM can speed up key updates by keeping most of
the used key pages in RAM.
* If you are not using transaction-safe tables or have
big disks and want to avoid long file checks, a UPS is good
idea to be able to take the system down nicely in case of a
power failure.
* For systems where the database is on a dedicated
server, one should look at 1G Ethernet. Latency is as
important as throughput.
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.
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.
Choosing API
* PERL
o Portable programs between OS and databases
o Good for quick prototyping
o One should use the DBI/DBD interface
* PHP
o Simpler to learn than PERL.
o Uses less resources than PERL, which makes it
good for embedding in Web servers.
o One can get more speed by upgrading to PHP4.
* C
o The native interface to MySQL.
o Faster and gives more control
o Lower level, so you have to work more.
* C++
o Higher level gives you more time to code your
application.
o Is still in development.
* ODBC
o Works on Windows and Unix
o Almost portable between different SQL servers.
o Slow; MyODBC, which is a simple pass-through
driver is 19 % slower than using a native interface.
o Many ways to do the same thing; Hard to get
things to work as many ODBC drivers have different bugs in
different areas.
o Problematic; Microsoft changes the interface
once in a while.
o Insecure future (Microsoft pushes more for OLE
than for ODBC).
* JDBC
o In theory portable between OS and databases.
o Can be run in the web client.
* Python + others
o May be fine, but we don't use them.
Optimizing the application
* One should concentrate on solving the problem.
* When writing the application one should decide what is
most important:
o Speed
o Portability between OS
o Portability between SQL servers
* Use persistent connections.
* Cache things in your application to lessen the load of
the SQL server.
* Don't query columns that you don't need in your
application.
* Don't use SELECT * FROM table_name...
* Benchmark all parts of your application, but put the
most effort into benchmarking the whole application under
the worst possible 'reasonable' load. By doing this in a
modular fashion you should be able to replace the found
bottleneck with a fast 'dummy module', you can then easily
identify the next bottleneck (and so on).
* Use LOCK TABLES if you do a lot of changes in a batch;
For example group multiple UPDATES or DELETES together.
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
What are date and time data types?
What is a session in mysql?
How to change a password for an existing user via mysqladmin?
How to dump a table from a database.
Why mongodb is faster than mysql?
Why is it called mysql?
How do I stop a mysql service?
How to store values to array from mysql database in php?
Explain timestamp?
what is mySQL. Is there any specific qualification required to do this course ? What is the scope in India after doing mySQL
Is mysql written in c?
What you can use regular expression for in mysql? Support your answer with an example?
How do I stop a mysql command?
How do I install mysql?
Can we store files in mysql?
