May 052010

Here are some neat database commands I recently came across while I was trying to get statistics and status information on some mysql and postgres databases.


This will give you a list of all of the databases in the server.*
select datname from pg_database;

* If you don’t know how to connect to postgres from the command line, don’t fear (well fear a little). What I had to do was su to the user that is a DB user, and then run /usr/local/pgsql/bin/psql This launched a command line client (without needing a password), that let me query the server. I can’t provide a better understanding then that, as this is the first time I’ve ever interacted with a postgres server.

This tells you how big a particular database is.
SELECT pg_size_pretty(pg_database_size('xythosdocs'));
The output is like this:
2834 MB
(1 row)


Get all database sizes:
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;
Produces output like this on the command line:

| Data Base Name     | Data Base Size in MB | Free Space in MB |
| blogadmin          |           0.35750008 |       0.00098896 | 
| blogs              |          90.60342026 |      27.73141003 | 
| information_schema |           0.00781250 |       0.00000000 | 
| musicschedule      |           0.00548172 |       0.00020599 | 
| mysql              |           0.54032421 |       0.00000000 | 
| phpflash           |           0.77745819 |       0.00000000 | 

Get some status information on the sever. Run this from the command line:
mysqladmin -uroot -p status
Phpmyadmin also provides a lot of process status information if you have that installed.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>