Hi,
I have a lot of mysql databases on my server. A lot of them are rarely accessed and unused.

will these slow the server down or increase load?

the pages that access the databases are rarely accessed.

Unless you're talking in excess of 1000 databases, I wouldn't be worried. There are few maintenance tasks that run, and so the number of DBs shouldn't noticeably impact performance. If they store a lot of information, they may impact your file system space, but not read/write performance.

The main factors you'll have to consider in DB performance are indexing, query optimization, conf file tweaking, stored procedure development, and UDFs where necessary to decrease query complexity (e.g. addition of a MEDIAN UDF will greatly improve performance over calculating medians in SQL).

You can install iotop and mytop on your server to gauge your I/O activity and MySQL activity respectively. Both will show the top processes for each. If MySQL is your top I/O process, you'll want to review why (e.g. you may have to tweak your my.conf file). MyTop will show queries that are queued up. Should typicaly be 0-1. If you have dozens or more, you'll see which queries are holding up your queue. Fix those problems by indexing columns and tweaking queries.

I wouldnt worry about the "amount" of DB's however, Query complexity will slow down the pages.

Check you have index's set for larger tables and run an optimize every now and again.

Ive had a database that made a webpage load after a minute or so....
After i ran an optimize it was loading instantly.

wow great advice about optimising databases. But its phpbb, sql size 1.87 gb, so optimizing it is super difficult unless there are ssh/phpmyadmin scripts that will do this automatically

its approximately 70 excess databases - most of them on joomla or wordpress etc

Originally Posted by dimensio

wow great advice about optimising databases. But its phpbb, sql size 1.87 gb, so optimizing it is super difficult unless there are ssh/phpmyadmin scripts that will do this automatically

its approximately 70 excess databases - most of them on joomla or wordpress etc

Write a php script to call from the browser to run the optimise command? if you know the root username and password?

Or install phpmyadmin?

Are the joomla and wordpress sites in use? Any one wordpress or joomla site could blast your database, especially if it has open permissions for registration or comments, and gets a lot of requests from spam bots.

Is this a dedicated server or VPS, or is it a shared server? If the latter, your database may be getting slammed by other applications on other accounts on the machine.

If you have SSH access, try the following from command line to add an index to a table:

$mysql -u 'yourusername' -p 'yourpassword' yourdatabasename
mysql> ALTER TABLE `tablename` ADD INDEX (`columnname`);
mysql> quit

If you have root access, try this to install and use iotip:

sudo apt-get install iotop
sudo iotop

Click here to see how to install and use mytop: http://jeremy.zawodny.com/mysql/mytop/