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
