Re: Speeding up MySQL server
On Thu, Mar 25, 2004 at 07:21:04PM -, Matt Chatterley wrote: Hmm. Taking a stab in the dark here, but.. If it's purely a 'hit counter' type affair, and you're updating a single row, perhaps performance could be gained by instead inserting into a table (presumably this can be done with single-row level locking - at least in InnoDB?), and running a regular job that aggregates the contents of this table into the counter row? Data won't be up-to-date instantly, but it might reduce contention. Do you experience contention when the counter is being read from? If so, this could possibly be reduced using a 'dirty read' (no locking on select), so that although slightly outdated information may be read, no updates would be delayed? Depends on what you're doing, and what you're trying to achieve, really! If I understand you correctly, you are suggesting to create a temp table, and update that all the time, then add that sum to the perm table every once in a while? In this case, the counter is only checked (for stats purposes) once an hour, so updating it at 30min increments would be fine (at our hit rate, that's about 180,000 hits) which would save a fair amount of contention time on the main table (If I understand it correctly). I'll have to look into this. -- Jim Richardson http://www.eskimo.com/~warlock Madness takes its toll. Please have exact change ready. signature.asc Description: Digital signature
RE: Speeding up MySQL server
Hmm. Taking a stab in the dark here, but.. If it's purely a 'hit counter' type affair, and you're updating a single row, perhaps performance could be gained by instead inserting into a table (presumably this can be done with single-row level locking - at least in InnoDB?), and running a regular job that aggregates the contents of this table into the counter row? Data won't be up-to-date instantly, but it might reduce contention. Do you experience contention when the counter is being read from? If so, this could possibly be reduced using a 'dirty read' (no locking on select), so that although slightly outdated information may be read, no updates would be delayed? Depends on what you're doing, and what you're trying to achieve, really! Thanks, Matt. -Original Message- From: Jim Richardson [mailto:[EMAIL PROTECTED] Sent: 25 March 2004 03:17 To: [EMAIL PROTECTED] Subject: Re: Speeding up MySQL server On Wed, Mar 24, 2004 at 08:21:15PM -0600, Paul DuBois wrote: >At 17:55 -0800 3/24/04, Jim Richardson wrote: >>I have a rather heavily loaded server, which I would like to tweak a >>little more performance out of. It currently is binlogging although >>there is no slave yet. Does the process of bin logging take significant >>resources? It's putting out about 1GB log per day, the IO load on the >>disks isn't too bad. But I am curious about the internal to MySQL load >>of logging all that data. > >It costs you about 1 percent in performance: > >http://www.mysql.com/doc/en/Binary_log.html > Thanks for the pointer. No big deal then, I'll have to actually do some work to improve the performance of the system :) darn, I was hoping for a quick easy victory. OK, so the system gets about 10million hits/day, and each hit, is acompanied by incrementing a counter in one of the tables. That's where the vast bulk of the writes come from, unfortunately, the table is locked with each write, and although it's quick, it still takes time. Any suggestions on where to look for info on improving this? Kind of a general question I know, but I don't need someone to do my work, just point me to someplace I can crib from :) -- Jim Richardson http://www.eskimo.com/~warlock Balance the budget. Declare politicions a game species and sell hunting stamps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speeding up MySQL server
Is the table you 're talking about critical ? If not, you could use type=HEAP for it, so every access will be done in memory. Marc. -Message d'origine- De : Jim Richardson [mailto:[EMAIL PROTECTED] Envoye : jeudi 25 mars 2004 04:17 A : [EMAIL PROTECTED] Objet : Re: Speeding up MySQL server On Wed, Mar 24, 2004 at 08:21:15PM -0600, Paul DuBois wrote: >At 17:55 -0800 3/24/04, Jim Richardson wrote: >>I have a rather heavily loaded server, which I would like to tweak a >>little more performance out of. It currently is binlogging although >>there is no slave yet. Does the process of bin logging take significant >>resources? It's putting out about 1GB log per day, the IO load on the >>disks isn't too bad. But I am curious about the internal to MySQL load >>of logging all that data. > >It costs you about 1 percent in performance: > >http://www.mysql.com/doc/en/Binary_log.html > Thanks for the pointer. No big deal then, I'll have to actually do some work to improve the performance of the system :) darn, I was hoping for a quick easy victory. OK, so the system gets about 10million hits/day, and each hit, is acompanied by incrementing a counter in one of the tables. That's where the vast bulk of the writes come from, unfortunately, the table is locked with each write, and although it's quick, it still takes time. Any suggestions on where to look for info on improving this? Kind of a general question I know, but I don't need someone to do my work, just point me to someplace I can crib from :) -- Jim Richardson http://www.eskimo.com/~warlock Balance the budget. Declare politicions a game species and sell hunting stamps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speeding up MySQL server
On Wed, Mar 24, 2004 at 08:21:15PM -0600, Paul DuBois wrote: At 17:55 -0800 3/24/04, Jim Richardson wrote: I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. It costs you about 1 percent in performance: http://www.mysql.com/doc/en/Binary_log.html Thanks for the pointer. No big deal then, I'll have to actually do some work to improve the performance of the system :) darn, I was hoping for a quick easy victory. OK, so the system gets about 10million hits/day, and each hit, is acompanied by incrementing a counter in one of the tables. That's where the vast bulk of the writes come from, unfortunately, the table is locked with each write, and although it's quick, it still takes time. Any suggestions on where to look for info on improving this? Kind of a general question I know, but I don't need someone to do my work, just point me to someplace I can crib from :) -- Jim Richardson http://www.eskimo.com/~warlock Balance the budget. Declare politicions a game species and sell hunting stamps. signature.asc Description: Digital signature
Re: Speeding up MySQL server
At 17:55 -0800 3/24/04, Jim Richardson wrote: I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. It costs you about 1 percent in performance: http://www.mysql.com/doc/en/Binary_log.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
