My unions are only performed by a looping script so its ok for me. Thanks for the heads up. Did you encounter any extra problems beyond increasing the heap size? Just so I know in advance, is this a my.cnf option.
Ric ----- Original Message ----- From: "Michael Grover" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, May 23, 2002 5:15 AM Subject: Re: 4.0.1 Bugs > > I had problems with unions that result in large queries too. > I found that MySQL 4 was using Heap tables for Unions. > One can Increase the Heap Table size to help, but > What about alot of conncurrent users running unions? > This could eat up alot of memory. > Is there a switch to stop unions from using heap tables? > > mike > > > Heikki Tuuri wrote: > > > > Richard, > > > > > I will look at the UNION problem later. > > > > I think MySQL uses a HEAP type temporary table when it calculates the UNION. > > > > HEAP type tables are allocated from the main memory. The message 'Table is > > full' tells that MySQL cannot allocate more memory. > > > > Regards, > > > > Heikki > > > > > Best regards, > > > > > > Heikki Tuuri > > > Innobase Oy > > > --- > > > Order technical MySQL/InnoDB support at https://order.mysql.com/ > > > See http://www.innodb.com for the online manual and latest news on InnoDB > > > > > > ----- Original Message ----- > > > From: "Richard Clarke" <[EMAIL PROTECTED]> > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]> > > > Sent: Sunday, May 19, 2002 12:02 PM > > > Subject: Re: 4.0.1 Bugs > > > > > > > > > > Heikki, > > > > As for my crashes. This one is a little hard, see, we have two > > > machines > > > > that do this. Both however, display little to no information in the log. > > > One > > > > server just says mysql restarted followed by mysql was shut down > > > > incorrectly. The second server once gave an error like this, > > > > 020426 12:26:31 InnoDB: Started > > > > /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections > > > > 020511 1:09:25 read_key: Got error 146 when reading table > > > > './counter/br_type' > > > > 020516 2:27:31 read_key: Got error 146 when reading table > > > > './counter/br_type' > > > > 020518 1:25:36 read_key: Got error 146 when reading table > > > > './counter/br_type' > > > > InnoDB: Error: undo->id is 136712960 > > > > InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line > > 1316 > > > > InnoDB: We intentionally generate a memory trap. > > > > InnoDB: Send a detailed bug report to [EMAIL PROTECTED] > > > > mysqld got signal 11; > > > > This could be because you hit a bug. It is also possible that this > > binary > > > > or one of the libraries it was linked against is corrupt, improperly > > > built, > > > > or misconfigured. This error can also be caused by malfunctioning > > > hardware. > > > > We will try our best to scrape up some info that will hopefully help > > > > diagnose > > > > the problem, but since we have already crashed, something is definitely > > > > wrong > > > > and this may fail. > > > > > > > > key_buffer_size=16773120 > > > > record_buffer=1044480 > > > > sort_buffer=1048568 > > > > max_used_connections=190 > > > > max_connections=500 > > > > threads_connected=90 > > > > It is possible that mysqld could use up to > > > > key_buffer_size + (record_buffer + sort_buffer)*max_connections = > > 1038376 > > > K > > > > bytes of memory > > > > Hope that's ok; if not, decrease some variables in the equation. > > > > > > > > 020518 01:30:27 mysqld restarted > > > > InnoDB: Database was not shut down normally. > > > > InnoDB: Starting recovery from log files... > > > > InnoDB: Starting log scan based on checkpoint at > > > > InnoDB: log sequence number 589 1379690513 > > > > InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 > > > > InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 > > > > InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 > > > > InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 > > > > InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 > > > > InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 > > > > > > > > The 146 errors can be ignore, they were deadlocks ocurring in a > > > > select/insert being run simultaneously. This server only gave this > > signal > > > 11 > > > > once. The other times it just did its restart/recover routine as > > described > > > > above. One thing I have noticed, though not something I have monitored > > > > specifically is the memory usage of mysql. When the daemon first starts > > it > > > > has a size of about 800megs and a res(ources) of about 700/800. Over > > time > > > > however the size can grow to 1gig and the res drop to around 200. We > > have > > > a > > > > third mysql box which doesn't seem to crash, currently it is reporting. > > > > > > > > last pid: 47508; load averages: 0.60, 0.40, 0.25 > > > > up 47+19:17:15 08:49:53 > > > > 116 processes: 3 running, 111 sleeping, 2 zombie > > > > CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% > > > > idle > > > > Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free > > > > Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse > > > > > > > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > > COMMAND > > > > 44577 mysql 31 0 1085M 79544K RUN 1 34.6H 5.37% 5.37% mysql > > > > > > > > This server is also 4.0.1, though its not crashing. It does however play > > a > > > > different role in our system and hence it doesn't perform the same > > queries > > > > as the other two. > > > > I have iteratively developed the queries on the other server, all the > > > while > > > > monitoring innodb monitor output amongst other things. I am certain no > > > > transactions are stuck or otherwise. > > > > One of the crashing systems crashed yesterday infact, the log output > > just > > > > said restared/shutdown incorrectly etc (no caught signals). Whether its > > > any > > > > help or not, I don't know but here is some innodb_monitor output. > > > > > > > > > > --------------------------------------------------------------------- > > 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
