Explain MySQL optimization?

Question Posted / pranab

1 Answers
5441 Views
I also Faced
E-Mail Answers

Explain MySQL optimization?..

Answer / 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