Re: Tuning a Server with >10,000 databases
On 5/9/06, Alex <[EMAIL PROTECTED]> wrote: That's what I actually did now. We have got the "databases start with usernames + number appended" situation here so i patched sql_show.cc code to only do acl checks on databases starting with the username. Still not optimal but cuts down a show databases on a server with 60.000 databases from 15 seconds to 0.14 seconds which is ok. Same here - my database names start with username+underscore, so query now takes 0.05 instead of 6 secs (I have about 15000 DBs). -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
That's what I actually did now. We have got the "databases start with usernames + number appended" situation here so i patched sql_show.cc code to only do acl checks on databases starting with the username. Still not optimal but cuts down a show databases on a server with 60.000 databases from 15 seconds to 0.14 seconds which is ok. Alexey Polyakov schrieb: That's not a bug, that's a missing feature (or we can call it unoptimal behaviour). I think that having 1 databases and 1 users on a single mysqld and doing "show databases" query often isn't what developers see as intended usage for MySQL. :) Here's what happens when you do "show databases" query: 1) mysqld gets a list of subdirectories of mysql data dir (usually /var/lib/mysql). Each directory is a database 2) It loops through all entries, and adds an entry to result set if: a) an user has global show databases privilege b) an user has been granted access for this database Part b) is what actually takes time. For each entry the server first checks ACL cache. It's a hash table and lookups against it are very fast. But for "show databases" query most lookups will return a miss, and a miss means full scan of the whole acl db. So for 1 databases it scans table with 1 rows 1 times, which means 10 million scanned records. That's why it's slow. As a workaround, if one has some rules regarding which user can see which DB (for example, if usernames and database names start with same substring), they can add this check to the code (so scans will be avoided for most entries). That's still far from optimal, but at least "show databases" will take dozens of milliseconds instead of seconds. On 5/6/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: Perhaps it's time to file a bug report, then? -Sheeri On 5/3/06, Alex <[EMAIL PROTECTED]> wrote: > This problem is indeed not related to OS / Hardware Problems. > > Take a look at this thread: > > http://lists.mysql.com/mysql/197542 > > Read the part about show databases as root vs standard user > > + observed file system activity. > > > > -- > 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] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
That's not a bug, that's a missing feature (or we can call it unoptimal behaviour). I think that having 1 databases and 1 users on a single mysqld and doing "show databases" query often isn't what developers see as intended usage for MySQL. :) Here's what happens when you do "show databases" query: 1) mysqld gets a list of subdirectories of mysql data dir (usually /var/lib/mysql). Each directory is a database 2) It loops through all entries, and adds an entry to result set if: a) an user has global show databases privilege b) an user has been granted access for this database Part b) is what actually takes time. For each entry the server first checks ACL cache. It's a hash table and lookups against it are very fast. But for "show databases" query most lookups will return a miss, and a miss means full scan of the whole acl db. So for 1 databases it scans table with 1 rows 1 times, which means 10 million scanned records. That's why it's slow. As a workaround, if one has some rules regarding which user can see which DB (for example, if usernames and database names start with same substring), they can add this check to the code (so scans will be avoided for most entries). That's still far from optimal, but at least "show databases" will take dozens of milliseconds instead of seconds. On 5/6/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: Perhaps it's time to file a bug report, then? -Sheeri On 5/3/06, Alex <[EMAIL PROTECTED]> wrote: > This problem is indeed not related to OS / Hardware Problems. > > Take a look at this thread: > > http://lists.mysql.com/mysql/197542 > > Read the part about show databases as root vs standard user > > + observed file system activity. > > > > -- > 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] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
Perhaps it's time to file a bug report, then? -Sheeri On 5/3/06, Alex <[EMAIL PROTECTED]> wrote: This problem is indeed not related to OS / Hardware Problems. Take a look at this thread: http://lists.mysql.com/mysql/197542 Read the part about show databases as root vs standard user + observed file system activity. -- 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]
Re: Tuning a Server with >10,000 databases
This problem is indeed not related to OS / Hardware Problems. Take a look at this thread: http://lists.mysql.com/mysql/197542 Read the part about show databases as root vs standard user + observed file system activity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
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 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]
Re: Tuning a Server with >10,000 databases
Gary - is there any way to check how many concurrent connections you have per second out of those 14,000 users? IIRC, each table requires at least one file handle to open the table's data file (.MYD), plus 1 more for the index file (.MYI) if the index file is updated. So, I'm wondering if you are running out of file handles, which would make mysql wait untill there are enough file handles free, for mysql to do it's job. open_files_limit The number of files that the operating system allows mysqld to open. This is the real value allowed by the system and might be different from the value you gave mysqld as a startup option. The value is 0 on systems where MySQL can't change the number of open files. mysql> show variables like "open%"; +--+---+ | Variable_name| Value | +--+---+ | open_files_limit | 1024 | +--+---+ 1 row in set (0.00 sec) mysql> show status like "open%"; +---+---+ | Variable_name | Value | +---+---+ | Open_files| 44| | Open_streams | 0 | | Open_tables | 20| | Opened_tables | 0 | +---+---+ 4 rows in set (0.00 sec) What does this return on your system? If this is the case, then I can't see how running multiple mysql servers will help. Keith On Sun, 2 Apr 2006, Alexey Polyakov wrote: > To: [EMAIL PROTECTED] > From: Alexey Polyakov <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > On 4/1/06, Greg Whalin <[EMAIL PROTECTED]> wrote: > > > 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? > > FWIW, I've experimented heavily with FS options, and found out that > dir_index on ext3 doesn't help at all, it actually harms performance. > 'noatime' and 'nodiratime' options do help a little. > Also, 14000 subdirectories is not something that will cause 15 seconds > delay - those 14000 subdirectories will always live in OS dentry cache > anyway. > > > -- > Alexey Polyakov > > -- > 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]
Re: Tuning a Server with >10,000 databases
On 4/1/06, Greg Whalin <[EMAIL PROTECTED]> wrote: > 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? FWIW, I've experimented heavily with FS options, and found out that dir_index on ext3 doesn't help at all, it actually harms performance. 'noatime' and 'nodiratime' options do help a little. Also, 14000 subdirectories is not something that will cause 15 seconds delay - those 14000 subdirectories will always live in OS dentry cache anyway. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
Hi! I'm facing somewhat similar problems - I've stuffed about 10 thousand databases, most of them are pretty small and not very active, and some of the queries are extremely slow. I've tried different filesystems, and currently I'm using bleeding-edge reiser4 filesystem. It handles large directories very well, commands like `find /var/lib/mysql|wc -l` complete under a second, but when for example authenticated user does SHOW DATABASES query, it takes about 15 seconds to complete. I'm pretty sure it's not OS or filesystem issue, cause during this query mysqld process consumes all available CPU time in user-land (if it was FS or OS issue, it would use kernel CPU time). I've tried pretty much anything I can think of, but can't seem to solve the problem. Splitting the databases between several mysqld instances will help of course, but it will be pretty tough to administer. I'm going to complile mysqld from sources with profiling enabled, then gather profiling info, and submit it as a bug. Seems that's the only thing that may help. On 4/1/06, Gary Huntress <[EMAIL PROTECTED]> 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
I'm not saying it is an apache issue Gary. I was just suggesting a way you might be able to implement several different mysql server instances, and still have one apache instance for your users to connect to via port 80. Each mysql server instance could be bound to an apache server instance via it's own php module. So each apache instance can talk to a different mysql server, via php, on a different unix socket. It may be possible for one apache listening on port 80 to forward requests to virtual servers, running on different apache servers on your machine, listening on different ports. That way your users will not have to define a different port to connect to. I've not tested this out yet, but it may be an option that would work for you. Hope this poor diagram make sense! - | apache main server port 80 | - | | | | | | | | | avs2 avs3 avs3 | | | --- | apache virtual server 1 port 81 | --- | v | php module 1 | | v -- | mysqld server 1 | -- ditto --- | apache virtual server x port xx | --- | v | php module x | | v -- | mysqld server x | -- Regards Keith On Sat, 1 Apr 2006, Gary Huntress wrote: > To: [EMAIL PROTECTED] > From: Gary Huntress <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > I really don't think this is an apache issue. I get the same poor > connection speeds using the mysql client. > > Gary > > [EMAIL PROTECTED] wrote: > > I'm just wondering if it would be possible to use several apache > > servers on different ports and using virtual servers, that would each > > talk to a different instance of mysql, each running on different unix > > sockets? > > > > The main apache server listening on port 80 could then redirect > > requests to the other apache virtual server instances. > > > > Everything after that should be plain sailing I think. > > > > Best place to ask would probably be the apache user mailing list on > > this one. > > > > Regards > > > > Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
I really don't think this is an apache issue. I get the same poor connection speeds using the mysql client. Gary [EMAIL PROTECTED] wrote: I'm just wondering if it would be possible to use several apache servers on different ports and using virtual servers, that would each talk to a different instance of mysql, each running on different unix sockets? The main apache server listening on port 80 could then redirect requests to the other apache virtual server instances. Everything after that should be plain sailing I think. Best place to ask would probably be the apache user mailing list on this one. Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 1 Apr 2006, Gary Huntress wrote: To: [EMAIL PROTECTED] From: Gary Huntress <[EMAIL PROTECTED]> Subject: Re: Tuning a Server with >10,000 databases 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
I'm just wondering if it would be possible to use several apache servers on different ports and using virtual servers, that would each talk to a different instance of mysql, each running on different unix sockets? The main apache server listening on port 80 could then redirect requests to the other apache virtual server instances. Everything after that should be plain sailing I think. Best place to ask would probably be the apache user mailing list on this one. Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 1 Apr 2006, Gary Huntress wrote: > To: [EMAIL PROTECTED] > From: Gary Huntress <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
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]
Re: Tuning a Server with >10,000 databases
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
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 -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
