From: BAUMEISTER Alexandre
Date: March 25 1999 2:58pm
Subject: Tweaking Mysql ?
List-Archive: http://lists.mysql.com/mysql/956
Message-Id: <15665.990325@finance-net.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Bonjour,

  I'm  running Mysql on a Ultra2 Enterprise 4 x 300MHz CPU with 1024 Mo
  of RAM.

  I installed Mysql 3.22.16a and I'm quite happy with the result.

  I would like to know what tweaking I could do in order to get things
  perfect.

  Here is how I run mysqld :

> port            = 3306
> socket          = /tmp/mysql.sock
> skip-locking
> skip-name-resolve
> set-variable    = key_buffer=16M
> set-variable    = sort_buffer=4M
> set-variable    = record_buffer=1M
> set-variable    = back_log=64
> set-variable    = max_connections=256
> set-variable    = max_allowed_packet=1M
> set-variable    = thread_stack=128K
> set-variable    = table_cache=128
> set-variable    = tmp_table_size=4M


  Here is 'mysqladmin variables' :
+----------------------------+------------------------------------------------+
| Variable_name              | Value                                          |
+----------------------------+------------------------------------------------+
| back_log                   | 64                                             |
| connect_timeout            | 5                                              |
| basedir                    | /usr/local/mysql-3.22.16a/                     |
| datadir                    | /usr/local/mysql-3.22.16a/var/                 |
| delayed_insert_limit       | 100                                            |
| delayed_insert_timeout     | 300                                            |
| delayed_queue_size         | 1000                                           |
| join_buffer                | 131072                                         |
| key_buffer                 | 16773120                                       |
| language                   | /usr/local/mysql-3.22.16a/share/mysql/english/ |
| log                        | OFF                                            |
| long_query_time            | 10                                             |
| low_priority_updates       | OFF                                            |
| max_allowed_packet         | 1047552                                        |
| max_connections            | 256                                            |
| max_connect_errors         | 10                                             |
| max_delayed_insert_threads | 20                                             |
| max_join_size              | 4294967295                                     |
| max_sort_length            | 1024                                           |
| net_buffer_length          | 16384                                          |
| port                       | 3306                                           |
| record_buffer              | 1044480                                        |
| skip_locking               | ON                                             |
| socket                     | /tmp/mysql.sock                                |
| sort_buffer                | 4194296                                        |
| table_cache                | 128                                            |
| thread_stack               | 131072                                         |
| tmp_table_size             | 4194296                                        |
| tmpdir                     | /var/tmp/                                      |
| update_log                 | OFF                                            |
| wait_timeout               | 28800                                          |
+----------------------------+------------------------------------------------+


  And here is 'mysqladmin extended-status' :
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 306        |
| Created_tmp_tables       | 863667     |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_delete           | 8739       |
| Handler_read_first       | 3418       |
| Handler_read_key         | 100941859  |
| Handler_read_next        | 913699054  |
| Handler_read_rnd         | 2075434070 |
| Handler_update           | 6265088    |
| Handler_write            | 110732962  |
| Key_blocks_used          | 15641      |
| Key_read_requests        | 427948059  |
| Key_reads                | 306226     |
| Key_write_requests       | 9875197    |
| Key_writes               | 2382190    |
| Max_used_connections     | 65         |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 127        |
| Open_files               | 107        |
| Open_streams             | 0          |
| Opened_tables            | 992        |
| Questions                | 13468705   |
| Running_threads          | 10         |
| Slow_queries             | 16         |
| Uptime                   | 1041287    |
+--------------------------+------------+

  'top'  tells  me  that  I have about 600Mo of RAM free even when the
  server is fully in use and load never goes higher than 0.50 .

  I  don't  really  know  how to read the 'mysqladmin extended-status'
  output  so if someone have a idea for tweaking Mysql's parameters in
  order to get things even better ...

  Yours,
  Alex.