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

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 iosta

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 wil

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

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:

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

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 r