Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Dec 17, 2001

The Mysqld Variables - Page 2

By Ian Gilfillan

So now you've compiled MySQL to be fast, and you know it's built to be fast. But, just as the Formula 1 car requires a big effort during the race to get to the finish — fuel, tires and driver all working smoothly — so should you be tweaking your database server for more speed. Many novice MySQL users are unaware that the server variables can be tweaked. What started out as a small database gets busier and busier, and they add more and more memory. But it's all in vain as the server doesn't even use this extra RAM. It's time to introduce the mysqld variables. You can view the variables by running:

mysqladmin variables

from the command line, or by running:

SHOW VARIABLES

from inside MySQL. You will see something like the following. In an untweaked system, most of the variables will probably be similar to those shown in this file here.

Together with looking at the variables, you'll need to look at the current system values. To do this, run:

mysqladmin extended-status

from the command line, or:

SHOW STATUS

from inside MySQL. The results will be something like the following:

+--------------------------+----------+
| Aborted_clients          | 142      |
| Aborted_connects         | 5        |
| Bytes_received           | 9005619  |
| Bytes_sent               | 15444786 |
| Connections              | 794      |
| Created_tmp_disk_tables  | 1        |
| Created_tmp_tables       | 716      |
| Created_tmp_files        | 0        |
| Delayed_insert_threads   | 0        |
| Delayed_writes           | 0        |
| Delayed_errors           | 0        |
| Flush_commands           | 1        |
| Handler_delete           | 27       |
| Handler_read_first       | 1534     |
| Handler_read_key         | 608840   |
| Handler_read_next        | 652228   |
| Handler_read_prev        | 164      |
| Handler_read_rnd         | 14143    |
| Handler_read_rnd_next    | 1133372  |
| Handler_update           | 90       |
| Handler_write            | 131624   |
| Key_blocks_used          | 6682     |
| Key_read_requests        | 2745899  |
| Key_reads                | 6026     |
| Key_write_requests       | 63925    |
| Key_writes               | 63790    |
| Max_used_connections     | 20       |
| Not_flushed_key_blocks   | 0        |
| Not_flushed_delayed_rows | 0        |
| Open_tables              | 64       |
| Open_files               | 128      |
| Open_streams             | 0        |
| Opened_tables            | 517      |
| Questions                | 118245   |
| Select_full_join         | 0        |
| Select_full_range_join   | 0        |
| Select_range             | 2300     |
| Select_range_check       | 0        |
| Select_scan              | 642      |
| Slave_running            | OFF      |
| Slave_open_temp_tables   | 0        |
| Slow_launch_threads      | 0        |
| Slow_queries             | 8        |
| Sort_merge_passes        | 0        |
| Sort_range               | 3582     |
| Sort_rows                | 16287    |
| Sort_scan                | 806      |
| Table_locks_immediate    | 82957    |
| Table_locks_waited       | 2        |
| Threads_cached           | 0        |
| Threads_created          | 793      |
| Threads_connected        | 1        |
| Threads_running          | 1        |
| Uptime                   | 1662790  |
+--------------------------+----------+

my.cnf

Fortunately, MySQL has made life a lot easier by providing default values that fit most systems. Simply using the correct default set of values can make a significant difference. You can set these variables in a file called:

my.cnf

which is usually placed in:

DATADIR/my.cnf

on Unix systems (DATADIR is the directory specified when the system was configured, not the datadir as listed in the variables above!), or C:\mysql\data\my.cnf on Windows systems. The file can be placed elsewhere if you have more than one MySQL server running, or want to specify different options down to a user level, but having one set of values per server is usually sufficient. The 4 sample files that MySQL supplies are:

my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf.

So, pump in my-huge.cnf, and let the good times roll, I hear you say. Well, not quite!

my-huge.cnf is recommended for systems that have at least 1GB memory, and run mainly MySQL (if your Web server and db server are on the same machine, don't even think about using this set!) my-large.cnf is for systems with slightly less memory (512MB), and also mainly running MySQL. my-medium.cnf is tweaked for a system where MySQL and a Web server are running together with around 128MB, or around 64MB with MySQL alone. Lastly, and probably leastly if you're reading this article, is my-small.cnf, running on a system with less than 64MB, where MySQL can't hog too many of the resources.



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date