>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]
