At 09:27 PM 3/31/2006, you wrote:
I have been offering free database hosting for over 4 years and I've been
doing it on a shoestring. My last MySQL server was a generic 1GHz
system with 256MB RAM running Redhat 9. The performance was surprisingly
good because the query loads were not typically high. One persistent
problem was the initial connection times. On that old system if I had
less than approx 10,000 separate databases then the connection times were
"fast", and on the order of 1 second or so. If I had more than 10,000
databases this dramatically changed the connection times to well over 15
seconds or more.
I always attributed this connection lag to a problem with the filesystem
and the large number of directories. The old server had RH9 and ext3 with
no htree support which I was told could help with this problem.
I recently bought a new 2.4 GHz system with 1GB of RAM and installed
Fedora 4 with ext3 and htree support. All new hardware, faster drives,
more RAM and updated software. I thought I was golden! Well, I have
14,000 databases on this new system and it is as slow as the old 1GHz system.
The tuning articles I've read, and the sample my-*.cnf files that ship
with the tarball appear to apply to the more typical installation of a
single huge database rather than thousands of individual dbs. Can anyone
offer any suggestions?
Thanks,
Gary Huntress
Gary,
You could create a test database directory with the same 10,000+
directories and then delete start deleting database directories until it
speeds up again. So if it is slow with 10001 databases, but speeds up with
9999 databases, then it is likely an OS problem.
Do all the databases have the same table structure? If so merging
them into 1 large database is the solution. If not, then why not create a
lookup database with a simple table containing the customer# and the
database (directory) where the data is stored. When a customer signs in,
you look up the customer# and get the database containing his set of
tables. His tables are prefixed with his customer number as in 1234_Table1,
1234_Table2 etc.. So instead of having 10,000 databases, you could have
1000 databases with 100 sets of tables each. (There are 100 customers in
each database)
Mike
--
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]
