UNIONS 'Got error 12 from storage engine'
Have a query that consists of 10 unions. For a period of time it will constantly return with an error 12. Moments later it might actually work once or twice. I can find nothing in any logs or whatever. The odd thing is that when it isn't working, simply reducing the unions to 8 or less returns with no problem. A) Very little to be read about on the net for Error 12 B) What little there is talks mostly of BDB, which is of no relevance. I've tried reproducing the problem with lots of unions, even tried joining some simple table to try to get the same error, but to no avail. I'm guess it is related to my specific schema. I'm on 4.1.11, up from 4.1.10a but that didn't fix it. Any debugging avenues suggested? I know someone is going to want an explain, and it'll have to wait until the morning. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
I must have done a poor job at explaining this. I already have all those primary keys, etc. If you are required to do a full table scan on the table, as I am in my example, please explain to me how an index even matters in that case. There is _no_ where clause in the query so how will an index help? :) Now I have been benchmarking creating a CKEY combined key column that just basically is a concat(month_day, ',', bucket, ',', src). I made that a char(44) column and make that the primary key. Things seem faster due to use only one row for the primary key instead of 3, as well as a less complex group by. Preliminary results show the CKEY to be 50% faster on the particular query I'm using. 1) Yes, it does have to calculate floor(bucket / 3) but that is fairly in expensive call on a per-row basis, one would think. 2) I already have that index. It's a Primary Key(month_day, src, bucket). I'm still confused on how the index will speed it up on the source table side. We are NOT I/O bound. Looks more like cpu bound to me. Mysql uses 25% cpu on the solaris which is 1 entire cpu on a 4 processor machine. I'll give the order by NULL a shot Cliff Jigal van Hemert [EMAIL PROTECTED] writes: insert into new_table select month_day, floor(bucket/3) as bucket, date, src, avg(value) as value from source_table group by month_day, bucket, src; Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort Query OK, 11495208 rows affected (4 hours 47 min 21.01 sec) IMHO two things slow you down: 1) floor(bucket/3) as bucket. This means that MySQL has to calculate this value for each record to be able to use it as a grouping value. If you always use this same expression for grouping, it might be useful to have a column with the precalculated value. 2) there's only one table involved, so only one index will be used. If you had an index that contains month_day, bucket and src it would speed up things perhaps. Furthermore, it depends on the speed of the disks, your configuration, memory configuration and use how fast it will go. But explain indicates that MySQL needs to copy the data into a temporary table and use filesort to order and group things. Maybe it will help to surpress the automatic sorting that is done by MySQL because of the GROUP BY, by adding ORDER BY NULL? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html Do not use DELAYED with INSERT ... SELECT. With respect to the table structure...can you explain how when you have to read every single row regardless how the structure (assuming you are going down the path of idexes) affects the query? Cliff Donny Simonton [EMAIL PROTECTED] writes: Use insert delayed, and you will cut your time in half. At least with my experience. But also how long does it actually take to run the query itself. Giving a summary explain doesn't help much. You really need a table structure that the select is using and a full explain. Donny -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 6:02 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: SLOW 22million rows, 5 hour query? On 23 Apr 2004, at 9:48 pm, [EMAIL PROTECTED] wrote: Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort The filesort is a giveaway. Can you increase the sort buffer size so that the sort can happen in memory rather than having to use a file to sort? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- 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]
slow to a crawl
I have one query, that basically reads from one table of 13 million rows, groups it by two fields, and inserts into a rollup table. The result set ends up being about 31,000 rows. This runs in about 2 minutes which is acceptable. However, I add an additional group by field, which will probably result in a million results and mysql goes to a crawl. It doesn't even max out one cpu as the former query does. Instead vmstat reports a process blocked and in wait, while mysql goes down to hardly any cpu. The box lags a tad bit like it's I/O related, but the I/O isn't abnormal. As soon as I use mysqladmin to kill the query everything is back to normal. I'll note the load goes above 3.0 when this is running but none of it's in CPU ... I realize this is very vague, bu I'm just shooting in the dark here wondering if someone has seen similar oddities, and it might be resolvable by tuning some of the parameters. 3.23.49, precompiled from mysql.com 2.4.9-21smp Linux key_buffer=16M table_cache=128 sort_buffer=8M record_buffer=2M tmp_table_size=16M I've tried increasing these but didn't 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: slow to a crawl
Benjamin Pflugmann [EMAIL PROTECTED] writes: Hello. First, key_buffer=16M seems a bit low for me with 13 million rows (well, depends on the indexes, and how much free memory you have, of course). 1 gig, plenty free. If I am not mistaken, sort_buffer/tmp_table_size are the one mostly needed for this query, I think. Btw, you can get more info about the query with EXPLAIN. Please post the result of it (for both SELECTs), if you answer. Query 1, works: group by bayid, ds1 table typepossible_keys key key_len ref rowsExtra detail_20020408 ALL NULLNULLNULLNULL13083666Using temporary Query 2, bad: group by bayid, ds1, scid table typepossible_keys key key_len ref rowsExtra detail_20020408 ALL NULLNULLNULLNULL13083666Using temporary Another idea is that you have an index over the two fields you were talking, but not the additional field. In this case, the first query can run only from index, but the second needs to read from the table file. EXPLAIN should show. Btw, this is a full table scan, no WHERE clause exists, so I'm not sure where indexes would change the behaviour of this. Btw, please always post what you observe, e.g. the vmstat output. Maybe someone on the list can see something you didn't. Query 1: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 0 0492 5084 91104 837016 0 0 4 43 3 1 1 1 1 0 0492 5084 91104 836632 0 0 11968 0 1178 2444 47 6 47 2 0 0492 5084 91104 835524 0 0 8228 714 1653 3283 36 6 58 2 0 0492 5096 91104 835248 0 0 12342 8 1205 2553 49 7 44 2 0 0492 5096 91104 836780 0 0 11220 0 1180 2477 47 7 47 2 0 0492 5096 91104 836764 0 0 10472 714 1729 3297 40 6 54 1 0 0492 5096 91104 836744 0 0 11220 0 1220 2609 49 5 46 0 1 0492 5096 91104 836728 0 0 10392 736 1599 3259 42 7 50 Query 2: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 2 0 0492 5060 91148 820528 0 0 4 44 3 1 1 1 2 0 0492 5096 91152 817160 0 0 16836 0 1276 2611 84 13 3 4 0 0492 5092 91164 817340 0 0 11232 3040 1168 2573 81 16 3 3 0 0492 16404 91168 820312 0 0 9736 2722 1776 4093 74 16 10 2 1 1492 5092 91168 834696 0 0 11976 7756 1183 2793 81 17 2 0 2 1492 5092 91168 834420 0 0 9734 8382 1950 3270 60 15 25 2 1 1492 5092 91168 833448 0 0 10476 164 1705 2571 45 7 47 2 1 1492 5092 91172 833172 0 0 13100 6382 1536 2897 78 18 3 1 2 1492 5092 91172 832888 0 0 9354 9148 2187 3468 46 14 41 3 1 2492 5092 91172 831904 0 0 11226 6210 1644 2567 60 11 28 2 2 1492 5092 91172 832900 0 0 13102 7286 1740 2507 75 19 6 You can see how very eradict the machine gets... Interactive typing even starts to pause when you see the idle near zero, it's like something is thrashing in the kernel but top yields no clues. Cliff - 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
hung read() after SIG ALARM + libmysqlclient + Race Condition?
Hope this makes sense
I'm seeing random hung reads() after a mysql insert that appears to be
related to a sig alarm race condition. The basic workflow of this program
is
while (1)
recvfrom() /* few hundred packets a sec */
parse packet
insert into sql
However, once per second a sig alarm is generated which does house keeping
and what-not. This will work for minutes or hours, but eventually the
read(3, ...) blocks , which is the mysql socket. When this occurs you just
see the repeated sig alarms being catch, but immediately returning to that
read() which never returns. Note there is still hundreds of udp packets
coming in but never able to be read because the never-returing read(3,) on
the mysql sock.
Normal syscalls resemble:
write(3, \1\1\0\0\3INSERT INTO call_logging.de..., 261) = 261
read(3, \3\0\0\1, 4) = 4
read(3, \0\1\0, 3)= 3
recvfrom(5, ...) = 398
Here is (hopefully enough) machine information:
*** Precompiled binaries from mysql.com.
*** Linux mmlog2 2.4.9-21smp #1 SMP Thu Jan 17 14:01:48 EST 2002 i686 unknown
*** My Program complied as such (tried both shared and static linked,)
*** Note, this program isn't making use of threads, and I've seen the same
*** results with -D_REENTRANT -lpthread and without, as well as linking
*** the non _r libmysqlclient
gcc -o clconsumer -O -D_REENTRANT -g -lpthread -lm -lz -I/usr/include/mysql
-I/usr/local/mysql/include clconsumer.o /usr/local/mysql/lib/libmysqlclient_r.a
mysql Ver 11.17 Distrib 3.23.49, for pc-linux-gnu (i686)
Connection id: 13
Current database:
Current user: root@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.49
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset:latin1
Server characterset:latin1
UNIX socket:/tmp/mysql.sock
Uptime: 4 hours 55 min 3 sec
Threads: 4 Questions: 3261389 Slow queries: 0 Opens: 195 Flush tables: 1 Open
tables: 7 Queries per second avg: 184.228
--
Here is the infinite syscalls when mysql isn't returning (expected?) data on
the socket?
read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted)
--- SIGALRM (Alarm clock) ---
time(NULL) = 1014267832
rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0
alarm(1)= 0
rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0
sigreturn() = ? (mask now [])
read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted)
--- SIGALRM (Alarm clock) ---
time(NULL) = 1014267833
rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0
alarm(1)= 0
rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0
sigreturn() = ? (mask now [])
read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted)
--- SIGALRM (Alarm clock) ---
Cliff
-
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
Bugs? Re: HAVING [cond...] problem
Still doesn't work, I've even tried: HAVING ( Avg_Xmit = 12345 ) AND ( Samples = 25 ) 19000 rows returned, none equalling 12345, but all have Samples = 25. It's almost as if this became an OR. Cliff Gerald Clark [EMAIL PROTECTED] writes: Have you tried: HAVING ( Avg_Xmit 28800 ) AND ( Samples = 10 ) ? Cliff Daniel wrote: I'm having some difficulting on 3.23.38, linux x86... When I write the following query the HAVING clause is only listening to the 'AND Samples = 10' and displas all Avg_Xmit regardless if it's less than or equal to 28800. If I remove the 'AND Samples = 10' I get the correct results, but of course samples are permitted if less than 10, which I do not want. Any ideas? SELECT INET_NTOA(NAS_Identifier), NAS_DS1, AVG(Ascend_Xmit_Rate) as Avg_Xmit, AVG(Ascend_Data_Rate) as Avg_Recv, AVG(Acct_Session_Time) / 60 as ACHT, COUNT(*) as Samples FROM tbl_data_call_detail WHERE Acct_Session_Time 0 GROUP BY NAS_Identifier, NAS_DS1 HAVING Avg_Xmit 28800 AND Samples = 10 ORDER BY Avg_Xmit DESC Regards, Cliff - 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 -- Gerald L. Clark [EMAIL PROTECTED] -- Cliff Daniel Level 3 Communications The government deficit is the difference between the amount of money the government spends and the amount it has the nerve to collect. --Sam Ewing - 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
