Description:
I have been testing MySQL 5.1.30 (source package compiled with the following options
./configure 'CC=gcc' 'CFLAGS=-DBIG_JOINS=1 -O3' 'CXX=g++' 'CXXFLAGS=-DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -O3' '--prefix=.......' '--localstatedir=....../data' '--libexecdir=....../bin' '--datadir=...../data' '-with-comment=Debian amd_64' '--with-server-suffix=Debian x86_64' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-big-tables' '--with-raid' '--with-mysqld-user=mysql' '--with-libwrap' '--with-mysqld-ldflags=-all-static' '--with-vio' '--with-bench' '--with-readline' '--with-extra-charsets=all' '--with-innodb' '--with-isam' '--with-archive-storage-engine' '--with-csv-storage-engine' '--with-federated-storage-engine' '--with-embedded-privilege-control' '--with-zlib-dir=bundled' '--with-ssl=bundled' '--with-fast-mutexes' '--with-pstack' '--with-partition'"
for storing data in several databases.
Each database contains a few hundred thousand tables.
All the tables have MYISAM as storage engine.
All the operations executed on those tables during the tests are inserts+updates.
Each request is wrapped using connect, and disconnect, so each client sends connect, statement (insert or update) and disconnect.
The number of concurrent requests that are performed is 200 (200 parallel Perl clients sending requests).
MySQL settings on my.cnf
table_cache = 260
thread_cache_size = 256
thread_stack = 256K
thread_concurrency = 16
During the benchmarks the number of threads created grows quite high compared to previous MySQL versions such as 5.1.23 (from source code or Debian). The same behaviour happened also on MySQL 5.1.26 (from source code or MySQL Debian) and MySQL 5.1.28
E.g.
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 255 |
| Threads_connected | 2 |
| Threads_created | 3276 |
| Threads_running | 1 |
+------------------------+-------+
How to repeat:
Create several databases (e.g. 20, 30, etc..)
Each database contains a few hundred thousand tables.
All the tables have MYISAM as storage engine.
Run from client/s 200 parallel perl processes that connect, send insert or update, disconnect
