Doesn't look like my reply last night made it to the list, resend...
Hello Seth, On Mon, 7 Jun 2004, Seth Brundle wrote: > 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. > > 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. Have you looked into a caching solution, such as memcached (http://www.danga.com/memcached/)? If you combine that with bundling your update queries into a textfile for later processing you will be able to buy yourself some time, however if you are planning on growing much more you should look into a redesign of your system. :) > 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. > > Again, a separate server would solve this problem, but I am really > struggling for a way to not do that - as much as I love MySQL, I really > hate installing, configuring, maintaining parallel permissions on 2 > servers. Especially since MySQL needs maintainence so infrequently, > every time I need to configure it it is like learning the entire process > all over again from scratch, and I always goof on some little detail > that doesnt raise its ugly head right away. > > If there is another option ( or maybe I shouldnt be OPTIMIZING every > day?), suggestions appreciated. > > PS I do not have any problems with the query performance. I would recommend setting up a crontab that runs every N minutes executing a query similar to this: sql> DELETE FROM <table> WHERE <where clause> LIMIT <limit>; You say you have 3 million records at the moment, give or take 100k on any given day. Doing a daily optimize at that rate is going to do you very little good as MySQL reuses "deleted" records. Play around with how long a 'DELETE ... LIMIT' takes by adjusting the <limit> vs. how many times an hour/day you want to run it. If you still want your table optimized every so often, try doing it only every Sunday morning or whenever the database activity is low. :) Atle - Flying Crocodile Inc, Junior Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
