username -p database_name

This will parse the entire file, drop the existing tables, and restore the

whole file at the target machine.

Q: What is mysqladmin?

A:

mysqladmin is a useful utility for creating or dropping a database, shutting

down the server, watching client (thread) activity, and so on. It can flush

logs, hosts, change access passwords, as well as perform other functions.

Exercises

1. Write the syntax for checking a table called "my_table" for deleted blocks.


2. Give the syntax for showing the current list of active threads on a MySQL server.


3. Write the function call for encrypting the string "my string" with the salt in
   "ht1QbxA2IJPhU".

Day 20: Optimizing MySQL

Overview

Now that you have your database up and running and it's been in production for a couple of weeks,
everything is just beautiful. But wait, here comes an angry programmer telling you that your database is
slow. He has a whole bunch of users yelling at him because the application is taking forever to run a simple
query. You tell him that it is his code. He vehemently argues that it is your database and not his code that is
causing the problems. You argue back saying that you are using MySQL and that there is nothing else
faster. After a couple of minutes, the pain of a bruised ego fades and you both decide to sit down and look at
the problem. You find that it is a combination of both problems, bad SQL statements on the programmers
end and some design and performance problems on the administrator's end.

If this sounds familiar or if it may still be something on the horizon for you, this day will explain what and

where to look for performance problems. MySQL is fast, very fast, but there are things that you can do

to help tweak the out-of-the-box defaults. They may be design issues. They may be bad queries. This

day will help you locate and solve your performance problems.

Today, you will learn

ƒ Performance tuning

ƒ Making better SQL statements

ƒ Getting rid of dead space—cleaning up the database

Performance Tuning

One of the first places to look when you are having performance problems is the system itself. On what type
of computer is your database server running? How much memory does it have? What is its processor
speed? These are the types of question you should be asking yourself before you start using your database
in production.

The ideal situation for a database server is a top-of-the-line system that has a ton of memory and is only

used for the database server. This is ideal, but you don't always get what you want. MySQL is a

different breed of database. It runs on almost any platform, and it performs better on some than it does

on others.

MySQL was developed primarily on Intel machines running Linux. This is why Linux is probably the best

platform on which to run MySQL. And because the machines are running Linux, the hardware does not

need to be as high-end as it would if you were running Windows NT. This adds to the attractiveness of

MySQL—a high-performance database that can run on older machines. What a deal! MySQL does not

perform as well on the Windows platform as it does on other platforms. This can be attributed to many

reasons—memory allocation at the operating-system level being the primary one.

MySQL is multithreaded. This means that every time a connection is made to MySQL, MySQL creates a

thread. Each thread takes up memory. Caching results of queries also takes up memory. So the more

memory the better. This generally helps performance.