Copying to tmp table
Hi friends
I have a proble with Copying to tmp table with large tables, table have more
than 3 million rows
EXPLAIN da Query :
Table : log0904
Type : ref
Possible_keys :
data_login_tplig,emp_grp_login_dtlig,grp_tiporeg_tipolig_dtlig,grp_dtlig_tplig,dtlig_num,grp_equipe_emp_dtlig
Key : grp_tiporeg_tipolig_dtlig
Key_Len : 7
Ref : const
Rows : 11554
Extra : Using where; Using temporary; Using filesort
I kill my queries with more than 60 seconds
Time (sec) : 75
User : logs
Maquina : 192.168.0.76:43947
BD : logs
Thread : 18811390
State : Copying to tmp table
Query : SELECT tipo_atendimento ,login_operador,sum(duracao_operador) as
duracao_operador, count(*)as total FROM log0904 Where grupo = '1172' AND
data_ligacao = '2009-04-27' and hora_ligacao_ini between '17:00:00' and
'17:59:59' AND empresa = 'EXTREME-U' AND tipo_registro in ('L','T') GROUP BY
tipo_atendimento,login_operador
Somebody kwons a kind of tunning can I do?
Thanks
Carlos Caldi
_
Descubra seu lado desconhecido com o novo Windows Live!
http://www.windowslive.com.br
RE: Copying to tmp table
Hi Caldi,
Can I knew what kind of table type you are using and also try to send me
the explain plan of the query execution.
I would like to know what kind of keys are being used.
Thanks Regards,
Dilipkumar
Mobile: +91-9884430998
E-mail: dilipkumar.par...@eds.com
-Original Message-
From: Carlos Eduardo Caldi [mailto:ce_ca...@hotmail.com]
Sent: Tuesday, April 28, 2009 8:19 PM
To: mysql@lists.mysql.com
Subject: Copying to tmp table
Hi friends
I have a proble with Copying to tmp table with large tables, table have
more than 3 million rows
EXPLAIN da Query :
Table : log0904
Type : ref
Possible_keys :
data_login_tplig,emp_grp_login_dtlig,grp_tiporeg_tipolig_dtlig,grp_dtlig
_tplig,dtlig_num,grp_equipe_emp_dtlig
Key : grp_tiporeg_tipolig_dtlig
Key_Len : 7
Ref : const
Rows : 11554
Extra : Using where; Using temporary; Using filesort
I kill my queries with more than 60 seconds
Time (sec) : 75
User : logs
Maquina : 192.168.0.76:43947
BD : logs
Thread : 18811390
State : Copying to tmp table
Query : SELECT tipo_atendimento
,login_operador,sum(duracao_operador) as duracao_operador, count(*)as
total FROM log0904 Where grupo = '1172' AND data_ligacao =
'2009-04-27' and hora_ligacao_ini between '17:00:00' and '17:59:59'
AND empresa = 'EXTREME-U' AND tipo_registro in ('L','T') GROUP BY
tipo_atendimento,login_operador
Somebody kwons a kind of tunning can I do?
Thanks
Carlos Caldi
_
Descubra seu lado desconhecido com o novo Windows Live!
http://www.windowslive.com.br
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql openBSD Copying to tmp table
Hi all, Let me first say i am new to this list, but i did search all the internet for an answer for my problems. I have the following situation: OpenBSD 3.4 with Mysql 4.0.20 (It does not matter which version, the problem is on all) There is a forum running on this machine dedicated, (2,6 Ghz 1GB internal mem) The forum runs quick until somebody searches the forum, the process list shows a item which has the: Copying to tmp table state This takes ages, at least 200 seconds I tested with all kinds of config in my.cnf but nothing seems to help at all.. Now i moved the DB over to a Redhad Linux and MySQL 3.23.54, and the same search takes about 2 seconds !! this is a few 1000% faster then the openBSD machine. I allready tried making the config of the OpenBSD machine the same as the one from the linux machine but even that does not help.. I figure the must be something really wrong with the combination of OpenBSD and Mysql.. Anyone here have any qlue ? Thanx in advance for all help.. Richard Mevers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql openBSD Copying to tmp table
Mevershosting.nl [EMAIL PROTECTED] wrote: Anyone here have any qlue ? We had a couple of dissatisfactions trying to run MySQL on OpenBSD. See http://bulk.fefe.de/scalability/ -- 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: Query hung up in Copying to tmp table
Kevin, Forgot to mention that I think you're not seeing any temporary files because they're hidden. From http://www.mysql.com/doc/en/Temporary_files.html MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will not see a big temporary file that fills up the filesystem in which the temporary file directory is located. But then it also says: For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form `SQL_*'. So I dunno. :-) But I'm thinking they ARE hidden... if there's any files at all. It may be just an in memory temp HEAP table if it's smaller than tmp_table_size and no TEXT/BLOB columns are used in the query (e.g. possible length 255). I don't think there's much tuning that can be done to the server. The query and/or tables may need to be changed, if possible, to examine/return less rows. Also, what's the EXPLAIN SELECT output look like for the queries (the original and modified)? Just change the names of the tables/columns if you can't let them be seen. :-) Hint: use \G instead of ; at the end of the EXPLAIN query to make the output more legible. Matt - Original Message - From: Kevin Fries Sent: Friday, September 05, 2003 11:09 AM Subject: RE: Query hung up in Copying to tmp table Matt, Thanks for your response. It's going to take a bit for me to get the actual SQL available. I don't yet have permission to show that off. In the mean time, I can tell you that there are 10 table in the query, with 4 being references to the same table. I've been able to make an improvement that greatly improves the speed... by over-joining some records. That is, I'm joining 3 tables such that A joins to B, B joins to C, and C joins to A, basically. That was enough to let mySQL sort out a better plan. Interestingly though, the result of the query (and it appears to be proper) is 0 rows. I believe it's the intermediate row set that may be huge. But I've never seen mysql spit out a temporary file while processing. Again, I'd really like someone's reference to a tuning guide, if there's a superior one available. Thanks again, Kevin -Original Message- From: Matt W Sent: Thursday, September 04, 2003 1:37 PM To: [EMAIL PROTECTED] Subject: Re: Query hung up in Copying to tmp table Hi Kevin, I imagine the query is examining/returning so many rows that it's going to take a long time to create the needed temporary table (how long have you it go?). To start with, show us the EXPLAIN output for the problem SELECT, along with the SELECT. Also the size of the involved tables (rows and MB). Matt - Original Message - From: Kevin Fries Sent: Wednesday, September 03, 2003 6:51 PM Subject: Query hung up in Copying to tmp table I'm having a problem where a complex SELECT query begins eating up lots of CPU and never returns. In show processlist, it reports Copying to tmp table. Other queries seem to get locked waiting for this query, as well. This is on version 3.23.36 of mySQL, and I'm considering upgrading to the latest 3.23.57, to see if that improves the performance. I can't find any matching references in the Changes web pages that indicate this, though. Can anyone verify this? Alternately, I'm trying to find ways to possibly improve the step of copying to tmp table. According to my show variables output, the tmpdir is /tmp, and /tmp has plenty of room (85 megs or so). But while the query is running, I don't even see a corresponding file there. The CPU is spiking with top reporting mysql as eating 85% CPU. So I then assume that the 'problem' is happening before the file is created? Does that sound right? If someone has a better reference for tracking down the source of a problem like this, other than http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very appreciative. thanks, Kevin Fries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query hung up in Copying to tmp table
Matt, Thanks for your response. It's going to take a bit for me to get the actual SQL available. I don't yet have permission to show that off. In the mean time, I can tell you that there are 10 table in the query, with 4 being references to the same table. I've been able to make an improvement that greatly improves the speed... by over-joining some records. That is, I'm joining 3 tables such that A joins to B, B joins to C, and C joins to A, basically. That was enough to let mySQL sort out a better plan. Interestingly though, the result of the query (and it appears to be proper) is 0 rows. I believe it's the intermediate row set that may be huge. But I've never seen mysql spit out a temporary file while processing. Again, I'd really like someone's reference to a tuning guide, if there's a superior one available. Thanks again, Kevin -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 1:37 PM To: [EMAIL PROTECTED] Subject: Re: Query hung up in Copying to tmp table Hi Kevin, I imagine the query is examining/returning so many rows that it's going to take a long time to create the needed temporary table (how long have you it go?). To start with, show us the EXPLAIN output for the problem SELECT, along with the SELECT. Also the size of the involved tables (rows and MB). Matt - Original Message - From: Kevin Fries Sent: Wednesday, September 03, 2003 6:51 PM Subject: Query hung up in Copying to tmp table I'm having a problem where a complex SELECT query begins eating up lots of CPU and never returns. In show processlist, it reports Copying to tmp table. Other queries seem to get locked waiting for this query, as well. This is on version 3.23.36 of mySQL, and I'm considering upgrading to the latest 3.23.57, to see if that improves the performance. I can't find any matching references in the Changes web pages that indicate this, though. Can anyone verify this? Alternately, I'm trying to find ways to possibly improve the step of copying to tmp table. According to my show variables output, the tmpdir is /tmp, and /tmp has plenty of room (85 megs or so). But while the query is running, I don't even see a corresponding file there. The CPU is spiking with top reporting mysql as eating 85% CPU. So I then assume that the 'problem' is happening before the file is created? Does that sound right? If someone has a better reference for tracking down the source of a problem like this, other than http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very appreciative. thanks, Kevin Fries -- 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: Query hung up in Copying to tmp table
Hi Kevin, I imagine the query is examining/returning so many rows that it's going to take a long time to create the needed temporary table (how long have you it go?). To start with, show us the EXPLAIN output for the problem SELECT, along with the SELECT. Also the size of the involved tables (rows and MB). Matt - Original Message - From: Kevin Fries Sent: Wednesday, September 03, 2003 6:51 PM Subject: Query hung up in Copying to tmp table I'm having a problem where a complex SELECT query begins eating up lots of CPU and never returns. In show processlist, it reports Copying to tmp table. Other queries seem to get locked waiting for this query, as well. This is on version 3.23.36 of mySQL, and I'm considering upgrading to the latest 3.23.57, to see if that improves the performance. I can't find any matching references in the Changes web pages that indicate this, though. Can anyone verify this? Alternately, I'm trying to find ways to possibly improve the step of copying to tmp table. According to my show variables output, the tmpdir is /tmp, and /tmp has plenty of room (85 megs or so). But while the query is running, I don't even see a corresponding file there. The CPU is spiking with top reporting mysql as eating 85% CPU. So I then assume that the 'problem' is happening before the file is created? Does that sound right? If someone has a better reference for tracking down the source of a problem like this, other than http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very appreciative. thanks, Kevin Fries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query hung up in Copying to tmp table
I'm having a problem where a complex SELECT query begins eating up lots of CPU and never returns. In show processlist, it reports Copying to tmp table. Other queries seem to get locked waiting for this query, as well. This is on version 3.23.36 of mySQL, and I'm considering upgrading to the latest 3.23.57, to see if that improves the performance. I can't find any matching references in the Changes web pages that indicate this, though. Can anyone verify this? Alternately, I'm trying to find ways to possibly improve the step of copying to tmp table. According to my show variables output, the tmpdir is /tmp, and /tmp has plenty of room (85 megs or so). But while the query is running, I don't even see a corresponding file there. The CPU is spiking with top reporting mysql as eating 85% CPU. So I then assume that the 'problem' is happening before the file is created? Does that sound right? If someone has a better reference for tracking down the source of a problem like this, other than http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very appreciative. thanks, Kevin Fries
Re: Copying to tmp table
Sender: [EMAIL PROTECTED] Date: Tue, 24 Sep 2002 08:21:20 +0200 From: Lars Heidieker [EMAIL PROTECTED] on solaris it probably a good idea to change the tmp directory from /var/tmp - /tmp I recalled this feature and decided to look into linux for it's parallel. It's called tmpfs and probably requires a kernel build for most users (I have no idea what RedHat does nowdays, nor any of the popular distributions for that matter). In a kernel 'make xconfig', select the 'Virtual memory file system support' under the File systems tab. Add a mount in /etc/fstab along the lines of : tmpfs /var/tmptmpfs size=100M In my.cnf, adjust tmpdir tmpdir = /var/tmp -- You want to avoid mounting at /tmp for reasons noted in the tmpfs docs. There are additional reasons to avoid /tmp, just ensure the permissions on /var/tmp match /tmp. sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Copying to tmp table
Teh, Tuesday, September 24, 2002, 8:31:54 AM, you wrote: TCP Does anyone has any idea to improve this situation? TCP When I show my processlist, there is inner join query that having Copying TCP to tmp Table state, and it takes quite long time to finish the query. Any TCP idea to avoid Copying to tmp Table? I have tried to increase my TCP tmp_table_size however it does not remedy my problem. MySQL uses temporary tables in some queries. You can try to optimize your query. Here is some info that could help you: http://www.mysql.com/doc/en/EXPLAIN.html http://www.mysql.com/doc/en/Where_optimisations.html http://www.mysql.com/doc/en/ORDER_BY_optimisation.html -- 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 - 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
Copying to tmp table
hi, Does anyone has any idea to improve this situation? When I show my processlist, there is inner join query that having Copying to tmp Table state, and it takes quite long time to finish the query. Any idea to avoid Copying to tmp Table? I have tried to increase my tmp_table_size however it does not remedy my problem. 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
Re: Copying to tmp table
hi, on solaris it probably a good idee to change the tmp directory from /var/tmp - /tmp as /tmp is a ram disk that gives a good performance boost if tmp tables are involved. The sort_buffer variable the the one to look at try increasing it, as if the result fits into the sort buffer no tmptable is needed... Teh Chee Peng wrote: hi, Does anyone has any idea to improve this situation? When I show my processlist, there is inner join query that having Copying to tmp Table state, and it takes quite long time to finish the query. Any idea to avoid Copying to tmp Table? I have tried to increase my tmp_table_size however it does not remedy my problem. 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 -- Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, daß sie noch nicht einmal oberflächlich sind. --Friedrich Nietzsche - 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
copying to tmp table
Hi! When I join 4 tables with about 3000 records in each mysql seems to work forever on copying to tmp table. I wonder what that could be. I have enough free space in the tmp-dir and don't know what else could it be. The database-model is normalized and on another machine it takes me about 1 second to get a response. The strange thing about it is, that it worked before. So if anyone is able to help me please do so. Thank you, Benjamin Boksa -- Benjamin Boksa [EMAIL PROTECTED] side by site GmbH Co. KG Druckgestaltung Webdesign Barbarastr. 3-9 (Block 6) D-50735 Koeln Telefon: +49 221 2790964 Telefax: +49 221 2790965 http://www.sidebysite.de/ - 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: copying to tmp table
Paste EXPLAIN SELECT ... (your SELECT); from the mysql client. Hi! When I join 4 tables with about 3000 records in each mysql seems to work forever on copying to tmp table. I wonder what that could be. I have enough free space in the tmp-dir and don't know what else could it be. The database-model is normalized and on another machine it takes me about 1 second to get a response. The strange thing about it is, that it worked before. - 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
Copying to tmp table order by, group by
Hi, Running 3.23.37 on freebsd We are making about 200-250 requetes per second on a server with 1Go RAM. We have lot of problems with the requetes which use order by, group by. It seems mysqld copies the data to a tmp table to make sort. The problem is it takes a while to be done. How to make it quicker ? -O long_query_time=10 -O wait_timeout=10 -O interactive_timeout=60 -O max_connect_errors=1 -O key_buffer=384M -O table_cache=1000 -O sort_buffer=15M -O record_buffer=15M -O max_connections=200 -O thread_cache_size=250 -O max_user_connections=10 -O tmp_table_size=100M -O max_tmp_tables=100 kind of the requets which takes more than 10sec. | Query | 13 | Copying to tmp table | SELECT medias.idmedia, medias.titre, |medias.note, medias.idtype, categ.nomcat FROM medias, medias_de | Query | 11 | Copying to tmp table | select products.products_id, |products.products_name, products.products_price, products.products_imag | Query | 11 | Copying to tmp table | SELECT distinct(gallerie.id) as |id,gallerie.titre as titre,count(wall.id) as galtaille,gallerie.wall thanks for help Octave - 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
