>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: >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: 1 hour 17 min 38 sec Threads: 2 Questions: 12 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.003 >Environment: System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]