SpirirHost Support Centre: MySQL Reference Manual p.365

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 5: MySQL Optimisation 365 MySQL uses algorithms that are very scalable, so you can usually run with very little memory. If you, however, give MySQL more memory, you will normally also get better performance. When tuning a MySQL server, the two most important variables to use are key_buffer_ size and table_cache. You should rst feel condent that you have these right before trying to change any of the other variables. If you have much memory (>=256M) and many tables and want maximum performance with a moderate number of clients, you should use something like this: shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O read_buffer_size=1M & If you have only 128M and only a few tables, but you still do a lot of sorting, you can use something like: shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M If you have little memory and lots of connections, use something like this: shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \ -O read_buffer_size=100k & or even: shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \ -O table_cache=32 -O read_buffer_size=8k -O net_buffer_ If you are doing a GROUP BY or ORDER BY on les that are much bigger than your available memory you should increase the value of record_rnd_buffer to speed up the reading of rows after the sorting is done. When you have installed MySQL, the `support-files' directory will contain some dif- ferent `my.cnf' example les, `my-huge.cnf', `my-large.cnf', `my-medium.cnf', and `my-small.cnf', you can use as a base to optimise your system. If there are very many connections, \swapping problems" may occur unless mysqld has been congured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course. Note that if you change an option to mysqld, it remains in eect only for that instance of the server. To see the eects of a parameter change, do something like this: shell> mysqld -O key_buffer=32m --help Make sure that the --help option is last; otherwise, the eect of any options listed after it on the command-line will not be re ected in the output. 5.5.3 How Compiling and Linking Aects the Speed of MySQL Most of the following tests are done on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads. You get the fastest executable when you link with -static. On Linux, you will get the fastest code when compiling with pgcc and -O3. To compile `sql_yacc.cc' with these options, you need about 200M memory because gcc/pgcc needs

MySQL Reference Manual

Home
Hosting Plans \| Domain Registration \| About Us \| Contact Us \| Site Map Terms of Use \| Privacy Policy \| Guarantees Merchant Accounts

SpiritHost - web hosting for spiritual and education sites SpiritHit.com - Religious and Spiritual Portal Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide