Re: mysqld Threads with Innobase
Nicholas, - Original Message - From: Nicholas Gaugler [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, October 09, 2002 8:22 AM Subject: mysqld Threads with Innobase I know it's not very important in normal day to day operations, but does anyone know what each thread does within mysqld with Innobase installed? It'd be nice to know what Innobase is doing when a process is pinned at 100% of the cpu..if it's doing cleanup from a huge update, etc. /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1682 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my ... only one thread, the main thread, does background cleaning operations. You can see from SHOW INNODB STATUS what the main thread is currently doing. It is a good idea to tell the id of that thread in the printout of SHOW INNODB STATUS. I will add it to 4.0.5. In your case there are lots of threads eating up 100 % of CPU. If the performance is very slow, this looks like yet another case of Linux 'thread thrashing' which is plaguing both MyISAM and InnoDB tables under certain loads. It is a clear performace bug in Linux or glibc, and we are working to find a fix or a workaround to that problem. You can try setting set-variable = innodb_thread_concurrency = 1 in my.cnf to remove the thread thrashing. That helps in some cases Thanks! nickg Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld Threads with Innobase
[ start stupid question ] Does setting innodb_thread_concurrency to 1 imply that only one innodb thread will be working at any given time? So using this on SMP servers that you would like to be answering simultaneous queries is probably not what you would really like to be doing, no? [ end stupid question ] Owen On Wed, 2002-10-09 at 02:55, Heikki Tuuri wrote: Nicholas, - Original Message - From: Nicholas Gaugler [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, October 09, 2002 8:22 AM Subject: mysqld Threads with Innobase I know it's not very important in normal day to day operations, but does anyone know what each thread does within mysqld with Innobase installed? It'd be nice to know what Innobase is doing when a process is pinned at 100% of the cpu..if it's doing cleanup from a huge update, etc. /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1682 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my ... only one thread, the main thread, does background cleaning operations. You can see from SHOW INNODB STATUS what the main thread is currently doing. It is a good idea to tell the id of that thread in the printout of SHOW INNODB STATUS. I will add it to 4.0.5. In your case there are lots of threads eating up 100 % of CPU. If the performance is very slow, this looks like yet another case of Linux 'thread thrashing' which is plaguing both MyISAM and InnoDB tables under certain loads. It is a clear performace bug in Linux or glibc, and we are working to find a fix or a workaround to that problem. You can try setting set-variable = innodb_thread_concurrency = 1 in my.cnf to remove the thread thrashing. That helps in some cases Thanks! nickg Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- USMail: InterGuide Communications, 1611 Dexter Avenue, Ann Arbor, MI 48103 Phone:+1 734 997-0922 FAX:+1 734 661-0324 mailto:[EMAIL PROTECTED] http://www.interguide.com/~osm/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld Threads with Innobase
Owen, - Original Message - From: Owen Scott Medd [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Nicholas Gaugler [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 5:44 PM Subject: Re: mysqld Threads with Innobase [ start stupid question ] Does setting innodb_thread_concurrency to 1 imply that only one innodb thread will be working at any given time? So using this on SMP servers that you would like to be answering simultaneous queries is probably not what you would really like to be doing, no? [ end stupid question ] only one thread is then allowed inside the InnoDB backend concurrently. But the threads also do work in the MySQL interpreter, and more importantly, in communication with the corresponding client application threads. Since 'thread thrashing' lowers performance 100-fold, also drastic measures like setting the InnoDB concurrency to 1 should be tried. Unfortunately, it does not usually help. The thrashing happens then outside the InnoDB backend. Owen Regards, Heikki On Wed, 2002-10-09 at 02:55, Heikki Tuuri wrote: Nicholas, - Original Message - From: Nicholas Gaugler [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, October 09, 2002 8:22 AM Subject: mysqld Threads with Innobase I know it's not very important in normal day to day operations, but does anyone know what each thread does within mysqld with Innobase installed? It'd be nice to know what Innobase is doing when a process is pinned at 100% of the cpu..if it's doing cleanup from a huge update, etc. /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1682 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my ... only one thread, the main thread, does background cleaning operations. You can see from SHOW INNODB STATUS what the main thread is currently doing. It is a good idea to tell the id of that thread in the printout of SHOW INNODB STATUS. I will add it to 4.0.5. In your case there are lots of threads eating up 100 % of CPU. If the performance is very slow, this looks like yet another case of Linux 'thread thrashing' which is plaguing both MyISAM and InnoDB tables under certain loads. It is a clear performace bug in Linux or glibc, and we are working to find a fix or a workaround to that problem. You can try setting set-variable = innodb_thread_concurrency = 1 in my.cnf to remove the thread thrashing. That helps in some cases Thanks! nickg Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqld Threads with Innobase
I know it's not very important in normal day to day operations, but does anyone know what each thread does within mysqld with Innobase installed? It'd be nice to know what Innobase is doing when a process is pinned at 100% of the cpu..if it's doing cleanup from a huge update, etc. 1655 ttyp1S 0:02 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1657 ttyp1S 0:08 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1658 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1659 ttyp1S 0:10 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1660 ttyp1S 0:03 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1661 ttyp1S 0:04 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1662 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1663 ttyp1S 0:34 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1664 ttyp1R 12:08 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1665 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1682 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata/ibdata1:2000M;ibdata/ibdata2:2000M;ibdata/ibdata3:50M:autoextend:max: 2000M innodb_log_group_home_dir = /usr/local/mysql/data/iblogs/ innodb_log_arch_dir = /usr/local/mysql/data/iblogs/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=98M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=256M set-variable = innodb_additional_mem_pool_size=40M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 Thanks! nickg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
