mysql + pid count + increases


I have mysql 4.1.22 compiled on RHEL 4 and running fine. Over the time the threads / pids of mysql keep on increasing.
For eg :

ps -ef |grep mysql |wc -l63ps -ef |grep mysql |wc -l101

The system is Intel Dual Core, RHEL4, 300 GB SATA HDD with 16GB RAM. Also I have observed that I cannot login into mysql shell when the process count goes higher beyond 100 meaning that it takes lots of time to enter into the mysql> shell

I am using the my-innodb-heavy-4G.cnf as my.cnf with couple of modifications like

max_connections=1000skip-name-resolveinnodb_file_per_tableinnodb_flush_method=O_DIRECTlower_case_table_names=1expire_logs_days = 10net_read_timeout=60net_write_timeout=120max_connect_errors = 100innodb_locks_unsafe_for_binlog=1innodb_flush_log_at_trx_commit=2innodb_lock_wait_timeout=300myisam_repair_threads = 10innodb_file_io_threads = 20innodb_thread_concurrency = 16innodb_table_locks = 0wait_timeout = 480

Rest of the settings remain same.

The configure command used for compiling mysql is

CFLAGS="-static -O3 -march=nocona -funroll-loops" \CXX=gcc CXXFLAGS="-static -O3 -march=nocona -pipe -mmmx -msse -msse2 -mfpmath=sse,387 -funroll-loops -fno-exceptions -fno-rtti -fomit-frame-pointer -felide-constructors -mtune=nocona" ./configure \ --prefix=/usr/local/mysql \ --with-mysqld-user=mysql \ --libexecdir=/usr/sbin \ --sysconfdir=/etc \ --mandir=/usr/share/man \ --with-client-ldflags=-all-static \ --with-mysqld-ldflags=-all-static \ --with-thread-safe-client \ --with-unix-socket-path=/tmp/mysql.sock \ --localstatedir=/usr/local/mysql \ --enable-assembler \ --disable-shared \ --without-readline \ --without-debug \ --without-docs \ --without-bench

and then make, make install and strip /usr/sbin/mysqld

What am I doing wrong and why the process count is increasing over the time ?


What does “SHOW PROCESSLIST” show before mysql choke up ?


I cannot invoke the show processlist as the command just hangs with no output for long time.

is the setting innodb_file_io_threads = 20 too high ?