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.
Postgres
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:
pg_size_pretty
----------------
2834 MB
(1 row)
Mysql
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.