How big is your MySQL?

If you're running persistent connections from webserver or anywhere else, each connection takes one thread. If you don't do this, perhaps some queries are hanging or not properly closing. Look for mysql processes with "show processes".

Number of threads isn't necessarily bad thing. I found out that system performance improved ten fold when I allowed persistent connections from apache that is doing a lot of database queries.

Correct sql command is "show processlist"

You're looking at a snapshot after freshly restarting the server, more or less. There are no persistent connections, and I'm not concerned about performance, so I don't want them. It's fine if web requests take an extra half a second because they have to establish a connection, because this is an extremely low-traffic server. I'm mostly concerned about the memory being used by mysqld even when it's idle.

Top doesn't show the memory usage correctly for threads. What does your "free" command give or the first lines in top?

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
22317 mysql      9   0 54292  36m  26m S  0.0 12.6   4:14.27 mysqld
22318 mysql      0   0 54292  36m  26m S  0.0 12.6   6:11.49 mysqld
22319 mysql      9   0 54292  36m  26m S  0.0 12.6   2:08.20 mysqld
 7986 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.16 mysqld
 8791 mysql      9   0 54292  36m  26m S  0.0 12.6   0:14.32 mysqld
 8983 mysql      9   0 54292  36m  26m S  0.0 12.6   0:10.01 mysqld
 9197 mysql      9   0 54292  36m  26m S  0.0 12.6   0:08.60 mysqld
 9469 mysql      9   0 54292  36m  26m S  0.0 12.6   0:16.79 mysqld
 9598 mysql      9   0 54292  36m  26m S  0.0 12.6   0:21.50 mysqld
 9608 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.42 mysqld
 9756 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.67 mysqld
 9765 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.72 mysqld
 9780 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.70 mysqld
 9851 mysql      9   0 54292  36m  26m S  0.0 12.6   0:15.05 mysqld
 9852 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.70 mysqld
 9901 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.78 mysqld
10218 mysql      9   0 54292  36m  26m S  0.0 12.6   0:03.19 mysqld
10812 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.18 mysqld
10814 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.52 mysqld
10816 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.57 mysqld
10854 mysql      9   0 54292  36m  26m S  0.0 12.6   0:10.42 mysqld
11623 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.34 mysqld
13170 mysql      9   0 54292  36m  26m S  0.0 12.6   0:03.25 mysqld
13303 mysql      9   0 54292  36m  26m S  0.0 12.6   0:03.30 mysqld
13443 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.20 mysqld
13831 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.15 mysqld
13832 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.34 mysqld
14650 mysql      9   0 54292  36m  26m S  0.0 12.6   0:01.39 mysqld
14682 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.18 mysqld
15096 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.18 mysqld
15119 mysql      9   0 54292  36m  26m S  0.0 12.6   0:00.00 mysqld

free will only tell me total used/free memory, not how much mysqld is using in particular. There's lots of other stuff running on this box. The output from top tells me that it's using at least 14M, and assuming that those are all threads of a single process, then 14M should be the correct number. The question is, can I do better than that?

Well if you want total size, I got a project in the works that'll be pushing half a TB and will probably reach over a TB once completed.

A TB on mysql is pretty impressive – some might say foolhardy, but I guess that depends on the application. But I was actually asking about RAM/swap usage, not database size on disk. I was not very clear about that, sorry.

For reference, a PostgreSQL 8.0.8 with 2 persistent connections, one on a tiny database (only tens of rows per table) and one on a small database (hundreds of rows per table) looks like this on my Gentoo with a 2.6 kernel.

USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  3957  0.0  0.2   7220   196 ?        S    Aug31   0:03 /usr/bin/postmaster -D /var/lib/postgresql/data
postgres  4027  0.0  0.7   7220   696 ?        S    Aug31   0:15 postgres: writer process                       
postgres  4028  0.0  0.0   6936    64 ?        S    Aug31   0:02 postgres: stats buffer process                 
postgres  4029  0.0  0.3   6068   308 ?        S    Aug31   0:02 postgres: stats collector process              
postgres  4125  0.0  2.7   7984  2584 ?        S    Aug31   0:12 postgres: sqlgrey sqlgrey 127.0.0.1(3599) idle 
postgres  4385  0.0  1.5   7900  1476 ?        S    Aug31   0:00 postgres: vmail vmail 127.0.0.1(3601) idle     

I suspect the total RSS is even smaller than the sum of its parts (postgres forks processes which then share the same memory pages).

This is a grand total of 5,3 - 5,4 M in resident memory (RSS). The database is accessed fairly often (several queries per email and several emails per minute).

The box is currently under a good amount of memory pressure (GCC 4.1.1 and Glibc 2.4 are now available -> I'm upgrading the whole box which implies a long standing background recompilation of each and every package) but PostgreSQL remains responsive.

I've tuned PostgreSQL for low memory usage by :

• lowering max_connections to 16,

• lowering shared_bufers to 64.

It may not be useful to you if you can't switch to PostgreSQL but :

• this means you may be able to tune MySQL to use less memory (see MySQL doc) like I did with PostgreSQL (it used to allocate 50% more memory at startup),

• you definitely don't need 14M for small databases.

Note that if you can avoid persistent connections, the total is squeezed to 1332 kB. You could even disable stats and get below 1M :-)

Yeah, I'm also running postgres on this box and it uses much less memory than mysql, but alas I have to run both. My favorite database for basic web apps these days is actually sqlite, which doesn't need a separate daemon process at all and is very slim, while still providing everything most apps need.