Mahesh, The best advice, from what I've heard around the list is to base your decision on this based on your filesystem. Some filesystems handle large # of files well (1 db, many tables) some don't. Some handle many directories well (many db's 1 tbl each), some don't (ala your example).
Personally, I would try to come to some happy medium. If you can group your databases by, let's say: 0- mysql data (of course) 1- collection system metadata (tables holding system listings, collection types, frequencies) then have a database per collection type. so that way you don't have a crazy number of files (tables) or directories (databases) finally, be aware that may systems have filesize limitation, so you may want to create archive tables (by month?) to roll data into so that the specific files don't hit system limits (typically 2gb or so) My $0.02.... Dan G > -----Original Message----- > From: Mahesh Tailor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 15, 2003 5:05 PM > To: [EMAIL PROTECTED] > Subject: DB Design > > > New to the list . . . > > Running MySQL Server 3.23.58-1.72 on RedHat Enterprise AS. System has > four 3GHz processors and 6GB RAM. > > I need some advise on what would be best way to approach this > problem. > > This system is using snmpcollect to collect network statistics from > about 1500 devices. The collections are configured to get data every > 5-30 minutes depending on the collection type. Given this I am > collecting approximately 170K records per hour. I have to keep this > collected data for at least 365 days. This works out to approximately > 1.50B records/year. After setting up the database, each record is 42 > bytes [which would yield, if my math is correct, a database of > approximately 62GB]. > > So my question is: is it better to create one database one > table or one > database many tables? If I use the many tables option, I will have > about 1500 tables. Or, it is better to create 1500 databases with one > table each. > > BTW, I tried the 1-DB-1-table approach the the server came to a crawl. > > Thanks for any opinions. > > Mahesh > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
