5.3 Database Server Tuning

only for RuBoard - do not distribute or recompile

5.3 Database Server Tuning

There are a number of settings you can tweak at the MySQL server level to influence application performance. One thing to keep in mind when tuning a server is that server behavior will affect all the applications using that server. An improvement for one application may have a detrimental effect for other applications on the same server.

There are a number of variables that can be modified in the MySQL server that may improve your performance. A full reference to these parameters can be found by typing mysqld -help .

In general, when tuning MySQL, the two most important variables are:

table_cache: table_cache controls the size of the MySQL table cache. Increasing this parameter allows MySQL to have more tables open simultaneously without opening and closing files.
key_buffer_size: key_buffer_size controls the size of the buffer used to hold indexes. Increasing this will improve index creation and modification and will allow MySQL to hold more index values in memory.

only for RuBoard - do not distribute or recompile

5.4 Operating System/Hardware Tuning

A full discussion of hardware and/or OS tuning is beyond the scope of this book. However, here are a few things to consider:

Many of the traditional hardware upgrades can help MySQL perform better. For example, adding memory to your system gives you more memory to allocate to MySQL caches and buffers. Upgrading to faster disks will improve I/O speed.
Intelligently distributing your databases across multiple physical devices can also help. For example, placing your data files and index files on different disks can improve performance.
Static binaries are faster than dynamic binaries. You can configure MySQL to link statically instead of dynamically when you build it.

In general, you should do optimizations and upgrades based on an understanding of how your database is most frequently used.

only for RuBoard - do not distribute or recompile

Chapter 6. Security

Among the many tasks of database administrators and architects is the critical one of making sure only the proper users can access data stored in the database. Ensuring proper data access (security) comes in many forms. For example:

Database administrators (DBAs) manage access to the database engine itself. They provide access to individual databases for specific applications and developers. They also make sure that a poorly designed application cannot be used as a tunnel into the data of another application.
System administrators manage the security of the operating system and hardware on which MySQL runs. Their job is to ensure that only MySQL DBAs have access to the physical files used by MySQL on a given machine. In many MySQL environments, the DBA and system administrator are the same person.
Database architects design the access to the applications to which the DBAs have granted access. A DBA, for example, may have given a web site full CREATE , INSERT , UPDATE , and DELETE privileges to its database, but it is up to the database architect to ensure that only valid application users are taking advantage of those privileges.

A security failure at any one of these points can compromise the integrity of all the data in the database engine. In this chapter, we examine how to secure MySQL at all levels.

only for RuBoard - do not distribute or recompile

Buy on amazon.com >>

Reese G., King T., Yarger R.J.

<< Previous page

Table of contents

Next page >>