On Sat, May 29, 2004 at 02:31:08PM +0100, Yannick Warnier wrote: > Hi there, > All of my my comments are base don my personal experience:
> - how wrong is creating 20 databases (total 400 tables) when you know > you could create just one (total around 200 tables)? <http://dev.mysql.com/doc/mysql/en/Creating_many_tables.html> 7.4.9 Drawbacks to Creating Many Tables in the Same Database > - what does MySQL handle better? Databases or tables? It depends on what you mean by 'handle'. Under the hood, as far as the file system goes, databases are merely directories, and tables are merely files in a database's directory. Of course, the set of database directories is kept under a single directory, whatever your 'datadir' variable is set to. All filesystems, irrespective of the OS in use, have performance problems when there are too many files (or directories) in a directory. Precisely what 'too many' is depends on the OS/filesystem in question, plus other modifiers such as how fast are your drives, how much RAM do you have, and so forth. 'Too many' is usually up in the thousands. A few hundred is usually fine. You can experience the performance of simply trying to use 'ls' on a directory; on a big directory, it will pause for a long period of time, as it gathers the list of files and sorts them. Your questions above WRT numbers of tables: > creating 20 databases (total 400 tables) when you know > you could create just one (total around 200 tables)? All points at numbers of files that are so small that they shouldn't be a major limit. Your first number, though: > I am using a mysql server at the moment which contains 2800 different > databases. Crosses that line into 'too many', for many filesystems. If your data is homogenous enough that is could be wadded up into one monster database, or broken up into an arbitrary number of smaller databases, then consider minimizing the number of databases, such that the number of tables per database is kept down in the hundreds. If your 2800 was really the number of tables in use, you could perhaps afford to scatter those tables into, say, ten databases of 280 each. But what you pick for numbers really does depend on the performance of your machines/OS/filesystem, as modified by what step's you've taken to tune your MySQL server. You're in the best position of all of us to make those tests. > - could a slowdown problem causing me to have a "show databases" query > answered in 42.50 seconds (more often being 0.02 sec) be bound to too > many databases? It could be a factor, but there could be other factors, such as how heavily the MySQL server is being used at that point. Or what other activity on that box is consuming CPU or disk cycles. > Thanks, > Yannick -- Brian Reichert <[EMAIL PROTECTED]> 37 Crystal Ave. #303 Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
