MySQL Closing/Opening tables
Hi Recently I noticed the server takes lot of time on and off when opening and closing tables. And I tried to increase the table_cache more the the total tables (file_limit is properly set); and the problem still continues and lowering it also continues.. and tried to set in middle.. same Any thoughts on fixing this ? I am going crazy.. Sometimes the threads spin 10-60secs in just opening and closing tables state.. Thanks for reply Rick
Re: MySQL Closing/Opening tables
Thanks for the quick followup Baron.. vmstat procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 3 0100 499380 139256 560400000 190 693 11 11 20 2 70 8 0 iostat -dx 5 5 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.02 187.72 0.28 10.36 4.66 1584.73 149.44 2.03 191.16 5.23 5.56 sdb 1.85 371.84 21.72 56.86 1514.44 3956.6769.63 2.63 33.43 3.44 27.03 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 1527.80 0.40 73.80 3.20 12812.80 172.72 1.59 21.46 0.24 1.76 sdb 0.0011.60 2.40 10.8065.60 950.4076.97 0.08 5.82 3.67 4.84 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 173.00 0.00 11.60 0.00 1476.80 127.31 0.58 50.24 8.22 9.54 sdb 0.00 5.80 0.00 13.40 0.00 153.6011.46 2.61 195.13 5.63 7.54 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 1624.40 0.00 126.00 0.00 14003.20 111.14 5.31 42.14 0.38 4.84 sdb 0.00 599.60 0.00 82.20 0.00 15697.60 190.97 1.79 21.75 2.32 19.08 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 1538.80 0.20 61.60 1.60 12803.20 207.20 5.10 82.48 2.37 14.62 sdb 0.20 1.00 7.40 2.40 200.0027.2023.18 0.22 22.73 10.73 10.52 The above is when low to medium load.. From: Baron Schwartz To: dbrb2002-...@yahoo.com Cc: mysql@lists.mysql.com Sent: Friday, February 27, 2009 5:50:11 AM Subject: Re: MySQL Closing/Opening tables On Fri, Feb 27, 2009 at 4:19 AM, wrote: > Hi > > Recently I noticed the server takes lot of time on and off when opening and > closing tables. And I tried to increase the table_cache more the the total > tables (file_limit is properly set); and the problem still continues and > lowering it also continues.. and tried to set in middle.. same > > Any thoughts on fixing this ? I am going crazy.. > > Sometimes the threads spin 10-60secs in just opening and closing tables > state.. Have you checked to see if your disk is saturated with requests? Try this: vmstat 5 5 iostat -dx 5 5 Assuming you're on a Unix-like OS. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: MySQL Closing/Opening tables
Thanks Dan.. thats a valuable point.. and this actually happening with MyISAM tables only.. But the question is; when I set the table_cache to higher than total tables.. then it should stop closing the table in first place..so that only un-opened tables will be opened and kept in cache.. it will avoid closing and re-opening.. but looks like it is not the case.. Unless the table_cache is also used(unlikely) for temporary tables which are created by select queries.. From: Dan Nelson To: dbrb2002-...@yahoo.com Cc: mysql@lists.mysql.com Sent: Friday, February 27, 2009 1:15:25 PM Subject: Re: MySQL Closing/Opening tables In the last episode (Feb 27), dbrb2002-...@yahoo.com said: > Recently I noticed the server takes lot of time on and off when opening > and closing tables. And I tried to increase the table_cache more the the > total tables (file_limit is properly set); and the problem still continues > and lowering it also continues.. and tried to set in middle.. same MyISAM tables flush dirty index blocks at the end of every update; this can cause a long wait inside "closing tables". If you have just deleted a lot of rows or did some other update touching many rows, you might have to flush a lot of dirty blocks. Running "show status like 'Key_blocks_not_flushed'" during one of these periods should show the count starting out large, dropping rapidly, then leveling off when that table's blocks have been flushed. Fixes include: * Altering your troublesome tables and adding the DELAY_KEY_WRITE=1 option. This will force you to repair those tables after a mysql or OS crash, since the on-disk copies of the index will almost always be out of synch. * Switching to an engine with logging like InnoDB will allow mysql to write the changes to a transaction log immediately, then trickle out the actual key block updates over time. If you want to try out mysql 6.0, the maria engine is basically MyISAM with logging. -- Dan Nelson dnel...@allantgroup.com
MySQL Log and Data directories
On a high read/write load.. is it good to split log (binlogs, innodb txn logs) and data (all tables, innodb tablespace) in different partitions ? Anybody had any experience ? For example; out of 25 disks array with 142GB 1rpm... I would like to keep few disks to logs and rest to data .. is it advised or better to keep everything in spool so that all spindles can be efficiently managed... Thanks in advance
Re: MySQL Log and Data directories
Lets say HP Modular Storage/Smart Array.. http://h18006.www1.hp.com/storage/disk_storage/msa_diskarrays/san_arrays/index.html From: Chaim Rieger Subject: Re: MySQL Log and Data directories To: "Michael Dykman" Cc: dbrb2002-...@yahoo.com, mysql@lists.mysql.com Date: Wednesday, March 4, 2009, 10:31 AM Michael Dykman wrote: > On Wed, Mar 4, 2009 at 1:22 PM, wrote: >> On a high read/write load.. is it good to split log (binlogs, innodb txn >> logs) and data (all tables, innodb tablespace) in different partitions ? >> >> Anybody had any experience ? >> >> For example; out of 25 disks array with 142GB 1rpm... I would like to >> keep few disks to logs and rest to data .. is it advised or better to keep >> everything in spool so that all spindles can be efficiently managed... >> >> Thanks in advance >> > > You most certainly can get enhanced performance by putting your bin > log and transaction logs onto separate spindles.. Separate > partitions on the same physical device will be of little-to-no-value. > For systems like Oracle or DB2, this is part of the standard > installation drill. ON MySQL, I do not do this routinely, but I do > when I am expecting high, sustained loads, > > > what kind of storage are you using ? we do this on netapp.
Re: MySQL Log and Data directories
Thanks Baron... Also, curious question.. as you might have used what is called GOOD hw configurarion with RAID 5/10 .. so whats the typical IO (rnd rw) that you archive/expect on high trafficked sites ? --- On Wed, 3/4/09, Baron Schwartz wrote: From: Baron Schwartz Subject: Re: MySQL Log and Data directories To: dbrb2002-...@yahoo.com Cc: mysql@lists.mysql.com Date: Wednesday, March 4, 2009, 11:40 AM On Wed, Mar 4, 2009 at 1:22 PM, wrote: > On a high read/write load.. is it good to split log (binlogs, innodb txn > logs) and data (all tables, innodb tablespace) in different partitions ? > > Anybody had any experience ? > > For example; out of 25 disks array with 142GB 1rpm... I would like to > keep few disks to logs and rest to data .. is it advised or better to keep > everything in spool so that all spindles can be efficiently managed... > > Thanks in advance There are exceptions to everything I'm about to write, but: Under high read loads, there is no benefit. Under high write loads, there might be. With this many disks, yes. With fewer disks, the relatively trivial sequential log writes will not actually degrade performance much, and the non-trivial performance impact of stealing disks away and dedicating them to the logging workload will make a lot of difference. The real answer is always -- run a benchmark and see. Does the improvement offset things like any kind of penalty the OS imposes on you (e.g. LVM can't take a snapshot across multiple volumes)? -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
RAID-10 perf numbers
Does anybody have any RAID-10 sysbench fileio numbers for random writes with any number of disks(4/6/8/12...) to compare with write cache(512,256,...) backed by BBU.. I really appreciate your nos.. or whats the decent requests/sec for pure rndwrs ?
percona builds
Does anybody know how percona builds the server .. what configure options.. I never able to match the mysqld binary size ... so, wondering whats wrong..
iostats per mysql process
Does anyone has any suggestions on how to get IO stats used by per process (mysqld) on Linux 2.6.18-53 kernel ... I just need to see how much reads and writes mysql is actually doing and what else is causing the IO load..
INSERT ... SELECT ON DUPLICATE
Does anyone know if I can add a hint SQL_BUFFER_RESULT to INSERT .. SELECT ON DUPLICATE ex.. INSERT INTO foo SELECT SQL_BUFFER_RESULT* FROM bar ON DUPLICATE KEY UPDATE foo.X=.. Both my tables foo and bar are InnoDB; but the idea is to release the lock on bar as soon as possible by moving the results to temp table to accommodate slow inserts.. Any reco or does SQL_BUFFER_RESULT work in this case ? Thanks
