Re: Re[2]: Diffrences in table types
As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
Here is another question. Can you achieve the same performance having to different kinds of databases as though you were only using one? I am assuming that you are going to run into problems because you cannot set both types of databases to have a lot of memory allocated to them. Right? On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey [EMAIL PROTECTED] wrote: As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
Yes, if you use both table types within a single database then you will have to split up the memory usage. However, in many databases there are just one or two tables that use 90% of the disk/memory space. If this is your situation then you just allocate most of the memory for the table type these tables use (assumign they use the same type), and you won't have any performance problem because the others don't need much memory. However, if your data is evenly split and evenly accessed between the two table types then splitting the memory may present some performance degredation. Of course the solution is buy more memory. John On Mon, 2004-10-11 at 09:49 -0700, Benjamin Arai wrote: Here is another question. Can you achieve the same performance having to different kinds of databases as though you were only using one? I am assuming that you are going to run into problems because you cannot set both types of databases to have a lot of memory allocated to them. Right? On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey [EMAIL PROTECTED] wrote: As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Diffrences in Table Types
Greetings: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Diffrences in table types
(SORRY FOR THE BLANK MESSAGE WRONG BUTTON PUSHED) Greetings: I am trying to create referential intergrity... I was reading that mySql does not support that on the Database Level and that you have to do it manually. However i am now seeing some posts that talk about it... i.e. Foreign Keys and such... with INNODB can someone please fill me in Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Diffrences in table types
It's pretty much just how you said it. MyISAM tables (the default mysql table type) do not support foreign key constraints. InnoDB tables do support them. Here is a breakdown of the pros and cons of each as I see it (others please feel free to tell me I've got it all wrong :)). MyISAM pros: 1)fast 2)default 3)smaller disk footprint MyISAM cons: 1)Table level locking creates poor performance in high concurrency situations 2)No foreign key constraints 3)No transation support InnoDB pros: 1)Transactions 2)Row level locking for good performance in high concurrency situations 3)Foreign Key Contstraints InnoDB cons: 1)Higher disk footprint 2)Slightly slower in non high concurrency situations due to transaction overhead key constraint checking etc I've had a very positive experience with using InnoDB tables in a production environment with a several gigabyte database. John McCaskey From: GH [mailto:[EMAIL PROTECTED] Sent: Sat 10/9/2004 5:29 PM To: [EMAIL PROTECTED] Subject: Diffrences in table types (SORRY FOR THE BLANK MESSAGE WRONG BUTTON PUSHED) Greetings: I am trying to create referential intergrity... I was reading that mySql does not support that on the Database Level and that you have to do it manually. However i am now seeing some posts that talk about it... i.e. Foreign Keys and such... with INNODB can someone please fill me in Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
