Gary Huntress wrote: > > > David Logan wrote: >> mos wrote: >> >>> 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, >>> Just a guess, but could the problem be the 14,000 directories >>> you have to store the 14,000 databases? The problem could be the OS >>> directory structure. Putting the data into fewer databases will >>> likely solve the problem or perhaps move half of the directories to >>> another drive. >>> >>> Mike >>> >>> >>> >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >>> >>> >>> >> Hi Gary, >> >> I think that Mike may have hit the nail on the head. I've a few unix >> directories with multiple thousand files and they do become a bit of a >> problem to manage speedwise. Perhaps, as Mike has suggested, place >> half of them on another drive. >> >> The other option could be to run multiple instances of MySQL, each >> having a different port number (this could be based on username or >> something similar) eg. A-D port 3306, E-H 3307, etc. and reducing the >> number of dbs per instance (server) that way. >> >> Regards >> > I agree with the diagnosis. I'm unsure how to move 1/2 the databases to > a new drive though. That would be the simplest solution. As I > understand it, MySQL will only use 1 data directory, so the best case > would be symlinks. I'm not sure about this but 15,000 symlinks to > multiple drives may be just as slow as 15,000 directory entries. Were > either of you thinking of another way to split up the directories? > Unfortunately, since I assign one database per user, I can't limit the > number created. > > Multiiple servers may be my best option. > > Thanks, > > Gary
Not necessarily sure this is the problem. But if it is, it could be solved by switching to a different filesystem. Or, if you are using ext3fs, you could try enabling dir_index on the filesystem (tune2fs -O dir_index ... man tune2fs), which could give you a boost in performance in a large dir (this could take a long time to complete). You may also want to up your table cache so that mysql can keep more of your commonly used tables open? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
