You can get the current buffer sizes with:
This should result in a list of all mysqld options and configurable variables like the following.
Possibly variables to option --set-variable (-O) are: back_log current value: 5 join_buffer current value: 131072 key_buffer current value: 1048568 max_allowed_packet current value: 65536 max_connections current value: 90 max_join_size current value: 4294967295 max_sort_length current value: 1024 net_buffer_length current value: 8192 record_buffer current value: 131072 sort_buffer current value: 2097144 table_cache current value: 64 tmp_table_size current value: 1048576 thread_stack current value: 65536
|back_log||How many outstanding connection requests may MySQL have. This comes into play when the main MySQL thread gets VERY many connection requests in a very short time. It then takes some time (but very short) for the main thread to check the connection and start a new thread. The back_log is how many connects can be stacked during this short time before MySQL momentarily stops answering new requests. You only need to increase this if you expect a large number of connections in a short period of time. In other words, the size of the listen queue for incoming tcp/ip connections. The manual page for the unix system call listen(2) should have more details. Check your OS documentation for the maximum value for this variable.|
|join_buffer||This buffer is used for full joins (without indexes). It is allocated one time for each full join between two tables. Increase this to get a faster full join when adding indexes is not possible. Normally the best way to get fast joins is by adding indexes.|
Buffers index blocks and are shared by all threads. You might want to
increase this when doing many delete/inserts on a table with lots of
indexes. To get even more speed use |
Max size of one packet. This allows the message buffer to grow up to
this limit when needed (it is initiated to
|max_connections||How many simultaneous clients are allowed. If you increase this you probably has to increase the number of file descriptors mysqld has. This is Operating system depended so look at you OS documentation.|
Joins that touch more records than max_join_size return an error. Set this if
you have users to tend to make joins without a |
The number of bytes to use when sorting on |
|net_buffer_length||The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory you can set it to the expected size of a query.|
|record_buffer||Each thread that is doing a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans you may want to increase this.|
Each thread that needs to do a sort allocates a buffer of this
size. Increase this for faster |
|table_cache||Number of open tables for all threads. If this is increased you must see to that the number of open file descriptor is also increased. MySQL needs two file descriptors for each unique table.|
If a temporary table gets bigger than this a |
How big will each threads |
> safe_mysqld -O key_buffer=16M -O table_cache=128 \ -O sort_buffer=4M -O record_buffer=1M &If you have little memory with lots of connections, use something like:
> safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &or even
> safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 \ -O record_buffer=8k -O net_buffer=1K &Note that if you change an option to
mysqldit is only for that instance. To see the effects of a parameter change do something like this
mysqld -O keybuffer=32m --help. If there are very many connections, 'swapping problems' may occur if mysqld has not been configured to use very little memory for each connection. It also works better if you have a enough memory for all connections of course. For example, for 200 open connections one should have a table cache of at least 200 * (max_number of tables in join).
mysqldone can specify a key buffer. This will buffer all indexes in all tables at FIFO basis (variable keybuffer).
BLOBs are stored on disk. One current problem is that if the HEAP table exceeds the size of
tmp_table_size, one will get the error: 'The table ### is full'. In the future we will fix this by automatically changing the in memory (HEAP) table to a disk based (NISAM) table if needed. To go around this problem one can increase the
-O tmp_table_size=#option to mysqld or use the SQL option
SQL_BIG_TABLES. See section SET OPTION syntax.. In MySQL
3.20the maximum size of the temporary table was
recordbuffer*16, so if you are using this version you have to increase
recordbuffer. There also exists a patch to always store temporary tables on disk, but this will affect the speed of all complicated queries.
BLOBs) is allocated. A
BLOBuses 5 to 8 bytes +length of blob data.
BLOBs, a buffer is enlarged dynamically to read in larger
BLOBs. If one scans a table there will be a allocated buffer as large as the largest
mysqladmin refreshcloses all tables that are not in use and marks all used tables to be closed when the running thread finishes. This will effectively free most used memory.
mysqld, ps and other programs may report that it
uses a lot of memory. This may be caused by thread-stacks on different
memory addresses. For example, the Solaris ps calculates the unused memory
between stacks as used memory. You can verify this by checking available
swap with 'swap -s'. We have tested
mysqld with commercial
memory-leakage detectors so there should not be any memory leaks.
INDEX(), are stored
in B trees. Strings are automatically prefix- and end-space compressed.
INDEX(col1, col2) creates a multiple index over the two columns.
The index can be seen as a concatenation of the given columns. If you use
INDEX(col2) instead of
you get two separate indexes instead.
SELECT * FROM table WHERE col1=# AND col2=#
In a case of
INDEX(col1,col2) the right row(s) can be fetched
directly. In a case of
optimiser decides which index will find fewer rows and this index will
be used to fetch the rows.
If the table has an index
INDEX(col1,col2,col3...) the prefix
of this can be used by the optimiser to find the rows. This means
that the above gives you search capabilities on:
MySQL can't use a portion of an index to locate rows through an index.
With the definition
SELECT * FROM table WHERE col1=# SELECT * FROM table WHERE col2=# SELECT * FROM table WHERE col2=# and col3=#
only the first query will use indexes.
(Incomplete, MySQL does a lot of optimisations.)
The first issue about making a slow
SELECT ... WHERE faster
is to check if one could add an index. All references between
different tables should usually be done with indexes. One can use
EXPLAIN command to check which indexes are used in a
select. See section EXPLAIN syntax. Get information about a SELECT.. See section How does MySQL use indexes?
((a AND b) AND c OR (((a AND b) AND (c AND d))))->
(a AND b) OR (a AND b AND c AND d)
(a<b AND b=c) AND a=5->
b>5 AND b=c A=5
(b>=5 AND b=5) OR (b=6 and 5=5) or (B=7 and 5=6)->
B=5 or B=6
HAVINGis merged with
WHEREif one doesn't use
GROUP BYor group functions.
WHEREis constructed to get a fast
WHEREevaluation for each sub join and also to skip records as soon as possible.
LIKEwith a character prefix like 'something%'.
index = 1 or A = 10->
NULL(can't use index.)
index = 1 or A = 10 and index=2->
index = 1 OR index = 2
index_part_1 = const and index_part_3 = const->
index_part_1 = const
const_table.index = constant
const_table.index_part_1 = const_table2.column and const_table.index_part_2 = constant
ORDER BYand in
GROUPcome from the same table, then this table is preferred first when joining.
The cache of open tables can grow to a maximum of
(default 64, changeable with -O table_cache=#).
A table is never closed, except when the cache is full and another thread
tries to open a table or if one uses 'mysqladmin refresh'.
When the limit is reached, MySQL closes as many tables as possible, until the cache size has been reached or there are no more unused tables. This means that if all tables are in use by some threads, there will be more open tables than the cache limit, but the extra tables will be closed eventually. Tables are closed according to last-used order.
A table is opened (again) for each concurrent access. This means that
if one has two threads running on the same table or access the table
twice in the same query (with
AS) the table needs to be opened twice.
The first open of any table takes two file descriptors, each following
use of the table takes only one file descriptor.
If MySQL notices that a table is a symbolic linked it will resolve the symlink and use the table it points to instead. MySQL will not notice if the database directory is symbolic linked! If you symlink a database to another and use the same table trough both databases, the tables will be inconsistent after any update to the tables.
Each table is actually three files. If you have many files in a directory open, close and create will be slow. If you also do selects on many different tables there will be a little overhead because when the table cache is full, for every table that has to be opened another has to be closed. One can make the overhead smaller by making the table cache larger.
All locking in MySQL is deadlock free. This is managed by always requesting all needed locks at once at query start and always locking the tables in the same order.
The locking method MySQL uses for
WRITE lock works as follows:
If there is no locks on the table, put a write lock on it, else put the lock in the write lock queue.
The locking method MySQL uses for
READ locks works as follows:
If there is no write locks on the table, put a read lock on it else put the lock in the read lock queue.
When a lock is released first use give the lock to the threads in the write lock queue and after this to the threads in the read lock queue.
This means that if you have many updates on the same table, select statements will be waiting until there is no more updates.
To fix this in the case where you do many inserts and many selects on the same table you could insert rows in another table and once in a while update the other table with all records from the temporary table.
This can be done with the following code:
LOCK TABLES real_table WRITE, insert_table WRITE insert into real_table select * from insert_table delete from insert_table UNLOCK TABLES
One could also change the locking code in mysys/thr_lock.c to use only one queue. In this case write locks would have the same priority that read locks and this could help some applications.
NOT NULLif possible. It makes everything faster and you save one bit per column.
MEDIUMINTis often better than
VARCHARcolumns, a fixed size record format will be used. This is much faster but may unfortunately waste some space. See section What are the different row formats? Or when to use VARCHAR/CHAR?.
isamchk -aon the table once it is loaded with relevant data. This updates a value for each index that tells how many rows that have the same value for this index on average. Of course, this is always 1 for unique indexes.
isamchk -Sir1(if you want so sort on index 1). If you have a unique index from which you want to read all records in numeric order, this is a good way to make that faster.
LOAD DATA FROM INFILE. This is usually 20 times faster than using a lot of
INSERTs. If the text file isn't on the server, rcp it to the server first. See section LOAD DATA INFILE syntax. You can even get more speed when loading data to tables with many indexes by doing:
isamchk -k0 database/table_name. This will remove all use of indexes from the table.
LOAD DATA INFILE....
isamchk -rq database/table_name.
LOAD DATA FROM INFILEand
INSERTis to enlarge the key buffer. This can be done with the
-O key_buffer=#option to
(safe)mysqld. For example 16M should be a good value if you have much RAM :)
SELECT ... INTO OUTFILE. See section LOAD DATA INFILE syntax.
LOCK TABLESon the tables.
...INTO OUTFILE...are atomic so you don't have to use
LOCK TABLESwhen using these. See section
To check how you are doing, run
isamchk -evi on the .ISM
file. @c See section The MySQL table check, optimise and repair program.
The time to insert a record consists of:
Where (number) is proportional time. This does not take into consideration the initial overhead to open tables (which is done once for each simultaneous running query).
The size of the table slows down the insert of indexes with N log N (B-trees).
A way of speeding up inserts is to lock your table during the inserts.
LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23) INSERT INTO a VALUES (2,34) INSERT INTO a VALUES (4,33) INSERT INTO a VALUES (8,26) INSERT INTO a VALUES (6,29) UNLOCK TABLES;
The main speed difference is that the index buffer is only flushed once to disk for all inserts. Normally there would be as many index buffer flushes as there are inserts.
Locking will also lower the total time of multi-connection test but the maximum wait time for some threads will go up.
thread 1 does 1000 inserts thread 2, 3, and 4 does 1 insert thread 5 does 1000 inserts
If you don't use locking, 2, 3 and 4 will finish before 1 and 5. If you use locking 2,3,4 may finish before 1 or 5 but probably not, but the total time should be about 40% faster.
DELETEs are very fast in
MySQL, one will obtain better overall performance by adding locks
around everything that does more than about 5 inserts/updates in a row.
If one does very many inserts in a row one could do a
TABLES followed by a
LOCK TABLES once in a while (about each
1000 rows) to give other threads access to the table. This would still
give a nice performance gain.
LOAD DATA INFILE is much faster still.
The delete time of a record is exactly proportional to the number of indexes. To increase the speed of deletes you can increase the size of the index cache. The default index cache is 1M and to get faster deletes it should be increased a couple of times (try 16M if you have enough memory).
mysqldwith the right options. More memory gives more speed if you have it. See section How does one change the size of MySQL buffers?.
SELECTs faster. See section How does MySQL use indexes?
NOT NULLon all columns. See section How should I arrange my table to be as fast/small as possible?
--skip-lockingdisables file locking between SQL requests. This gives a greater speed but has the following consequences:
mysqladmin refreshbefore one tries to check/repair tables with
isamchk -d table_nameis always allowed).
--skip-lockingis on by default when compiling with MIT threads. This is because flock() isn't fully supported by MIT threads on all platforms.
MySQL dosen't have true SQL VARCHAR() types.
MySQL has instead 3 different ways to store records and uses this to
If one doesn't use any of the
column types a fixed row size is used, otherwise a dynamic row size is
VARCHAR() are treated identically from
the applications point of view; Both truncates end space from the column
when the column is accessed.
You can check the format used in a table with
MySQL has three different table formats:
BLOBtype in a table.
isamchk -rfrom time to time to get better performance. Use
isamchk -ei table_namefor some statistics.
isamchk -ed. All links may be removed with
When you run
mysqladmin status you get something like:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
This can be somewhat perplexing if you only have 6 tables.
As MySQL is multithreaded it may have many queries on the same table at once. To minimise the problem with two threads having different states on the same file, I open the table again for each concurrent thread. This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.
Go to the first, previous, next, last section, table of contents.