Hi Tom, Sounds odd... Do other queries that "behave normally" use GROUP BY or DISTINCT? What are your configuration variables? e.g. SHOW VARIABLES or mysqladmin variables. Is sort_buffer_size set to some huge value?
Matt ----- Original Message ----- From: <[EMAIL PROTECTED]> Sent: Friday, September 19, 2003 5:19 PM Subject: mysqld consumes 1.3Gb of swap for simple query on solaris > >Description: > > A particular simple mysql query, > including FORMAT, count, and group commands, run on a very small > table, causes mysqld to consume about 1300 Mbytes of swap space > on our sparc solaris systems. Killing and restarting mysqld frees > up that space. All other routine mysql queries seem to behave normally. > > >How-To-Repeat: > > Here's an example that reproduces the problem: > > mysql> create database test1; > Query OK, 1 row affected (0.05 sec) > > mysql> use test1; > Database changed > > mysql> create table table1 (length double, id int); > Query OK, 0 rows affected (0.04 sec) > > mysql> insert into table1 (length, id) VALUES (1000, 1); > Query OK, 1 row affected (0.00 sec) > > mysql> insert into table1 (length, id) VALUES (1010, 1); > Query OK, 1 row affected (0.00 sec) > > mysql> insert into table1 (length, id) VALUES (1020, 2); > Query OK, 1 row affected (0.00 sec) > > mysql> select FORMAT(length, 0) as len, count(distinct id) > -> from table1 group by len; > +-------+--------------------+ > | len | count(distinct id) | > +-------+--------------------+ > | 1,000 | 1 | > | 1,010 | 1 | > | 1,020 | 1 | > +-------+--------------------+ > 3 rows in set (0.00 sec) > > > Here are 'top' snapshots, and mysqld memory usage as shown > by 'ps', both before and after the query was made. > > Note that the 'swap free' was reduced by 1366M, > and the memory size (SZ) reported for mysqld by ps went from > 1578 pages (~13M) to 176368 pages (~1400M). > > > Before query: > ------------ > top: > > load averages: 0.01, 0.03, 0.04 14:14:31 > 173 processes: 172 sleeping, 1 on cpu > CPU states: % idle, % user, % kernel, % iowait, % swap > Memory: 1024M real, 499M free, 281M swap in use, 2335M swap free > > ps -efl: > > F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY T > IME CMD > 8 S mysql 5884 4997 0 48 20 ? 1578 ? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld > > > After query: > ------------ > top: > > load averages: 0.02, 0.03, 0.04 14:15:55 > 175 processes: 174 sleeping, 1 on cpu > CPU states: % idle, % user, % kernel, % iowait, % swap > Memory: 1024M real, 498M free, 1647M swap in use, 969M swap free > > ps -efl: > F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY T > IME CMD > 8 S mysql 5884 4997 0 48 20 ? 176368 ? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld > > > Mysql versions tried: > > % mysql --version > mysql Ver 11.18 Distrib 3.23.55, for sun-solaris2.7 (sparc) > (Also tried mysql-4.0.13, it behaves the same way). > > Solaris versions tried: > > % uname -a > SunOS test 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 > (Also tried an Ultra-2 running 5.7, behaved the same). > > > Thanks, > Tom Kilsdonk > > >Fix: > > > >Submitter-Id: <submitter ID> > >Originator: > >Organization: > > >MySQL support: [none | licence | email support | extended email support ] > >Synopsis: mysqld consumes 1.3Gb of swap for simple query on solaris > >Severity: > >Priority: > >Category: mysql > >Class: sw-bug > >Release: mysql-3.23.55 (Source distribution) > >Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for sun-solaris2.7 on sparc > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB > This software comes with ABSOLUTELY NO WARRANTY. This is free software, > and you are welcome to modify and redistribute it under the GPL license > > Server version 3.23.49 > Protocol version 10 > Connection Localhost via UNIX socket > UNIX socket /tmp/mysql.sock > Uptime: 41 min 43 sec > > Threads: 2 Questions: 11 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.004 > >Environment: > > System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 > Architecture: sun4 > > Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/gcc3.3/bin/gcc /usr/ucb/cc > GCC: Reading specs from /usr/local/gcc3.3/bin/../lib/gcc-lib/sparc-sun-solaris2.8/3.3/specs > Configured with: ../gcc-3.3/configure --prefix=/home/kilsdonk/gcc3.3/install > Thread model: posix > gcc version 3.3 > Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' > LIBC: > -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /lib/libc.a > lrwxrwxrwx 1 root root 11 Sep 18 2001 /lib/libc.so -> ./libc.so.1 > -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /lib/libc.so.1 > -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /usr/lib/libc.a > lrwxrwxrwx 1 root root 11 Sep 18 2001 /usr/lib/libc.so -> ./libc.so.1 > -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /usr/lib/libc.so.1 > Configure command: ./configure '--without-docs' '--enable-thread-safe-client' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
