--=-WHb7yYBfPjIjPgE5Ple/ Content-Type: text/plain Content-Transfer-Encoding: 7bit
On Wed, 2005-03-30 at 10:42 +0200, Gabor Szabo wrote: > Firs of all, thanks for the responses. > > To get you more details to chew on. > We found the problem and solved it but I would be glad to see how other > would attack the problem with this extra information: > > Basically on every hit the database write a row in a table in MySQL. > The server gets about 5 hits per second from web clients (at least > that's what the Apache status said). As long as the above table has > 1-2000 rows things work fine the load is around > 0 (mostly on the positive side ;) with ocassionally jumpingto 0.30 > > When the table is around 20.000 rows the load goes up to what I > described in my previous > message. > > As this is mostly a logging table we decided to keep it below 2000 > rows all the time. > What would you do to solve this problem ? > When you update a table in a database, the table gets locked. When a table is locked, and you try to perform a query on that table, the query is being queued for execution (even if it's a SELECT!). The time that the table stays locked, depends, of course, on the time it takes to build the new table that contains the up-to-date data. Like Baruch Even said earlier, if you have indexes on the table, it means that the index has to be built so that it will contain correct references to the new state of the table. The more records you have, the more time this takes. Generally, updating a big table all the time is a BAD IDEA, and should _never_ be done. The main question is: is it requirable that the table be up-to-date according to the last INSERT/UPDATE you just did, or that you just want it to be updated some when, as long as it eventually happens. This problem is well known in system that holds counters for data, or, like your case, logging. The solution is rather simple - you don't have to make your tables small. You really don't. What you need is a small table to which you will be logging whatever you need, and once in a while (say, via a cronjob every 10 minutes), you run a query to check what is the current autoincrement value of the last item in that small table. Then, you do a SELECT with WHERE limiting to the maximum of that number of current autoincrement. From all that data that you got, you build a transaction that INSERTs all the items into your logging table, and close the transaction. All the data is inserted in one chunk, and even if you have indexes, that means that they're built only once, instead of after each and each INSERT. After you verify that the operation was successful, you run a DELETE from the small table, limiting it, again, to the autoincrement number from before. -- shimi <[EMAIL PROTECTED]> --=-WHb7yYBfPjIjPgE5Ple/ Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8"> <META NAME="GENERATOR" CONTENT="GtkHTML/3.2.5"> </HEAD> <BODY> On Wed, 2005-03-30 at 10:42 +0200, Gabor Szabo wrote: <BLOCKQUOTE TYPE=CITE> <PRE> <FONT COLOR="#000000">Firs of all, thanks for the responses.</FONT> <FONT COLOR="#000000">To get you more details to chew on.</FONT> <FONT COLOR="#000000">We found the problem and solved it but I would be glad to see how other</FONT> <FONT COLOR="#000000">would attack the problem with this extra information:</FONT> <FONT COLOR="#000000">Basically on every hit the database write a row in a table in MySQL.</FONT> <FONT COLOR="#000000">The server gets about 5 hits per second from web clients (at least</FONT> <FONT COLOR="#000000">that's what the Apache status said). As long as the above table has</FONT> <FONT COLOR="#000000">1-2000 rows things work fine the load is around</FONT> <FONT COLOR="#000000">0 (mostly on the positive side ;) with ocassionally jumpingto 0.30</FONT> <FONT COLOR="#000000">When the table is around 20.000 rows the load goes up to what I</FONT> <FONT COLOR="#000000">described in my previous</FONT> <FONT COLOR="#000000">message. </FONT> <FONT COLOR="#000000">As this is mostly a logging table we decided to keep it below 2000</FONT> <FONT COLOR="#000000">rows all the time.</FONT> <FONT COLOR="#000000">What would you do to solve this problem ?</FONT> </PRE> </BLOCKQUOTE> <BR> When you update a table in a database, the table gets locked. When a table is locked, and you try to perform a query on that table, the query is being queued for execution (even if it's a SELECT!). The time that the table stays locked, depends, of course, on the time it takes to build the new table that contains the up-to-date data. Like Baruch Even said earlier, if you have indexes on the table, it means that the index has to be built so that it will contain correct references to the new state of the table. The more records you have, the more time this takes.<BR> <BR> Generally, updating a big table all the time is a BAD IDEA, and should _never_ be done. The main question is: is it requirable that the table be up-to-date according to the last INSERT/UPDATE you just did, or that you just want it to be updated some when, as long as it eventually happens.<BR> <BR> This problem is well known in system that holds counters for data, or, like your case, logging.<BR> <BR> The solution is rather simple - you don't have to make your tables small. You really don't. What you need is a small table to which you will be logging whatever you need, and once in a while (say, via a cronjob every 10 minutes), you run a query to check what is the current autoincrement value of the last item in that small table. Then, you do a SELECT with WHERE limiting to the maximum of that number of current autoincrement. From all that data that you got, you build a transaction that INSERTs all the items into your logging table, and close the transaction. All the data is inserted in one chunk, and even if you have indexes, that means that they're built only once, instead of after each and each INSERT. After you verify that the operation was successful, you run a DELETE from the small table, limiting it, again, to the autoincrement number from before.<BR> <BR> <TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%"> <TR> <TD> -- <BR> shimi <<A HREF="mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A>> </TD> </TR> </TABLE> </BODY> </HTML> --=-WHb7yYBfPjIjPgE5Ple/-- ================================================================= To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
