> > 1. The timeout is set to 5 min, because of the number of queries, there > > are a lot of unused http processes that linger with connections, and the > > only way to seeminly keep MySQL connections available is to keep > > timeouts short. > What about using a connection-pool? > Like Apache::DBI. > It should solve most of your problems (in theory...) Why not just use a master->slave system and then you wouldn't have to worry about it. All inserts go off the master, and all selects on the slave.
> > > However, I have updates that take over an hour at a time, and the 5 min > > timeout will kill the update process. If I change the timeout to 2 > > hours, Apache will eat up all the connections. Im a little confused at > > the behaviour. > Probably annoying, but not confusing behaviour - if I understand your > problem correctly. An hour is not bad, I just had one that took right at 97 hours that finally finished. With a master->slave system you wouldn't have to worry about this problem anymore. > > > Any suggestions appreciated. It seems to me the only answer is to > > maintain a completely seperate MySQL server with a 2 hour timeout on the > > same box, hotcopy the database, do the updates, and hotcopy it back, > > which I would *really* prefer not to do. There has got to be an easier > > way - any suggestions? > This doesn't sound a very nice solution. Same as above, but an even easier solution is to not delete the records you don't need anymore. Just add a status flag, and update the flag to deleted or whatever. And change your selects to only look for ones that aren't deleted. Then you never have to optimize again. (In theory). > > > > 2. The other problem I have is that because I am rotating so many > > records daily and the queries are so complex and the tables/indexes so > > large, I want to keep the db OPTIMIZEd, but running an OPTIMIZE TABLE on > > this database also takes over an hour and creates timeouts on queries. > > At the same time, I want to get the optimize done as soon as possible. > IMHO you'd be better off by optimizing your databases so that an OPTIMIZE > command would not be needed at all. See above. > > I think you should change tha application instead of hacking MySql. (Been > there, done that.) Probably you should place a layer between Apache and > MySQL. Or do the slow queries in a spearate process (definetely not in > Apache). Or separate the time slow queries into smaller ones. IMHO the first thing I would do is add a status flag. And see how that works. Then I would go to master-slave replication system if the flag option doesn't work as well as it should. It works for us without any problems. Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
