pt-query-digest --processlist
Hi guys, I'm trying to use pt-query-digest on one of our Prod servers. The problem our slow log is disabled, no tcpdump is installed and I decided to use --processlist parameter which I never tried before. pt-query-digest version 2.2.2 I run to connect to remote node: pt-query-digest --processlist h=192.168.1.111,u=user -p PASSWORD --print --no-report --run-time 60 SERVER-`date +%Y%m%d-%H` I get: Unknown option: print Without --print parameter I don't have errors but I do not get any output into file. The prod server is very busy. How to make it work? Thanks, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: pt-query-digest --processlist
Hi Claudio, Thanks for reply. Great. I was sure you can't enable slow log dynamically on 5.0 and 5.1. That solves my issue for 5.1 at least. Many thanks, Igor On 06/06/13 13:06, Claudio Nanni wrote: Hi, First of all I discourage you to use --processlist, I don't think it is enough an accurate method to analyse queries. You can dynamically enable the Slow Query Log with long_query_time=0 and get a way better data. Check the syntax for SET GLOBAL variables. Remember to disable it afterwards. Now to your question, pt-query-digest version 2.2.2 I run to connect to remote node: pt-query-digest --processlist h=192.168.1.111,u=user -p PASSWORD --print --no-report --run-time 60 SERVER-`date +%Y%m%d-%H` I get: Unknown option: print Maybe because such option does not exist? Cheers Claudio
Re: pt-query-digest --processlist
- Original Message - From: Claudio Nanni claudio.na...@gmail.com You can dynamically enable the Slow Query Log with long_query_time=0 Except when you have persistent connections on a stock mysqld. The recent Percona ones do have a way to change the settings of existing connections, iirc. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: pt-query-digest --processlist
Hi, First of all I discourage you to use --processlist, I don't think it is enough an accurate method to analyse queries. You can dynamically enable the Slow Query Log with long_query_time=0 and get a way better data. Check the syntax for SET GLOBAL variables. Remember to disable it afterwards. Now to your question, pt-query-digest version 2.2.2 I run to connect to remote node: pt-query-digest --processlist h=192.168.1.111,u=user -p PASSWORD --print --no-report --run-time 60 SERVER-`date +%Y%m%d-%H` I get: Unknown option: print Maybe because such option does not exist? Cheers Claudio
RE: mysqlimport --use-threads / mysqladmin processlist
I'm skeptical that use-treads can every be very effective. What order are the rows in? They are probably in PRIMARY KEY order, which means that the INSERTing threads will be fighting over similar spots in the table. Is it I/O bound when it is single-threaded? If so, then there can't be any improvement with use-threads. etc. Suggest you file a bug with bugs.mysql.com. If nothing else, the documentation should say more than it does. -Original Message- From: Róbert Kohányi [mailto:kohanyi.rob...@gmail.com] Sent: Tuesday, July 24, 2012 10:52 AM To: mysql@lists.mysql.com Subject: mysqlimport --use-threads / mysqladmin processlist I'm in the middle of migrating an InnoDB database to an NDBCluster. I use mysqldump to first create two dumps, the first one contains only the database schema, the second one contains only tab delimited data (via mysqldump --tab). I edit my InnoDB schema here and there (ENGINE=InnoDB to ENGINE=NDB, etc.) import it and after this I import the InnoDB data *as is* using mysqlimport. I use it like this: mysqlimport --local --use-threads=4 db dir/*.txt (dir of course cotains the tab delimited data I dumped before.) The import starts, and I check its progress via mysqladmin, like this: mysqladmin --sleep=1 processlist this is what I see: http://pastebin.com/raw.php?i=M23fWVjc Only a single process seems to be loading my data. Is this what I *should* see, or, in my case using 4 threads, should I see four processes? I'm not asking which one will be faster, I'm just simply confused because I don't know what to expect. If I start four different mysqlimport processes, each one importing different files, then I can see four different process in the mysql processlist. If it's matters, here is my server version (I use the official binaries). Server version: 5.5.25a-ndb-7.2.7-gpl MySQL Cluster Community Server (GPL) Regards, Kohányi Róbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqlimport --use-threads / mysqladmin processlist
Yes, the rows are in primary key order, however each row contains specific integer primary keys; I'm not inserting nulls into a table where the primary key is auto increment, so I don't see why concurrent inserts would fight for similar spots (although, I'm admittedly not a MySQL hotshot, so the basis of my assumption is a *hunch* only). I'm not sure (yet) if a single-threaded operation would run into an i/o bottleneck. I didn't run mysqlimport using --use-threads=1 just yet (will do if I have the time), but when I've ran it with --use-threads=4 the import (of a ~500 MB dump) took more time than running for different processes (I've split my tab delimited dumps with split into four even pieces and imported those in four different sessions). Anyway, it seems that doing a simple import (from a dump, which isn't tab delimited, but contains complete or extended inserts) takes the same amount of time than doing a mysqlimport using --use-threads=4 and as it turns out splitting my tab delimited dump is too complex to handle gracefully, because my data contains newline characters all over the place, so I've dropped the idea of this whole mysqlimport thing for now. (I'll try the method of migrating an InnoDB database to an NDBCluster described here[1] instead.) If I have the time I'll write up a bug report, or documentation enhancement request for this. Thanks for the input! Regards, Kohányi Róbert [1]: http://johanandersson.blogspot.se/2012/04/mysql-cluster-how-to-load-it-with-data.html On Wed, Jul 25, 2012 at 6:49 PM, Rick James rja...@yahoo-inc.com wrote: I'm skeptical that use-treads can every be very effective. What order are the rows in? They are probably in PRIMARY KEY order, which means that the INSERTing threads will be fighting over similar spots in the table. Is it I/O bound when it is single-threaded? If so, then there can't be any improvement with use-threads. etc. Suggest you file a bug with bugs.mysql.com. If nothing else, the documentation should say more than it does. -Original Message- From: Róbert Kohányi [mailto:kohanyi.rob...@gmail.com] Sent: Tuesday, July 24, 2012 10:52 AM To: mysql@lists.mysql.com Subject: mysqlimport --use-threads / mysqladmin processlist I'm in the middle of migrating an InnoDB database to an NDBCluster. I use mysqldump to first create two dumps, the first one contains only the database schema, the second one contains only tab delimited data (via mysqldump --tab). I edit my InnoDB schema here and there (ENGINE=InnoDB to ENGINE=NDB, etc.) import it and after this I import the InnoDB data *as is* using mysqlimport. I use it like this: mysqlimport --local --use-threads=4 db dir/*.txt (dir of course cotains the tab delimited data I dumped before.) The import starts, and I check its progress via mysqladmin, like this: mysqladmin --sleep=1 processlist this is what I see: http://pastebin.com/raw.php?i=M23fWVjc Only a single process seems to be loading my data. Is this what I *should* see, or, in my case using 4 threads, should I see four processes? I'm not asking which one will be faster, I'm just simply confused because I don't know what to expect. If I start four different mysqlimport processes, each one importing different files, then I can see four different process in the mysql processlist. If it's matters, here is my server version (I use the official binaries). Server version: 5.5.25a-ndb-7.2.7-gpl MySQL Cluster Community Server (GPL) Regards, Kohányi Róbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
mysqlimport --use-threads / mysqladmin processlist
I'm in the middle of migrating an InnoDB database to an NDBCluster. I use mysqldump to first create two dumps, the first one contains only the database schema, the second one contains only tab delimited data (via mysqldump --tab). I edit my InnoDB schema here and there (ENGINE=InnoDB to ENGINE=NDB, etc.) import it and after this I import the InnoDB data *as is* using mysqlimport. I use it like this: mysqlimport --local --use-threads=4 db dir/*.txt (dir of course cotains the tab delimited data I dumped before.) The import starts, and I check its progress via mysqladmin, like this: mysqladmin --sleep=1 processlist this is what I see: http://pastebin.com/raw.php?i=M23fWVjc Only a single process seems to be loading my data. Is this what I *should* see, or, in my case using 4 threads, should I see four processes? I'm not asking which one will be faster, I'm just simply confused because I don't know what to expect. If I start four different mysqlimport processes, each one importing different files, then I can see four different process in the mysql processlist. If it's matters, here is my server version (I use the official binaries). Server version: 5.5.25a-ndb-7.2.7-gpl MySQL Cluster Community Server (GPL) Regards, Kohányi Róbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: To get Processlist and Status of MySQL ?
I found some tools to capture the MySQL processlist, log errors, etc. MySQLReport http://hackmysql.com/mysqlreport and mytophttp://jeremy.zawodny.com/mysql/mytop/apps. My goal is to grab the logs of mysql including the processlist to help me assist in diagnosis of faults. What tool/apps do you use? Thanks. James On Tue, May 18, 2010 at 5:06 AM, Raj Shekhar rajl...@rajshekhar.net wrote: In infinite wisdom James Corteciano ja...@linux-source.org wrote: [1 text/plain; ISO-8859-1 (7bit)] Hi All, What is the other way to get the *processlist* and *status* of mysql server on event that the mysql server cannot be able to reach due to hung or crashed? GDB http://poormansprofiler.org/ -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ja...@linux-source.org
Re: To get Processlist and Status of MySQL ?
If your mysql server is hung, crashed or otherwise unreacheable (as you first posted), you can't connect to it and request it's status and processlist - it's threaded and thus all inline. You could use GDB or something similar to go look in the execution stack, as Raj said, and if you have full query logging enabled, you can parse your logs and see what was happening at the time of the crash - although I suspect that only completed queries get logged, so the one causing the crash may not be in there. Generally, it's a good idea to use Munin, Cacti or similar tools to graph performance data all the time, so you can have a post-mortem look at stuff like did the number of connections increase in the moments before ? On Tue, May 18, 2010 at 9:28 AM, James Corteciano ja...@linux-source.orgwrote: I found some tools to capture the MySQL processlist, log errors, etc. MySQLReport http://hackmysql.com/mysqlreport and mytophttp://jeremy.zawodny.com/mysql/mytop/apps. My goal is to grab the logs of mysql including the processlist to help me assist in diagnosis of faults. What tool/apps do you use? Thanks. James On Tue, May 18, 2010 at 5:06 AM, Raj Shekhar rajl...@rajshekhar.net wrote: In infinite wisdom James Corteciano ja...@linux-source.org wrote: [1 text/plain; ISO-8859-1 (7bit)] Hi All, What is the other way to get the *processlist* and *status* of mysql server on event that the mysql server cannot be able to reach due to hung or crashed? GDB http://poormansprofiler.org/ -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ja...@linux-source.org -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: To get Processlist and Status of MySQL ?
In infinite wisdom James Corteciano ja...@linux-source.org wrote: [1 text/plain; ISO-8859-1 (7bit)] Hi All, What is the other way to get the *processlist* and *status* of mysql server on event that the mysql server cannot be able to reach due to hung or crashed? GDB http://poormansprofiler.org/ -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
To get Processlist and Status of MySQL ?
Hi All, What is the other way to get the *processlist* and *status* of mysql server on event that the mysql server cannot be able to reach due to hung or crashed? Cheers, James
Re: Getting more info from show full processlist
I also want to know the answer to this question.If it's for me.I'll scan all the source code manually.But it's too complicated. On Tue, Oct 7, 2008 at 1:02 AM, Olaf Stein [EMAIL PROTECTED] wrote: It specifies the query in the Info field. In your case the connection is sleeping, nothing is being executed at the moment, therefore info is NULL Olaf On 10/6/08 11:38 AM, MaBa.listas [EMAIL PROTECTED] wrote: Hello, reading the MySQL documentation I've run into the show full processlist\G command. In my case I've got an output similar to this: -- --- mysql show full processlist\G *** 1. row *** Id: 30127 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show full processlist *** 2. row *** Id: 30399 User: root Host: localhost db: this would be my DB Command: Sleep Time: 0 State: Info: NULL -- --- What I'd like to know is how to get more info about the query identified with 30399 (in this example). I mean, the exact query, like SELECT pr1, pr2 FROM someDB. Thanks Matias - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Getting more info from show full processlist
One way to do it (maybe the only way for selects) is to use the general log. The Id number, 30399, is the thread id. You can look up the thread id in the general log and see the entire command. But you have to have the general log running. If the command changes data (update, delete, insert) then it'll be in the bin log with the thread id. If you know what time the command was executed, you can use the --start-datetime option of mysqlbinlog. But only the general log keeps selects. On Mon, Oct 6, 2008 at 10:38 AM, MaBa.listas [EMAIL PROTECTED]wrote: Hello, reading the MySQL documentation I've run into the show full processlist\G command. In my case I've got an output similar to this: - mysql show full processlist\G *** 1. row *** Id: 30127 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show full processlist *** 2. row *** Id: 30399 User: root Host: localhost db: this would be my DB Command: Sleep Time: 0 State: Info: NULL - What I'd like to know is how to get more info about the query identified with 30399 (in this example). I mean, the exact query, like SELECT pr1, pr2 FROM someDB. Thanks Matias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Getting more info from show full processlist
Hello, reading the MySQL documentation I've run into the show full processlist\G command. In my case I've got an output similar to this: - mysql show full processlist\G *** 1. row *** Id: 30127 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show full processlist *** 2. row *** Id: 30399 User: root Host: localhost db: this would be my DB Command: Sleep Time: 0 State: Info: NULL - What I'd like to know is how to get more info about the query identified with 30399 (in this example). I mean, the exact query, like SELECT pr1, pr2 FROM someDB. Thanks Matias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting more info from show full processlist
It specifies the query in the Info field. In your case the connection is sleeping, nothing is being executed at the moment, therefore info is NULL Olaf On 10/6/08 11:38 AM, MaBa.listas [EMAIL PROTECTED] wrote: Hello, reading the MySQL documentation I've run into the show full processlist\G command. In my case I've got an output similar to this: -- --- mysql show full processlist\G *** 1. row *** Id: 30127 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show full processlist *** 2. row *** Id: 30399 User: root Host: localhost db: this would be my DB Command: Sleep Time: 0 State: Info: NULL -- --- What I'd like to know is how to get more info about the query identified with 30399 (in this example). I mean, the exact query, like SELECT pr1, pr2 FROM someDB. Thanks Matias - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Processlist full of Opening tables
Thanks for the reply. It helped alot, since I did not know where to look. I read the documentation regarding the issue and some more pages google turned up for me. I did increase my table cache to 16k and my open files are at 30k. I do run debian etch 32-bit. How would I determine what reasonable means for open files are on my system? I have 4GB Ram and a 2.6 Ghz DualCore. What also interests me is my key buffer. I have it set to 768MB: mysql show status like key%; ++--+ | Variable_name | Value| ++--+ | Key_blocks_not_flushed | 0| | Key_blocks_unused | 661370 | | Key_blocks_used| 40169| | Key_read_requests | 22935142 | | Key_reads | 65152| | Key_write_requests | 1941899 | | Key_writes | 162770 | ++--+ 7 rows in set (0.00 sec) This would mean the following: 65152 Key_reads / 22935142 Key_read_requests * 100 = 0.29 % I think this would be an reasonable value. Am I right? Regards, Samy Brent Baisley schrieb: The problem is your table cache setting. MySQL will only keep a certain amount of tables open and ready for access. Once that limit is hit, if a table that is not already open needs to be accessed, it needs to close a table to make room for opening the new table. This doesn't mean you can just set the table cache size really high. The operating system has limits as to how many file handles it can have open at one time. In mysql type this: show status like open% Your Opened_tables number is probably huge. That's how many times mysql had to open a table for access. Meaning the table wasn't opened already. The Open_tables is how many tables MySQL will keep open (table cache). You need to increase this number using the table_cache variable. set global table_cache=#; The Open_files number from the show status command is important, make sure that stays within limits of your operating system. On Nov 12, 2007 5:09 PM, Samuel Vogel [EMAIL PROTECTED] wrote: Hey guys, I do run MySQL on a high traffic Server with approximately 10k databases. Since some time MySQL is has become very sluggish. When I look at my processlist it shows more than 25 processes (sometimes of the same table) with status Opening tables. Some processes also show closing tables. Since I am running I shared hosting environment, I can not examine the situation to such extent, where I could see if table locks are involved. But it does not seem to be the case, since it does not appear for just some users, but for everybody. Also when I run a simple select query it takes more than 2 sec. I guess MySQL is trying to tell me, that my hard drive is too slow. How could I ease this situation ? My key_buffer is set to 1Gb of my 4Gb system memory. Other values are tweaked as well, but I do not think the matter in that case. Would partitioning the disk as Raid 0 ease the situation? What other places do I have to look at, to further narrow down the problem? Regards, Samy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Processlist full of Opening tables
Hey guys, I do run MySQL on a high traffic Server with approximately 10k databases. Since some time MySQL is has become very sluggish. When I look at my processlist it shows more than 25 processes (sometimes of the same table) with status Opening tables. Some processes also show closing tables. Since I am running I shared hosting environment, I can not examine the situation to such extent, where I could see if table locks are involved. But it does not seem to be the case, since it does not appear for just some users, but for everybody. Also when I run a simple select query it takes more than 2 sec. I guess MySQL is trying to tell me, that my hard drive is too slow. How could I ease this situation ? My key_buffer is set to 1Gb of my 4Gb system memory. Other values are tweaked as well, but I do not think the matter in that case. Would partitioning the disk as Raid 0 ease the situation? What other places do I have to look at, to further narrow down the problem? Regards, Samy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
processlist
Hi All, I have some doudts in db connections. Please clarify if u know. I am using MySQL 5 version, while my program(Java Program with threads) is running, some times i won't get DB connection with in expected time. When ever i type show processlist on mysql prompt, it is showing 180+ connections estableshed but almost all are in sleep stage only. Is these many opened connections are delaying a new connection? if yes is there any process to close these sleeping connections?
Re: processlist
the problem is not on the MySQL side.. the problem is in the connection management of your Java application... Are you using connection pooling? Nothing on the server is going to close those connections for you (although they will go stal eventually), that is up to your client application. - michael On 3/15/07, balaraju mandala [EMAIL PROTECTED] wrote: Hi All, I have some doudts in db connections. Please clarify if u know. I am using MySQL 5 version, while my program(Java Program with threads) is running, some times i won't get DB connection with in expected time. When ever i type show processlist on mysql prompt, it is showing 180+ connections estableshed but almost all are in sleep stage only. Is these many opened connections are delaying a new connection? if yes is there any process to close these sleeping connections? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show full processlist question
Hi, I want to get a full list of all queries being run at a specific moment... If I look at mytop output, I'm hitting 500 queries per second. If I do show full processlist though, I only see 1 query as output - it's 'show full processlist' itself! And a few sleeping queries sometimes. How does this work? How can I get a full list of queries running within, say, a particular second? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show full processlist question
Peter, SHOW PROCESSLIST is a moment-in-time snapshot of connections and their activities. It shows you what all is going on at the instant you issue the command; it does not show you recent commands (even those that happened a second ago). Many of your queries are very likely so fast that you're just not seeing them in SHOW PROCESSLIST - if they took much time, you wouldn't be getting that 500-per-second number. Its purpose is more to show you what is going on with queries that don't run so quickly, or when your database experiences a deadlock. If you do really need to get a list of all queries that ran in a particular second, you might be able to work some magic with MySQL's general log aka the query log. You have to specifically enable that, and it is a record of ALL queries including data inserts and updates, so it can become a large file quickly - beware. Dan Peter Van Dijck wrote: Hi, I want to get a full list of all queries being run at a specific moment... If I look at mytop output, I'm hitting 500 queries per second. If I do show full processlist though, I only see 1 query as output - it's 'show full processlist' itself! And a few sleeping queries sometimes. How does this work? How can I get a full list of queries running within, say, a particular second? Thanks! Peter -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show full processlist question
Hi I didn´t find it in the documentation I would like to know if the collumn time in the command show full processlist show the seconds the query is taking to execute!? Thank´s in advance - Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador do Yahoo! agora.
maximum id of processlist
Hi, I noticed that the number of id when I do show processlist is getting bigger and bigger. Now it is about 444,466,168. What is the maximum number, and what will happen if it runs out of number? --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum id of processlist
Hello. As said Guilhem Bichot. It will go up to 232 or 264 depending on your architecture (32 or 64 bit) then it will go down to 0 and grow again. There is nothing wrong with having a thread of id 0 (I had tested). I've looked though code of mysqld.cc, but haven't found any checks. The value of thread_id only increments: thd-thread_id=thread_id++; Is this a problem? No, the only problem which could happen is if thread 1 still exists when a new thread of number 232+1 == 1 is created. But that sounds unlikely. Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I noticed that the number of id when I do show processlist is getting bigger and bigger. Now it is about 444,466,168. What is the maximum number, and what will happen if it runs out of number? --bk -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show processlist state value 'statistics'
Does anyone know what is the meaning of value 'statistics' in state column of show processlist? It is displaying this state during a SELECT query. I noticed lot of them in DB while our application is being run in a stress mode. This is not documented in Mysql documentation for show processlist TIA ananth
RE: show processlist state value 'statistics'
I have seen a similar option in PHPMyAdmin which says 'Enable Statistics' I suppose when the status shows statistics, mysql is just updating the statistics in there for the query just run? -Original Message- From: Ananth Reddy [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 11:13 PM To: [EMAIL PROTECTED] Subject: show processlist state value 'statistics' Does anyone know what is the meaning of value 'statistics' in state column of show processlist? It is displaying this state during a SELECT query. I noticed lot of them in DB while our application is being run in a stress mode. This is not documented in Mysql documentation for show processlist TIA ananth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting custom information in processlist
Thank you very much for your reply, Jeremy. I have been searching high and low for some sort of solution in MySQL, now I can stop. In answer to your question other systems simply allow certain attributes to be set at connection time, e.g. application_name, host_name, etc. So I normally post_fix to the application_name a short date-time value which allows me to distinguish connections in the list of processes on the server. Cheers, Naran Naran Hirani (Database/Software Specialist) MRC Rosalind Franklin Centre for Genomics Research Wellcome Trust Genome Campus, Hinxton, Cambridge, CB10 1SB, UK __ Tel: +44 (0)1223 494536 Fax: +44 (0)1223 494512 Web: www.rfcgr.mrc.ac.uk E-mail: [EMAIL PROTECTED] Jeremy Zawodny wrote: On Thu, Aug 05, 2004 at 12:36:55PM +0100, Naran Hirani wrote: Hi, I'm using a single shared user-login for a web-based application to my mysql database - is there a way of including some information at connect time or during processing that would show up when issuing `show processlist'? Only if you prefixed each query with a comment: /* foo #3 */ SELECTL * FROM world ORDER BY... But not at connect time. Basically, I need to able to distinguish potentially multiple connections with the same login and process information some how. This sort of thing is possible in other SQL engines so probably should be in MySQL too. Interesting. How do other systems handle this? Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting custom information in processlist
On Thu, Aug 05, 2004 at 12:36:55PM +0100, Naran Hirani wrote: Hi, I'm using a single shared user-login for a web-based application to my mysql database - is there a way of including some information at connect time or during processing that would show up when issuing `show processlist'? Only if you prefixed each query with a comment: /* foo #3 */ SELECTL * FROM world ORDER BY... But not at connect time. Basically, I need to able to distinguish potentially multiple connections with the same login and process information some how. This sort of thing is possible in other SQL engines so probably should be in MySQL too. Interesting. How do other systems handle this? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting custom information in processlist
Oracle has a procedure called DBMS_APPLICATION_INFO.SET_APPLICATION_INFO that allows you to specify up to 64k of addtional information about the current connection. It doesn't have any way to specify this information at connect time though. The data can be accessed in Oracle through the V$SESSION system view, or through userenv('CLIENT_INFO') Something in mysql that would be similar and just as easy to implement would be: create table process_info(ThreadID int, Information text, primary key (ThreadID)); then in each connection do: replace into process_info values (CONNECTION_ID(), 'Connection details here'); Add in a little cron job that removes old values from the process_info table nightly and that should do the trick. On Tue, 10 Aug 2004 15:24:34 -0700, Jeremy Zawodny [EMAIL PROTECTED] wrote: On Thu, Aug 05, 2004 at 12:36:55PM +0100, Naran Hirani wrote: Hi, I'm using a single shared user-login for a web-based application to my mysql database - is there a way of including some information at connect time or during processing that would show up when issuing `show processlist'? Only if you prefixed each query with a comment: /* foo #3 */ SELECTL * FROM world ORDER BY... But not at connect time. Basically, I need to able to distinguish potentially multiple connections with the same login and process information some how. This sort of thing is possible in other SQL engines so probably should be in MySQL too. Interesting. How do other systems handle this? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grepping data from show processlist in more elegant way
Hello, I have wrote a small program which's main purpose is to do change master to, slave start and after a transfer of data - slave stop. But after slave start i need to know when the transfer ends - to do this I could parse the mysql_list_processes() output for a string Has read all relay log; waiting for the I/O slave thread to update it but it is not too elegant, is there a way to do it in more elegant way? Or maybe mysql do not permit to do slave stop before the end of transmission? -- Irek Slonina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW PROCESSLIST State Locked - what does this mean?
Hi, Currently, I have a situation where an app makes connections (via JDBC) to a mysql server, 50 connections at once, and everything just becomes super-slow. For instance, a SELECT that should take 0.01 sec takes several minutes. SHOW PROCESSLIST says that these threads that are connections from the app are in a state Locked. The mysql manual doesn't explain this option, or even list it as a possibility (maybe I'm looking at the wrong place: http://dev.mysql.com/doc/mysql/en/SHOW_PROCESSLIST.html) The other question is what one can do about this, to prevent this locking, or even diagnose it. Thanks for your help. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW PROCESSLIST displays incorrect slave latency in MySQL 3.23
Description: When I do 'SHOW PROCESSLIST' on my server currently, the line for 'system user' is as follows: ++-+--+-+-++---+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+--+-+-++---+--+ | 495801 | system user | none | NULL| Connect | 4294967211 | Reading master update | NULL | A few minutes before, and for a number of days, it had been displaying very low ( 100s) numbers. Subtracting the number from 2 ** 32 gives what looks like a sensible figure, so this may be a signed/unsigned bug. How-To-Repeat: Hard to say; MySQL has been up for approaching 64 days - not sure if that's significant. Doing SLAVE STOP; SLAVE START; has no effect. Fix: Unknown (except to work around it as shown above). Submitter-Id: submitter ID Originator:root Organization: NewsNow Publishing Limited MySQL support: none Synopsis: SHOW PROCESSLIST displays incorrect slave latency in MySQL 3.23 Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.49 (Source distribution) (actually debian 'woody' stable distribution package) Environment: Machine: Dell PowerEdge 2650 2xPIV Xeon 2.6 GHz w. PERC 3/Di RAID controller OS: Debian GNU/Linux 'woody/stable' distribution, with custom-compiled kernel 2.4.24. System: Linux craven 2.4.24dell-poweredge-2650 #1 SMP Tue Jan 6 13:09:17 GMT 2004 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 24 2003 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Apr 8 2003 /lib/libc-2.2.5.so -rw-r--r--1 root root 2391002 Apr 8 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 8 2003 /usr/lib/libc.so Configure command: ./configure --prefix=/usr --exec-prefix=/usr --libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info --mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler --with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile --with-raid --enable-thread-safe-client --without-readline --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all Output from 'mysqladmin extended-status': +--++ | Variable_name| Value | +--++ | Aborted_clients | 1451 | | Aborted_connects | 83 | | Bytes_received | 2448140052 | | Bytes_sent | 2623702023 | | Com_admin_commands | 0 | | Com_alter_table | 14 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 3 | | Com_change_master| 1 | | Com_check| 0 | | Com_commit | 0 | | Com_create_db| 1 | | Com_create_function | 3 | | Com_create_index | 0 | | Com_create_table | 2 | | Com_delete | 822956 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush| 2 | | Com_grant| 2 | | Com_insert | 444024318 | | Com_insert_select| 21493 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_purge| 0 | | Com_rename_table | 2 | | Com_repair | 0 | | Com_replace | 7483359| | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_select | 256096442 | | Com_set_option | 6 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 28 | | Com_show_fields | 2306 | | Com_show_grants | 0
Processlist
Occasionally, when looking at the processlist using MySQLAdmin, I see entries 'unauthenticated user' 'reading from net' Would some kind person tell me what this means, and if I'm in danger of having data compromised? Thanks Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Processlist : state Writing to net
rekin's janky wrote: Hi listers, Using MySQL 4.0.15-max-debug on Windows 2000, I am working with perlscripts. When one of my program runs, I have an unexpected long time for one query which take at least 10 min (in the best case, but it stayed blocked most of the time) instead of 10 sec when the query is on the mysql command line (a SELECT query about 81000 lines). The command SHOW PROCESSLIST shows that the state of that command is Writing to net ! I don't see what that means and how I can find a solution ? Anybody can help me ? Try $sth-{mysql_use_result} = 1; before $sth-execute() The problem, I think, is that your client system starts swapping hard while trying to allocate enough memory to store 81,000 rows. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Processlist : state Writing to net
Hi listers, Using MySQL 4.0.15-max-debug on Windows 2000, I am working with perlscripts. When one of my program runs, I have an unexpected long time for one query which take at least 10 min (in the best case, but it stayed blocked most of the time) instead of 10 sec when the query is on the mysql command line (a SELECT query about 81000 lines). The command SHOW PROCESSLIST shows that the state of that command is Writing to net ! I don't see what that means and how I can find a solution ? Anybody can help me ? best regards, Rekin's - Yahoo! Mail : votre e-mail personnel et gratuit qui vous suit partout ! Créez votre Yahoo! Mail Dialoguez en direct avec vos amis grâce à Yahoo! Messenger !
mysqladmin processlist and pid
hi listers how would i determine the association between the id from mysqladmin processlist to a pid? what i'm after is that i notice some of the mysql threads have a high cpu utilization. i see this using top (on a linux box). i would like to know which user/program is responsible for tuning purposes. i can use mysqladmin processlist and it gives me a list (including an id) of what processes are running but how do i tie this in with unix' pid? tks tom
processlist: state is Opening table
filter: select, mysql Solaris 3.23.40 connections using perl, jdbc, odbc. Yesterday, we experienced a rare mysqld failure where all connections where in the state=Opening table. Normally our 200+ connections are in state=sleep. The Time field from command show processlist showed each connection accumulating time. This is not the case when a connection is in the sleep mode. The number of connections was nearly 400 when we normally have over 200. The threads running about equal to number of connections. The threads created was at 1240 - normally is ~ number of connections. When I attempted a mysql.server stop, it timed out. I had to use a kill -9. I used a combination of myismcheck (db off) and check table (db on) to verify that all tables were ok. I did repair tables that were OK but terminated improperly probably due to the kill -9 command. The info column from the show processlist showed the command the user or program was executing. All of the connections were accounted for including the extra 170 connections. These extra connections accumulated from programs between the time server failed and time of restart. To me, it appears that the mysqld allowed connections, but then froze with all connections executing at the state Opening table. This is verified from programs making connections without failing. It appears all were waiting for mysqld to process the query. Just in case this matters, there are ~75 tables in this installation. On a normal day, open_tables=149 and table cache is set to 256. Prior to restarting mysqld, opened_tables was at 831. Any suggestions welcome. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqladmin processlist and pid
Hi Tom, You can't. MySQL's own thread ids are sequential. The OS pids are random. There's no connection between them. Besides, mysqld is really only running in a single real process, it's just that LinuxThreads shows each thread as a process. Matt - Original Message - From: Tom Roos Sent: Tuesday, March 09, 2004 6:05 AM Subject: mysqladmin processlist and pid hi listers how would i determine the association between the id from mysqladmin processlist to a pid? what i'm after is that i notice some of the mysql threads have a high cpu utilization. i see this using top (on a linux box). i would like to know which user/program is responsible for tuning purposes. i can use mysqladmin processlist and it gives me a list (including an id) of what processes are running but how do i tie this in with unix' pid? tks tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Processlist command
All: I recently discovered the Show Processlist statement which is great. My question is this... One the process completes it falls off this list. Is there another command that will show me the run time of the processes that have completed? Is this info in a log? If so, which one? Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible values in COMMAND for SHOW PROCESSLIST
Hi, Does anyone know what the possible values for column Command are when doing a SHOW PROCESSLIST? All I'm getting so far, is Query, Killed and Sleep. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible values in COMMAND for SHOW PROCESSLIST
Checking table Closing tables connect out Copying to tmp table on disk Creating tmp table Deleting tmp table Deleting from main table Deleting from reference tables Flushing tables Killed Sending data Sorting for group Sorting for order Opening tables Removing duplicates Reopen table Repair by sorting Repair with keycache Searching rows for update Sleeping System lock Upgrading lock Updating User lock Waiting for tables Waiting for handler insert ... These are ones that I know of. Original Message On 2/18/04, 9:26:21 AM, Martijn Tonies [EMAIL PROTECTED] wrote regarding Possible values in COMMAND for SHOW PROCESSLIST: Hi, Does anyone know what the possible values for column Command are when doing a SHOW PROCESSLIST? All I'm getting so far, is Query, Killed and Sleep. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible values in COMMAND for SHOW PROCESSLIST
Hi, Are you sure about this? According to http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html these are the values of State With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Checking table Closing tables connect out Copying to tmp table on disk Creating tmp table Deleting tmp table Deleting from main table Deleting from reference tables Flushing tables Killed Sending data Sorting for group Sorting for order Opening tables Removing duplicates Reopen table Repair by sorting Repair with keycache Searching rows for update Sleeping System lock Upgrading lock Updating User lock Waiting for tables Waiting for handler insert ... These are ones that I know of. Original Message On 2/18/04, 9:26:21 AM, Martijn Tonies [EMAIL PROTECTED] wrote regarding Possible values in COMMAND for SHOW PROCESSLIST: Hi, Does anyone know what the possible values for column Command are when doing a SHOW PROCESSLIST? All I'm getting so far, is Query, Killed and Sleep. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible values in COMMAND for SHOW PROCESSLIST
My confusion, I thought that was what you were asking for. Original Message On 2/18/04, 10:01:24 AM, Martijn Tonies [EMAIL PROTECTED] wrote regarding Re: Possible values in COMMAND for SHOW PROCESSLIST: Hi, Are you sure about this? According to http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html these are the values of State With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Checking table Closing tables connect out Copying to tmp table on disk Creating tmp table Deleting tmp table Deleting from main table Deleting from reference tables Flushing tables Killed Sending data Sorting for group Sorting for order Opening tables Removing duplicates Reopen table Repair by sorting Repair with keycache Searching rows for update Sleeping System lock Upgrading lock Updating User lock Waiting for tables Waiting for handler insert ... These are ones that I know of. Original Message On 2/18/04, 9:26:21 AM, Martijn Tonies [EMAIL PROTECTED] wrote regarding Possible values in COMMAND for SHOW PROCESSLIST: Hi, Does anyone know what the possible values for column Command are when doing a SHOW PROCESSLIST? All I'm getting so far, is Query, Killed and Sleep. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible values in COMMAND for SHOW PROCESSLIST
On Wed, Feb 18, 2004 at 04:26:21PM +0100, Martijn Tonies wrote: Hi, Does anyone know what the possible values for column Command are when doing a SHOW PROCESSLIST? A table in Chapter 1 of High Performance MySQL[*] Binlog Dump Change user Connect Connect Out Create DB Debug Delayed_insert Drop DB Field List Init DB Kill Ping Processlist Query Quit Refresh Register Slave Shutdown Sleep Statistics [*] Pre-order today. See http://highperformancemysql.com/ -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 157 days, processed 1,902,977,977 queries (139/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW PROCESSLIST
Hi All, I executed a SHOW PROCCESSLIST command and it returned me 39 rows which 21 of than show Sleep in the column Command. What does this tell me? Thank´s, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
On Wed, Feb 18, 2004 at 04:01:57PM -0300, Ronan Lucio wrote: Hi All, I executed a SHOW PROCCESSLIST command and it returned me 39 rows which 21 of than show Sleep in the column Command. What does this tell me? 21 threads are idle. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 157 days, processed 1,905,944,239 queries (139/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
Jeremy, On Wed, Feb 18, 2004 at 04:01:57PM -0300, Ronan Lucio wrote: Hi All, I executed a SHOW PROCCESSLIST command and it returned me 39 rows which 21 of than show Sleep in the column Command. What does this tell me? 21 threads are idle. But, what could make so many threads get idle? Shouldn´t the queries just be executed, return the results and be closed? Does this mean some kind of congestion or it is normal? Thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible values in COMMAND for SHOW PROCESSLIST
Hi Jeremy, On Wed, Feb 18, 2004 at 04:26:21PM +0100, Martijn Tonies wrote: Hi, Does anyone know what the possible values for column Command are when doing a SHOW PROCESSLIST? A table in Chapter 1 of High Performance MySQL[*] Thanks! With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
Hi, I executed a SHOW PROCCESSLIST command and it returned me 39 rows which 21 of than show Sleep in the column Command. What does this tell me? 21 threads are idle. But, what could make so many threads get idle? Connecting, but don't doing anything. Shouldn´t the queries just be executed, return the results and be closed? If you don't close your connection, what do you expect? Does this mean some kind of congestion or it is normal? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
Martijn, 21 threads are idle. But, what could make so many threads get idle? Connecting, but don't doing anything. Shouldn´t the queries just be executed, return the results and be closed? If you don't close your connection, what do you expect? Does this mean some kind of congestion or it is normal? It´s a website, so it´s possible that the users closed his browsers before the query finishes? Thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
Hi Ronan, 21 threads are idle. But, what could make so many threads get idle? Connecting, but don't doing anything. Shouldn´t the queries just be executed, return the results and be closed? If you don't close your connection, what do you expect? Does this mean some kind of congestion or it is normal? It´s a website, so it´s possible that the users closed his browsers before the query finishes? I don't know - that all depends on how you coded your back-end. For example: if I write a cgi-bin in Delphi, and a user closed the browser while waiting for a result, I'm sure the cgi-bin gets cleaned up and I can close the connection in my executable. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show processlist
Hi, show processlist displays processes with state statistics, what does this status means. Documentation doesn't say much A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show processlist
Hi Andrius, Yes, I've wondered about this before too, but wasn't exactly sure what it meant either. :-) So I just decided to see where this state is set in the code, and it's when the make_join_statistics() function is called. I think that function checks key distribution and things to see which index to use, if any, when looking up rows in a table. Now we both know. ;-) Hope that helps. Matt - Original Message - From: Andrius Jakas Sent: Thursday, December 18, 2003 6:08 AM Subject: show processlist Hi, show processlist displays processes with state statistics, what does this status means. Documentation doesn't say much A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query not visible by sh processlist
Hi All, Is there a possibility that a certain query that is executing in mysql by application is not visible by a show processlist command. As happened in my database a query was taking number of CPU cycles but when seen through the show processlist command nothing was displayed. shasan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query not visible by sh processlist
Sohail Hasan [EMAIL PROTECTED] wrote: Is there a possibility that a certain query that is executing in mysql by application is not visible by a show processlist command. As happened in my database a query was taking number of CPU cycles but when seen through the show processlist command nothing was displayed. If the user dosn't have SUPER privilege he can see only his own threads. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Meaning of Column time in Show Processlist
First of all, thank you Jeremy for your answer. You said: It's the amount of time that thread has been in its current state. Is it the current state column value (which is in fact nothing or the text of the running SQL request) or the current command column value (sleeping, opening table, closing table, ...) that is used for the amount of time ? What happens to the thread if the value of time is greater than interactive timeout or wait timeout ? Regards, Marc Mechain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Meaning of Column time in Show Processlist
Thanks very much. Marc. -Message d'origine- De : Jeremy Zawodny [mailto:[EMAIL PROTECTED] Envoyé : mercredi 13 août 2003 17:01 À : Mechain Marc Cc : [EMAIL PROTECTED] Objet : Re: Meaning of Column time in Show Processlist On Wed, Aug 13, 2003 at 03:26:24PM +0200, Mechain Marc wrote: First of all, thank you Jeremy for your answer. You said: It's the amount of time that thread has been in its current state. Is it the current state column value (which is in fact nothing or the text of the running SQL request) or the current command column value (sleeping, opening table, closing table, ...) that is used for the amount of time ? Both, really. I can't think of a time when one changes but the other does not. What happens to the thread if the value of time is greater than interactive timeout or wait timeout ? The client will be disconnected and thread closed. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 11 days, processed 431,850,739 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
I don't believe Windows services can be started with any priority types. MySQL is on it's own box with the ASP version, which works like a dream. It's simply that whenever the PHP version is used (either as a solitary website on another box or as another website on the same box), that's when mysqld goes mad. The odd thing is that the software is effectively identical between the two languages, and has simply had functions changed as and where appropriate. I have also used the programming methods as used in the book PHP and MySQL Web Development too, as well as reading advice from MySQL Second Edition by Paul Dubois. I just wonder if this is a problem that is unable to be solved?! -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED] Sent: 08 August 2003 17:16 To: 'Gary Broughton'; [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time I think I see the problem. Mysql really needs to be on it's own box. It's designed to just use as much power as it can find. This is a good thing for those with dedicated machines. I don't know if there's a configuration setup that tell mysql that it's not the head honcho. Does Windows have a way to start a process (mysql) in low priority? -Original Message- From: Gary Broughton [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:20 AM To: [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql processlist sleep time
Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Has anybody any ideas on why this can be? I cannot find out how to tell what is causing the long sleep period. Many thanks Gary
RE: Mysql processlist sleep time
I think I see the problem. Mysql really needs to be on it's own box. It's designed to just use as much power as it can find. This is a good thing for those with dedicated machines. I don't know if there's a configuration setup that tell mysql that it's not the head honcho. Does Windows have a way to start a process (mysql) in low priority? -Original Message- From: Gary Broughton [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:20 AM To: [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql processlist sleep time
On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: processlist state = null
does anyone know what it means when it says state = NULL doing the show processlist? The client is connected but not doing anything. It could be either preparing to send a query, between queries, or finished with its last query and hasn't disconnected yet. -M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Meaning of Column time in Show Processlist
Jeremy Zawodny wrote: What happens to the thread if the value of time is greater than interactive timeout or wait timeout ? The client will be disconnected and thread closed. That's what I thought was supposed to happen, but we constantly wind up with sleeping connections longer than these values: +---+---+--++-++---+--+ | Id| User | Host | db | Command | Time | State | Info | +---+---+--++-++---+--+ | 1162 | carpenter | | a | Sleep | 40937 | | NULL | | 1818 | milberg | | b | Sleep | 45634 | | NULL | | 10460 | kokaz | | c | Sleep | 130282 | | NULL | +---+---+--++-++---+--+ +--+---+ | Variable_name| Value | +--+---+ | interactive_timeout | 600 | | wait_timeout | 600 | +--+---+ I think these are clients who have left either the mysql command line client open on their machine or have remained logged in via phpmyadmin. Eventually they go away, but that seems to be after they log out. Shouldn't these connections be shut down, or is there some other variable or option that I need to check. Thanks, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Meaning of Column time in Show Processlist
On Wed, Aug 13, 2003 at 03:26:24PM +0200, Mechain Marc wrote: First of all, thank you Jeremy for your answer. You said: It's the amount of time that thread has been in its current state. Is it the current state column value (which is in fact nothing or the text of the running SQL request) or the current command column value (sleeping, opening table, closing table, ...) that is used for the amount of time ? Both, really. I can't think of a time when one changes but the other does not. What happens to the thread if the value of time is greater than interactive timeout or wait timeout ? The client will be disconnected and thread closed. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 11 days, processed 431,850,739 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Meaning of Column time in Show Processlist
On Tue, Aug 12, 2003 at 11:43:50AM +0200, Mechain Marc wrote: I would like to know (if possible, there is no explanation in the documentation) the exact meanning of the column time in the show processlist command. It's the amount of time that thread has been in its currnet state. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 11 days, processed 368,692,693 queries (386/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Meaning of Column time in Show Processlist
I would like to know (if possible, there is no explanation in the documentation) the exact meanning of the column time in the show processlist command. Why is it sometime so hight ? When is it reinitialize (and why) ? Is there any correlation between the time column and the variables wait timeout and interactive timeout ? Regards, Marc Mechain Atos Origin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
exactly, NULL is a good thing. It means that php isn't constantly tearing down sessions and starting new ones. The connect process is virtually always the longest step of the query (except the big queries, but the goal is to keep them minimized in an application and use good design, indexes, etc.) -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 5:55 PM To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: processlist state = null
On Thu, Aug 07, 2003 at 05:42:12PM -0400, Bernd Jagla wrote: Dear list, does anyone know what it means when it says state = NULL doing the show processlist? Usually you see this when the 'command' is sleep. That means the thread is idle. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,707,492 queries (398/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
processlist state = null
Dear list, does anyone know what it means when it says state = NULL doing the show processlist? Thanks Bernd = Please note that this e-mail and any files transmitted with it may be privileged, confidential, and protected from disclosure under applicable law. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, or other use of this communication or any of its attachments is strictly prohibited. If you have received this communication in error, please notify the sender immediately by replying to this message and deleting this message, any attachments, and all copies and backups from your computer.
Re: [MYSQL] Question about 'SHOW PROCESSLIST' output columns
PAUL MENARD [EMAIL PROTECTED] wrote: I'm having an intermittent problem with a MySQL server that I have running on a Windows 2000 Advance server systems. This issue occurs every few days depending on the load of the system. Here are the details. MySQL version 3.23.42-nt (I know I need to upgrade. Soon). Machine: Windows 2000 Advanced Server 2G memory 36G disk space. I have a Web page that allows my operators to monitor various process tasks on this server. Sometimes they receive an error that PHP cannot connect to MySQL. If this happens during the day they call me. I log into the server and bring up the WinMySQLadmin 3.1 client and click on the 'Process' tab. There are a few (maybe 250 attached processes). I know there are many machines connecting to this database to feed updates from enterprise applications. So I bumped up the 'max_connections' limit to 1000. This just seems to delay the limit and the phone call. My question is in looking at the output form the 'Process' tab I see the columns 'Command' and 'Time'. In all of the rows for 'Command' the value is 90% 'Sleep'. This would tell me I have some processes out there that are not dis-connecting (right?). In the 'Time' column is see numbers ranging from 150 to 1000+. I'm guessing this is the time the connection is idle (correct?). What I would like to do is define my parameters so that these dead processes will be disconnected by MySQL after a shorter time but not sure which variable to change. Advice? You can decrease value of wait_timout variable: http://www.mysql.com/doc/en/SHOW_VARIABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL] Question about 'SHOW PROCESSLIST' output columns
Hello All, I'm having an intermittent problem with a MySQL server that I have running on a Windows 2000 Advance server systems. This issue occurs every few days depending on the load of the system. Here are the details. MySQL version 3.23.42-nt (I know I need to upgrade. Soon). Machine: Windows 2000 Advanced Server 2G memory 36G disk space. I have a Web page that allows my operators to monitor various process tasks on this server. Sometimes they receive an error that PHP cannot connect to MySQL. If this happens during the day they call me. I log into the server and bring up the WinMySQLadmin 3.1 client and click on the 'Process' tab. There are a few (maybe 250 attached processes). I know there are many machines connecting to this database to feed updates from enterprise applications. So I bumped up the 'max_connections' limit to 1000. This just seems to delay the limit and the phone call. My question is in looking at the output form the 'Process' tab I see the columns 'Command' and 'Time'. In all of the rows for 'Command' the value is 90% 'Sleep'. This would tell me I have some processes out there that are not dis-connecting (right?). In the 'Time' column is see numbers ranging from 150 to 1000+. I'm guessing this is the time the connection is idle (correct?). What I would like to do is define my parameters so that these dead processes will be disconnected by MySQL after a shorter time but not sure which variable to change. Advice? Paul
RE: SHOW PROCESSLIST
This is a know bug that has been addressed in version 4.0.13, you will need to upgrade. See http://bugs.mysql.com/bug.php?id=164 Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Shane Kirk [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 12:25 PM To: [EMAIL PROTECTED] Subject: SHOW PROCESSLIST Hi, we've got a linux server (version 4.0.12) and we seem to be having a strange issue.whenever we view the processlist, it ALWAYS shows connections coming from the localhost instead of the remote machine's name/ip. Is there any particular reason this might be happening? Viewing netstat's output from a shell resolves remote addresses just fine. So it's no doubt a MySQL configuration option I'm missing or set wrong somewhere. Any ideas? -Shane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
Are you running AIX? This problem was just addressed, and is fixed in 4.0.13 I thought it was a name resolution problem, sorry for the misdirection on whoever had this problem earlier. -Shane - Original Message - From: Shane Kirk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 12:24 PM Subject: SHOW PROCESSLIST Hi, we've got a linux server (version 4.0.12) and we seem to be having a strange issue.whenever we view the processlist, it ALWAYS shows connections coming from the localhost instead of the remote machine's name/ip. Is there any particular reason this might be happening? Viewing netstat's output from a shell resolves remote addresses just fine. So it's no doubt a MySQL configuration option I'm missing or set wrong somewhere. Any ideas? -Shane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: SHOW PROCESSLIST
Thanks, Mike. I should have checked the bug database first. I thought for sure we had it working right before, but I think someone might have snuck a newer version of the MySQL on the server without telling me. :-) Oh well, go figure. -Shane ---Original Message--- From: Mike Hillyer [EMAIL PROTECTED] Sent: 06/17/03 02:37 PM To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: SHOW PROCESSLIST This is a know bug that has been addressed in version 4.0.13, you will need to upgrade. See http://bugs.mysql.com/bug.php?id=164 Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Shane Kirk [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 12:25 PM To: [EMAIL PROTECTED] Subject: SHOW PROCESSLIST Hi, we've got a linux server (version 4.0.12) and we seem to be having a strange issue.whenever we view the processlist, it ALWAYS shows connections coming from the localhost instead of the remote machine's name/ip. Is there any particular reason this might be happening? Viewing netstat's output from a shell resolves remote addresses just fine. So it's no doubt a MySQL configuration option I'm missing or set wrong somewhere. Any ideas? -Shane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: SHOW PROCESSLIST
Thanks, Mike. I should have checked the bug database first. I thought for sure we had it working right before, but I think someone might have snuck a newer version of the MySQL on the server without telling me. :-) Oh well, go figure. -Shane ---Original Message--- From: Mike Hillyer [EMAIL PROTECTED] Sent: 06/17/03 02:37 PM To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: SHOW PROCESSLIST This is a know bug that has been addressed in version 4.0.13, you will need to upgrade. See http://bugs.mysql.com/bug.php?id=164 Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Shane Kirk [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 12:25 PM To: [EMAIL PROTECTED] Subject: SHOW PROCESSLIST Hi, we've got a linux server (version 4.0.12) and we seem to be having a strange issue.whenever we view the processlist, it ALWAYS shows connections coming from the localhost instead of the remote machine's name/ip. Is there any particular reason this might be happening? Viewing netstat's output from a shell resolves remote addresses just fine. So it's no doubt a MySQL configuration option I'm missing or set wrong somewhere. Any ideas? -Shane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [BUG] show full processlist on AIX
On Sun, Jun 15, 2003 at 03:01:18PM +0800, Ares Liu wrote: MySQL 4.0.12 max on AIX 4.3.2 HA. When I use show full processlist on localhost, all Hosts show as localhost. but when I use show full processlist from remote, all Hosts show as remote IP. as follow: Known bug fixed in 4.0.13 (the latest version). -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 13 days, processed 436,631,307 queries (374/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[BUG] show full processlist on AIX
MySQL 4.0.12 max on AIX 4.3.2 HA. When I use show full processlist on localhost, all Hosts show as localhost. but when I use show full processlist from remote, all Hosts show as remote IP. as follow: mysql show full processlist; +-+-+-++-+--+---+---+ | Id | User| Host| db | Command | Time | State | Info | +-+-+-++-+--+---+---+ | 14 | gege| localhost:3425 | NewProduct | Sleep | 744 | | NULL | | 20 | yuheyang| localhost:3277 | NewProduct | Sleep | 4044 | | NULL | | 21 | yuheyang| localhost:3278 | NewProduct | Sleep | 4044 | | NULL | | 22 | yuheyang| localhost:3279 | NewProduct | Sleep | 4132 | | NULL | | 23 | yuheyang| localhost:3280 | NewProduct | Sleep | 4137 | | NULL | | 24 | yuheyang| localhost:3312 | NewProduct | Sleep | 1946 | | NULL | | 25 | yuheyang| localhost:3313 | NewProduct | Sleep | 1946 | | NULL | | 26 | yuheyang| localhost:3314 | NewProduct | Sleep | 1946 | | NULL | | 27 | yuheyang| localhost:3315 | NewProduct | Sleep | 1946 | | NULL | | 28 | root| localhost | NULL | Query | 0| NULL | show full processlist | | 29 | happynessrabbit | localhost:44555 | NewProduct | Sleep | 291 | | NULL | | 30 | happynessrabbit | localhost:44556 | NewProduct | Sleep | 291 | | NULL | | 31 | happynessrabbit | localhost:44557 | NewProduct | Sleep | 291 | | NULL | | 32 | happynessrabbit | localhost:44558 | NewProduct | Sleep | 291 | | NULL | | 115 | happynessrabbit | localhost:44654 | NewProduct | Sleep | 1809 | | NULL | | 116 | happynessrabbit | localhost:44655 | NewProduct | Sleep | 1801 | | NULL | | 117 | happynessrabbit | localhost:44657 | NewProduct | Sleep | 1792 | | NULL | | 118 | happynessrabbit | localhost:44658 | NewProduct | Sleep | 1781 | | NULL | | 119 | happynessrabbit | localhost:44659 | NewProduct | Sleep | 1776 | | NULL | | 131 | happynessrabbit | localhost:44671 | NewProduct | Sleep | 1715 | | NULL | | 137 | happynessrabbit | localhost:44677 | NewProduct | Sleep | 1711 | | NULL | | 156 | happynessrabbit | localhost:44696 | NewProduct | Sleep | 1603 | | NULL | | 162 | happynessrabbit | localhost:44704 | NewProduct | Sleep | 1561 | | NULL | | 172 | gege| localhost:44721 | NULL | Sleep | 17 | | NULL | +-+-+-++-+--+---+---+ 24 rows in set (0.00 sec) on remote: mysql show full processlist; +-+-+-++-+--+---+---+ | Id | User| Host| db | Command | Time | State | Info | +-+-+-++-+--+---+---+ | 14 | gege| 192.168.5.108:3425 | NewProduct | Sleep | 727 | | NULL | | 20 | yuheyang| 192.168.5.108:3277 | NewProduct | Sleep | 4027 | | NULL | | 21 | yuheyang| 192.168.5.108:3278 | NewProduct | Sleep | 4027 | | NULL | | 22 | yuheyang| 192.168.5.108:3279 | NewProduct | Sleep | 4115 | | NULL | | 23 | yuheyang| 192.168.5.108:3280 | NewProduct | Sleep | 4120 | | NULL | | 24 | yuheyang| 192.168.5.108:3312 | NewProduct | Sleep | 1929 | | NULL | | 25 | yuheyang| 192.168.5.108:3313 | NewProduct | Sleep | 1929 | | NULL | | 26 | yuheyang| 192.168.5.108:3314 | NewProduct | Sleep | 1929 | | NULL | | 27 | yuheyang| 192.168.5.108:3315 | NewProduct | Sleep | 1929 | | NULL | | 28 | root| 192.168.5.108 | NULL | Sleep | 668 | | NULL | | 29 | happynessrabbit | 192.168.5.108:44555 | NewProduct | Sleep | 274 | | NULL | | 30 | happynessrabbit | 192.168.5.108:44556 | NewProduct | Sleep | 274 | | NULL | | 31 | happynessrabbit | 192.168.5.108
embedded server - show processlist returns empty set
Hi Im running mysql 4.0.12-embedded, a simple query - 'show proceslist'; returns an empty set ? Is this a bug ? thx G
Bug in SHOW PROCESSLIST?
Hello, all. I have noticed over the last few days that when I do a SHOW PROCESSLIST on my MySQL server the host field always reports the same host IP address or name regardless of who is making the connection. Here is a run of the command on my machine: mysql show processlist; +-+---++-+-+--+---+--+ | Id | User | Host | db | Command | Time | State | Info | +-+---++-+-+--+---+--+ | 334 | engineer | 127.0.0.1:1164 | engineering | Sleep | 235 | | NULL | | 335 | processviewer | 127.0.0.1:1313 | NULL| Sleep | 0| | NULL | | 336 | root | 127.0.0.1:1316 | NULL| Query | 0| NULL | show processlist | +-+---++-+-+--+---+--+ 3 rows in set (0.00 sec) The user 'engineering' is not connecting from my local machine but the other two are. The port for the engineer user is correct according to netstat. The server is running on my development machine which runs Windows 2000 Professional. The server is the standard Win32 binary 4.0.12-nt-log. Has anyone else seen this phenomenon? Thanks, Joshua Smith -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in SHOW PROCESSLIST?
Josh Smith [EMAIL PROTECTED] wrote: I have noticed over the last few days that when I do a SHOW PROCESSLIST on my MySQL server the host field always reports the same host IP address or name regardless of who is making the connection. Here is a run of the command on my machine: mysql show processlist; +-+---++-+-+--+---+--+ | Id | User | Host | db | Command | Time | State | Info | +-+---++-+-+--+---+--+ | 334 | engineer | 127.0.0.1:1164 | engineering | Sleep | 235 | | NULL | | 335 | processviewer | 127.0.0.1:1313 | NULL| Sleep | 0| | NULL | | 336 | root | 127.0.0.1:1316 | NULL| Query | 0| NULL | show processlist | +-+---++-+-+--+---+--+ 3 rows in set (0.00 sec) The user 'engineering' is not connecting from my local machine but the other two are. The port for the engineer user is correct according to netstat. The server is running on my development machine which runs Windows 2000 Professional. The server is the standard Win32 binary 4.0.12-nt-log. There was a bug in 4.0.12 related to the host name in the SHOW PROCESSLIST command. Please, check it on 4.0.13. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in SHOW PROCESSLIST?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Smith wrote: Hello, all. I have noticed over the last few days that when I do a SHOW PROCESSLIST on my MySQL server the host field always reports the same host IP address or name regardless of who is making the connection. Here is a run of the command on my machine: mysql show processlist; +-+---++-+-+--+---+--+ | Id | User | Host | db | Command | Time | State | Info | +-+---++-+-+--+---+--+ | 334 | engineer | 127.0.0.1:1164 | engineering | Sleep | 235 | | NULL | | 335 | processviewer | 127.0.0.1:1313 | NULL| Sleep | 0 | | NULL | | 336 | root | 127.0.0.1:1316 | NULL| Query | 0 | NULL | show processlist | +-+---++-+-+--+---+--+ 3 rows in set (0.00 sec) The user 'engineering' is not connecting from my local machine but the other two are. The port for the engineer user is correct according to netstat. The server is running on my development machine which runs Windows 2000 Professional. The server is the standard Win32 binary 4.0.12-nt-log. Has anyone else seen this phenomenon? Yes. It was fixed in 4.0.13 (which is the latest STABLE release) :) -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+4KSbtvXNTca6JD8RAo9WAKCaPmpbGzsz8650JdfpJXrJcIeigACfXYJV NToY2vwgYSGO6yZ6TZP0ORY= =s2e8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
processlist
Hello all at mysql list, can anyone tell me, please, what kind of format is Time in PROCESSLIST? thanks -- Martin Hudec -- :@: [EMAIL PROTECTED] :w: http://www.corwin.sk :m: +421.907.303.393 In google non est, ergo non est. - unknown IRC operator -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: processlist
seconds - Original Message - From: Martin Hudec [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 3:55 PM Subject: processlist Hello all at mysql list, can anyone tell me, please, what kind of format is Time in PROCESSLIST? thanks -- Martin Hudec -- :@: [EMAIL PROTECTED] :w: http://www.corwin.sk :m: +421.907.303.393 In google non est, ergo non est. - unknown IRC operator -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: processlist
On Tuesday 01 April 2003 10:55, Martin Hudec wrote: can anyone tell me, please, what kind of format is Time in PROCESSLIST? In seconds. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with MySQL4, host field in show processlist
Hi, My first post here l;) Ok, here it goes. I recently installed MySQL4 and transfered all my databases there. Now, it all works great, but the host field, in show processlist command, don't work correctly. It don't display correct info from where the query has came. Example: mysql show processlist; ++--+-+--++--++--+ | Id | User | Host| db | Command| Time | State | Info | ++--+-+--++--++--+ | 32 | DELAYED | localhost | db1 | Delayed_insert | 0 | Waiting for INSERT | | | 1387 | DELAYED | localhost | db2 | Delayed_insert | 68 | Waiting for INSERT | You see the localhost, value for host. It isn't correct. These queries are from different server, not from localhost. I'm executing show processlist from localhost only. Anyone know how can i fix this, or something to show correct info ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble with MySQL4, host field in show processlist
Sasa, Now, it all works great, but the host field, in show processlist command, don't work correctly. It don't display correct info from where the query has came. Example: mysql show processlist; ++--+-+--++--++--+ | Id | User | Host| db | Command| Time | State | Info | ++--+-+--++--++--+ | 32 | DELAYED | localhost | db1 | Delayed_insert | 0 You see the localhost, value for host. It isn't correct. These queries are from different server, not from localhost. I'm executing show processlist from localhost only. Anyone know how can i fix this, or something to show correct info ? AFAIK it's a bug in 4.0.12. Should be corrected really soon, probably in 4.0.13 already. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble with MySQL4, host field in show processlist
Hi, I opened today a bug report for a similar bug here : http://bugs.mysql.com/bug.php?id=189 This problem only happens with DELAYED thread. Regards, Jocelyn - Original Message - From: Sasa Ugrenovic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 5:37 PM Subject: Trouble with MySQL4, host field in show processlist Hi, My first post here l;) Ok, here it goes. I recently installed MySQL4 and transfered all my databases there. Now, it all works great, but the host field, in show processlist command, don't work correctly. It don't display correct info from where the query has came. Example: mysql show processlist; ++--+-+--++- -++--+ | Id | User | Host| db | Command| Time | State | Info | ++--+-+--++- -++--+ | 32 | DELAYED | localhost | db1 | Delayed_insert | 0 | Waiting for INSERT | | | 1387 | DELAYED | localhost | db2 | Delayed_insert | 68 | Waiting for INSERT | You see the localhost, value for host. It isn't correct. These queries are from different server, not from localhost. I'm executing show processlist from localhost only. Anyone know how can i fix this, or something to show correct info ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqladmin processlist = weird in version 4.0.12
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 18 Mar 2003, Andrew Braithwaite wrote: Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? It seems to be a bug - I've now entered it into our bug database: http://bugs.mysql.com/?id=164 Thanks for spotting this! It will be fixed for the next release. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+eHtWSVDhKrJykfIRArHvAJ9WmjFwoNDOoLI6/Qi2CSOhUYniGgCfTzNI C8D5o3fmmpnjMMR13SxZ4HU= =gn2i -END PGP SIGNATURE- - 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
mysqladmin processlist = weird in version 4.0.12
Hi all, Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? Cheers, Andrew mysql,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: mysqladmin processlist = weird in version 4.0.12
The change log for 4.012 mentions (Lenz Grimmer just posted this a couple of messages ago): Functionality added or changed: * `SHOW PROCESSLIST' will now include the client TCP port after the hostname to make it easier to know from which client the request originated. I guess whenever the hostname lookup fails you just see the IP address. HTH/h On 3/18/03 2:36 PM, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi all, Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? Cheers, Andrew mysql,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 - 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: mysqladmin processlist = weird in version 4.0.12
It says in the change log that it added the port to make it easier to see where the connection is coming from. 146.101.143.72:48753 gives you a lot more information than host1 From the release announcement -- * `SHOW PROCESSLIST' will now include the client TCP port after the hostname to make it easier to know from which client the request originated. -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 2:36 PM To: [EMAIL PROTECTED] Subject: mysqladmin processlist = weird in version 4.0.12 Hi all, Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? Cheers, Andrew mysql,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: mysqladmin processlist = weird in version 4.0.12
Hi, I do understand what you're saying and I did read the 4.0.12 changelog and appreciate the addition of the TCP port in that display, but all the hostnames in the processlist are displaying as the localhost of the mysql server that they are connecting to instead of the hostnames of the server that is connecting. The /etc/hosts file is fine and is the same as it was before Bug or feature? Cheers, Andrew -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED] Sent: Tuesday 18 March 2003 22:48 To: Andrew Braithwaite; MySQL Mailinglist Subject: Re: mysqladmin processlist = weird in version 4.0.12 The change log for 4.012 mentions (Lenz Grimmer just posted this a couple of messages ago): Functionality added or changed: * `SHOW PROCESSLIST' will now include the client TCP port after the hostname to make it easier to know from which client the request originated. I guess whenever the hostname lookup fails you just see the IP address. HTH/h On 3/18/03 2:36 PM, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi all, Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? Cheers, Andrew mysql,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 - 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: mysqladmin processlist = weird in version 4.0.12
Anyone else notice this, or is it just me? Andrew -Original Message- From: Andrew Braithwaite Sent: Tuesday 18 March 2003 23:05 To: 'R. Hannes Niedner'; MySQL Mailinglist Subject: RE: mysqladmin processlist = weird in version 4.0.12 Hi, I do understand what you're saying and I did read the 4.0.12 changelog and appreciate the addition of the TCP port in that display, but all the hostnames in the processlist are displaying as the localhost of the mysql server that they are connecting to instead of the hostnames of the server that is connecting. The /etc/hosts file is fine and is the same as it was before Bug or feature? Cheers, Andrew -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED] Sent: Tuesday 18 March 2003 22:48 To: Andrew Braithwaite; MySQL Mailinglist Subject: Re: mysqladmin processlist = weird in version 4.0.12 The change log for 4.012 mentions (Lenz Grimmer just posted this a couple of messages ago): Functionality added or changed: * `SHOW PROCESSLIST' will now include the client TCP port after the hostname to make it easier to know from which client the request originated. I guess whenever the hostname lookup fails you just see the IP address. HTH/h On 3/18/03 2:36 PM, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi all, Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? Cheers, Andrew mysql,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 - 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 - 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
Zoombie threads when listing with show processlist.
Has anybody seen something like this when using show processlist? Note how they have been killed, their status is null and the have been running for 171316 seconds. I killed them yesterday. mysql show processlist; +-+-+---+---+-++---+-+ | Id | User| Host | db| Command | Time | State | Info| +-+-+---+---+-++---+-+ | 1365767 | nativeadmin | localhost | webnative | Killed | 171316 | NULL | SELECT ModifyDate From file WHERE FileID=325322 | 1365770 | nativeadmin | localhost | webnative | Killed | 171316 | NULL | SELECT ModifyDate From file WHERE FileID=398169 | 1365773 | nativeadmin | localhost | webnative | Killed | 171316 | NULL | NULL| | 1395711 | nativeadmin | localhost | webnative | Sleep | 0 | | NULL| | 1397954 | root| localhost | NULL | Query | 0 | NULL | show processlist| +-+-+---+---+-++---+-+ 5 rows in set (0.00 sec) Any ideas on this? Thanks in advance, --Erik Zapien - 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: show processlist ?
On Mon, Oct 21, 2002 at 02:51:14PM +0200, cristian ditoiu wrote: Hi . Using Mysql3.23.49 on RH 7.3 + PHP + Apache . When issuing 'show processlist' i get a lot of these : What exactly means 'sleep' ? The thread (connection) is idle. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 76 days, processed 1,603,281,982 queries (242/sec. avg) - 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
strange processlist
Thanks to Jeremy Zawodny for his great tool, I was able to capture this during a server meltdown at the hands of an upraded MySQL binary (From 3.23.32 to 3.23.52). My story is a famliar one -- a db (MyISAM tables) that ran fine for 3 years all of sudden brings our cpu to a halt with an upgraded binary from MySQL. RH, Linux 2.2.16-22enterprise, harmless load averages until a few respectable queries are issued and we get crippled. Never seen a processlist like this though. It happens within 30 seconds of the server getting throttled. Thanks, | 17546 | root| localhost | NULL| Connect | NULL | login | NULL | | 17547 | root| localhost | NULL| Connect | NULL | login | NULL | | 17548 | root| localhost | NULL| Connect | NULL | login | NULL | | 17549 | root| localhost | NULL| Connect | NULL | login | NULL | | 17550 | root| localhost | NULL| Connect | NULL | login | NULL | | 17551 | root| localhost | NULL| Connect | NULL | login | NULL | | 17552 | root| localhost | NULL| Connect | NULL | login | NULL | | 17553 | root| localhost | NULL| Connect | NULL | login | NULL | | 17554 | root| localhost | NULL| Connect | NULL | login | NULL | | 17555 | root| localhost | NULL| Connect | NULL | login | NULL | | 17556 | root| localhost | NULL| Connect | NULL | login | NULL | | 17557 | root| localhost | NULL| Connect | NULL | login | NULL | | 17558 | root| localhost | NULL| Connect | NULL | login | NULL | | 17559 | root| localhost | NULL| Connect | NULL | login | NULL | | 17560 | root| localhost | NULL| Connect | NULL | login | NULL | | 17561 | root| localhost | NULL| Connect | NULL | login | NULL | | 17562 | root| localhost | NULL| Connect | NULL | login | NULL | | 17563 | root| localhost | NULL| Connect | NULL | login | NULL | | 17564 | root| localhost | NULL| Connect | NULL | login | NULL | | 17565 | root| localhost | NULL| Connect | NULL | login | NULL | | 17566 | root| localhost | NULL| Connect | NULL | login | NULL | +---+--+---+-+-+--+--- __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.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
show processlist
Hi, I am developing a web-based application using java + tomcat + mysql in Win 2000. When the application is running,I try to view the processlist in MS-DOS using the commad show processlist. mysql show processlist; +--+--+-++-+ --+---++ | Id | User | Host| db | Command | Time | State | Info| +--+--+-++-+ --+---+ + | 4369 | ODBC | localhost | release2 | Query | 0 | NULL | show processlist | | 7916 | root | localhost | release2 | Query | 0 | freeing items | insert into app_overflow(location_id, date, status, time, ic, assistant_id, provider_id, reserve_sta | | 7917 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | | 8334 | unauthenticated user | localhost | NULL | Connect | NULL | Reading from net | NULL| +--+--+-++-+ --+---+---+ Actually,my application is running without error,but I'd like to find out the meaning of this kind of message.(freeing items,Reading from net,unauthenticated user and etc). Thanks in advance. - 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
state writing to net in processlist
Hello everybody I'd like to know what does that means: Writing to net in the state row of processlist. I have to launch a perl script which do traitement on a mysql table with 1.750.000 records. The request blocks during a long time everytime on the select of the hole table and i can see in the processlist: | 57732 | emc| data2.XYZ.net | emc_up2| Query | 85 | Writing to net | select * from Emc_load_temp_2860 order by id_transfert | I'd like to know what does that means: Writing to net in the state row of processlist. thanks for your help - 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: return row count from show processlist
On Tue, Jul 30, 2002 at 12:31:32PM -0400, Chris Friedline wrote: hello everyone, i'm a newbie so please excuse the probably dumb question. i'm using monitoring software to record the number of processes at a current time. currently, i'm just sending a show processlist and my monitoring software returns something like this: Fix your monitoring software. The SQL query 'show processlist' is just that; an SQL query. The MySQL API (either directly used, or used via the perl DBI module, or the like) will let you get a _ton_ of information about the query you've made, including the names of the columns, the number of rows, etc. thanks very much, in advance. chris -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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