Re: Table size vs Memory requirements?
On Nov 22, 2009, at 8:54 AM, Ryan Chan wrote: Hello, Is it common heard from people that if you have large table (assume MyISAM in my case), you need large memory in order to have the key/index in memory for performance, otherwise, table scan on disk is slow. But how to estimate how much memory I need? Consider a simple case, a MyISAM table is 10GB in size, with 2GB index, how much memory I need? Thanks. It's not the size of the table, it's the size of the index that you need to watch. MyISAM keeps the table and index separate, so the memory requirements can be considerably less than the size of the table. What you have likely heard is in reference to InnoDB tables. Since InnoDB clusters the index with the data, the memory requirements can be much greater. You may notice a significant drop off in performance from InnoDB once the data size passes a certain level, which is based on your RAM and InnoDB settings. MyISAM performance is usually fairy steady as the size of the table increases. -- Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table size vs Memory requirements?
Hello, Is it common heard from people that if you have large table (assume MyISAM in my case), you need large memory in order to have the key/index in memory for performance, otherwise, table scan on disk is slow. But how to estimate how much memory I need? Consider a simple case, a MyISAM table is 10GB in size, with 2GB index, how much memory I need? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table size vs Memory requirements?
In the last episode (Nov 22), Ryan Chan said: Is it common heard from people that if you have large table (assume MyISAM in my case), you need large memory in order to have the key/index in memory for performance, otherwise, table scan on disk is slow. But how to estimate how much memory I need? Consider a simple case, a MyISAM table is 10GB in size, with 2GB index, how much memory I need? If by table scan you mean a full table scan with no index usage, your RAM is irrevelant unless you have at leat 10GB (enough to cache the entire table). Anything less than that and you will have to read the entire table every time, and in that case, your disks' sequential throughput is the only factor. If you're more interested in random record lookups: Ideal case is 12 GB (no disk reads needed ever). Next best would be 2GB (enough to cache the entire index, but not the data, so you need to do one disk seek per lookup). Next best would be enough to cache all but the leaves of the index (requiring one index and one table seek per lookup); this depends on your key size but 200MB should be enough. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
