Re: Delayed Insert Question
Tuesday, December 09, 2003 2:51 PM Chris Elsworth wrote: > If you increase delayed_insert_limit then you're effectively giving > the DELAYED thread more preferencee to the table; it will write more > rows (once it can, ie there's a phase of time where there's no locks > on the table) in a batch, which potentially makes other selects wait > longer. > > Inserting delayed_queue_size means the clients can pile more and more > rows into the DELAYED thread while it gets chance to write. This may > give your clients a bit of a boost, but only if the DELAYED thread > fills up; at a default of 1000, you must be doing a lot of inserts to > reach that. Thank you Chris, I think I understand now. >Remember if you have a lot of rows waiting and mysql > crashes, they're lost. Well, I know that, but loosing 2000 inserts when i made more than 3 000 000 a day isn't a big problem. This table is for statistics only, data isn't very important and MySQL doesn't crash as often happyily :) Now i just need to choose, I can boost the insert ratio but i'll take some risks, or i can leave all as default ... Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed Insert Question
On Tue, Dec 09, 2003 at 02:18:58PM +0100, David Bordas wrote: > > I've read mysql doc sereval times, but i can't find any varaible that > specify when the delayed queue was flushed. Well, I suppose that's because there isn't one. The DELAYED thread handles that by itself. You don't want it too large because if mysql crashes while you have rows sat waiting to be written, they're lost. > If I understand, I can increase delayed_insert_limit for better performance, > but I should also increase the delayed_queue as well ? If you increase delayed_insert_limit then you're effectively giving the DELAYED thread more preferencee to the table; it will write more rows (once it can, ie there's a phase of time where there's no locks on the table) in a batch, which potentially makes other selects wait longer. Inserting delayed_queue_size means the clients can pile more and more rows into the DELAYED thread while it gets chance to write. This may give your clients a bit of a boost, but only if the DELAYED thread fills up; at a default of 1000, you must be doing a lot of inserts to reach that. Remember if you have a lot of rows waiting and mysql crashes, they're lost. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed Insert Question
> > So, i'm using INSERT DELAYED with some good succes. > > > > But I've got a question. > > If i decrease delayed_insert_limit to ten secondes for example, is that mean > > that delayed_queue will be flushed every ten secondes ? > > Is there an other variable that specify the flush time ? > > No - delayed_insert_limit refers to how many rows a DELAYED thread > will insert at once before checking if any other SELECTs are waiting > for the table. The process (and all related variables you can tweak) > are documented here: > > http://www.mysql.com/doc/en/INSERT_DELAYED.html > Thanks Chris, I've read mysql doc sereval times, but i can't find any varaible that specify when the delayed queue was flushed. If I understand, I can increase delayed_insert_limit for better performance, but I should also increase the delayed_queue as well ? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed Insert Question
On Tue, Dec 09, 2003 at 12:17:41PM +0100, David Bordas wrote: > So, i'm using INSERT DELAYED with some good succes. > > But I've got a question. > If i decrease delayed_insert_limit to ten secondes for example, is that mean > that delayed_queue will be flushed every ten secondes ? > Is there an other variable that specify the flush time ? No - delayed_insert_limit refers to how many rows a DELAYED thread will insert at once before checking if any other SELECTs are waiting for the table. The process (and all related variables you can tweak) are documented here: http://www.mysql.com/doc/en/INSERT_DELAYED.html -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]