Re: innodb/myisam performance issues
Hey Josh, I came in really late on this discussion. It's been my experience that InnoDB is great until the size of the database/indexes surpasses the amount of memory you can give to InnoDB for caching. The performance drop off is pretty quick and dramatic. I've seen this happen on live tables that performed great one day and then horrible the next. Although this was on table of about 20 million rows, not 130M. Based on your table size, you would need to be running a 64-bit system and 64-bit mysql so you could allocate enough memory to InnoDB. You don't see the system swapping because InnoDB is working within it's defined memory allocation limits. Using EXPLAIN on your queries probably isn't showing you anything helpful because MySQL is using the proper indexes, but InnoDB can't fit the entire index in memory. My best guess is that InnoDB is loading part of the index, searching, loading the next part, searching, etc. Which is why you don't see consistent high IO or CPU. If you run vmstat 1, that may show you that IO is occurring, followed by CPU, then back to IO. For very large tables I stick with MyISAM and use MERGE tables if they are applicable. Hope that helps or points you in the right direction. Brent Baisley On Sep 4, 2008, at 4:26 PM, Josh Miller wrote: Good afternoon, I have recently converted a large table from MyISAM to InnoDB and am experiencing severe performance issues because of it. HTTP response times have gone from avg .25 seconds to avg 2-3 seconds. Details follow: PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one that serves images, one master DB that serves all reads/writes, backup DB that only serves for backup/failover at this time (app being changed to split reads/writes, not yet). The one table that I converted is 130M rows, around 10GB data MyISAM to 22GB InnoDB. There are around 110 tables on the DB total. My.cnf abbreviated settings: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer= 3G sort_buffer_size = 45M max_allowed_packet = 16M table_cache = 2048 tmp_table_size= 512M max_heap_table_size = 512M myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 thread_cache_size = 300 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 600M thread_concurrency = 8 max_connections = 2048 sync_binlog = 1 innodb_buffer_pool_size = 14G innodb_log_file_size = 20M innodb_flush_log_at_trx_commit=1 innodb_flush_method = O_DIRECT skip-innodb-doublewrite innodb_support_xa = 1 innodb_autoextend_increment = 16 innodb_data_file_path = ibdata1:40G:autoextend We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? The plan is to convert all tables to InnoDB which does not seem like a great idea at this point, we're considering moving back to MyISAM. Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
Aaron Blew wrote: Here are a couple ideas: * Decrease innodb_autoextend_increment to 8 or even 4. You may see additional IO wait because you're pre-allocating space in chunks disproportinate to what you immediately need, causing bursty performance. * If your remaining MyISAM tables don't need it, take 2GB of the key_buffer alocation and put it towards the innodb buffer pool What are the system's specs? What's it's underlying storage? What flags were used when you created the filesystem(s)? What OS/Version of MySQL are you running? Could you send us some iostat output? Thanks for all of your suggestions -- we've switched back to MyISAM until we can test this better. * increasing the innodb_buffer_pool had no apparent effect on performance. * System is a Dell PE2950 4 core, 32GB RAM, RAID-10 local disks. * File system is plain ext3, 'mke2fs -j' * Running RHEL 4.4, MySQL 5.0.66a-enterprise (open ticket with MySQL, working all angles here). * iostat output sample (iostat -x 5): avg-cpu: %user %nice %system %iowait %steal %idle 5.110.003.37 23.440.00 68.08 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 294.00 328.00 2560.00656 5120 dm-0359.50 328.00 2560.00656 5120 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 14.270.006.63 22.280.00 56.82 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 360.7055.72 4815.92112 9680 dm-0456.2255.72 4815.92112 9680 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 34.080.00 23.60 15.860.00 26.47 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 348.00 316.00 3304.00632 6608 dm-0446.00 316.00 3304.00632 6608 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 29.590.00 27.84 15.230.00 27.34 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 475.00 152.00 4284.00304 8568 dm-0554.50 152.00 4284.00304 8568 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 23.280.00 15.77 18.150.00 42.80 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 312.50 192.00 3044.00384 6088 dm-0401.50 192.00 3044.00384 6088 dm-1 0.00 0.00 0.00 0 0 Interesting note: when I switched to using the myisam version of the table with the old configuration, we still had very poor performance with significant CPU IO wait as you can see from the above iostat. This was without any load on the InnoDB table at all. Once I restarted with the new settings, the load and performance recovered immediately. You can see from this iostat output where the restart occurred: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.0061.68 260.88 610.58 2158.88 5384.43 8.6678.96 90.60 1.05 91.52 dm-0 0.00 0.00 261.08 673.05 2158.88 5384.43 8.0885.99 92.06 0.98 91.54 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 3.500.002.05 19.450.00 75.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.0030.40 373.20 251.00 3340.80 2251.20 8.9631.07 49.77 1.13 70.64 dm-0 0.00 0.00 373.00 281.40 3340.80 2251.20 8.5533.85 51.72 1.08 70.72 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 4.150.002.002.400.00 91.45 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 8.40 20.60 54.20 206.40 500.80 9.45 5.30 70.80 1.37 10.28 dm-0 0.00 0.00 20.60 62.60 206.40 500.80 8.50 5.57 66.90 1.23 10.26 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.750.000.602.25
Re: innodb/myisam performance issues
On Fri, Sep 5, 2008 at 12:55 PM, Josh Miller [EMAIL PROTECTED] wrote: Aaron Blew wrote: Here are a couple ideas: * Decrease innodb_autoextend_increment to 8 or even 4. You may see additional IO wait because you're pre-allocating space in chunks disproportinate to what you immediately need, causing bursty performance. * If your remaining MyISAM tables don't need it, take 2GB of the key_buffer alocation and put it towards the innodb buffer pool What are the system's specs? What's it's underlying storage? What flags were used when you created the filesystem(s)? What OS/Version of MySQL are you running? Could you send us some iostat output? Thanks for all of your suggestions -- we've switched back to MyISAM until we can test this better. * increasing the innodb_buffer_pool had no apparent effect on performance. * System is a Dell PE2950 4 core, 32GB RAM, RAID-10 local disks. * File system is plain ext3, 'mke2fs -j' * Running RHEL 4.4, MySQL 5.0.66a-enterprise (open ticket with MySQL, working all angles here). * iostat output sample (iostat -x 5): avg-cpu: %user %nice %system %iowait %steal %idle 5.110.003.37 23.440.00 68.08 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 294.00 328.00 2560.00656 5120 dm-0359.50 328.00 2560.00656 5120 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 14.270.006.63 22.280.00 56.82 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 360.7055.72 4815.92112 9680 dm-0456.2255.72 4815.92112 9680 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 34.080.00 23.60 15.860.00 26.47 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 348.00 316.00 3304.00632 6608 dm-0446.00 316.00 3304.00632 6608 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 29.590.00 27.84 15.230.00 27.34 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 475.00 152.00 4284.00304 8568 dm-0554.50 152.00 4284.00304 8568 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 23.280.00 15.77 18.150.00 42.80 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 312.50 192.00 3044.00384 6088 dm-0401.50 192.00 3044.00384 6088 dm-1 0.00 0.00 0.00 0 0 Interesting note: when I switched to using the myisam version of the table with the old configuration, we still had very poor performance with significant CPU IO wait as you can see from the above iostat. This was without any load on the InnoDB table at all. Once I restarted with the new settings, the load and performance recovered immediately. You can see from this iostat output where the restart occurred: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.0061.68 260.88 610.58 2158.88 5384.43 8.66 78.96 90.60 1.05 91.52 dm-0 0.00 0.00 261.08 673.05 2158.88 5384.43 8.08 85.99 92.06 0.98 91.54 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 3.500.002.05 19.450.00 75.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.0030.40 373.20 251.00 3340.80 2251.20 8.96 31.07 49.77 1.13 70.64 dm-0 0.00 0.00 373.00 281.40 3340.80 2251.20 8.55 33.85 51.72 1.08 70.72 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 4.150.002.002.400.00 91.45 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 8.40 20.60 54.20 206.40 500.80 9.45 5.30 70.80 1.37 10.28 dm-0 0.00 0.00 20.60 62.60 206.40 500.80 8.50 5.57 66.90 1.23 10.26 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00
RE: innodb/myisam performance issues
Hello Josh, why you moved your table to InnoDB? Your description doesn't sound like the tables rows are accessed concurrently and need to be locked? Are you sure you need InnoDB for this table? If you need InnoDB you probably need to redesign your queries and table structure to get them more convenient for InnoDB. With kind regards, TomH -Original Message- From: Josh Miller [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2008 10:27 PM To: mysql@lists.mysql.com Subject: innodb/myisam performance issues Good afternoon, I have recently converted a large table from MyISAM to InnoDB and am experiencing severe performance issues because of it. HTTP response times have gone from avg .25 seconds to avg 2-3 seconds. Details follow: PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one that serves images, one master DB that serves all reads/writes, backup DB that only serves for backup/failover at this time (app being changed to split reads/writes, not yet). The one table that I converted is 130M rows, around 10GB data MyISAM to 22GB InnoDB. There are around 110 tables on the DB total. My.cnf abbreviated settings: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer= 3G sort_buffer_size = 45M max_allowed_packet = 16M table_cache = 2048 tmp_table_size= 512M max_heap_table_size = 512M myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 thread_cache_size = 300 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 600M thread_concurrency = 8 max_connections = 2048 sync_binlog = 1 innodb_buffer_pool_size = 14G innodb_log_file_size = 20M innodb_flush_log_at_trx_commit=1 innodb_flush_method = O_DIRECT skip-innodb-doublewrite innodb_support_xa = 1 innodb_autoextend_increment = 16 innodb_data_file_path = ibdata1:40G:autoextend We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? The plan is to convert all tables to InnoDB which does not seem like a great idea at this point, we're considering moving back to MyISAM. Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
Tom Horstmann wrote: Hello Josh, why you moved your table to InnoDB? Your description doesn't sound like the tables rows are accessed concurrently and need to be locked? Are you sure you need InnoDB for this table? If you need InnoDB you probably need to redesign your queries and table structure to get them more convenient for InnoDB. Hi Tom, The rows in this table are accessed concurrently as any activity on the site is recorded/added/updated to this table. We have several others which serve similar purposes, (sessions, totaltraffic, etc...). I don't disagree, the application needs to be written to perform better and use MySQL more efficiently. I need to find a way to make it work better in the interim :) Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb/myisam performance issues
The rows in this table are accessed concurrently as any activity on the site is recorded/added/updated to this table. We have several others which serve similar purposes, (sessions, totaltraffic, etc...). Is the performance lag occurring with read-only queries and updates/inserts to the InnoDB table? Is the table mostly read or more written? You could set innodb_flush_log_at_trx_commit=2 if you may loose the latest InnoDB writes in case of a MySQL crash. It should give you much less IO for writes on your InnoDB tables. Please see http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html for a detailed description. Please also read about innodb_flush_method at this site and possibly try other settings. TomH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb/myisam performance issues
Addendum.. Please also try increasing your innodb_log_file_size to a much higher value if you have lots of writes/transactions. Maybe 250MB is a good first try. You need to delete/move the InnoDB logs before restart. Not sure about this, but please also set innodb_log_buffer_size. Try something between 16-32MB if you have many transactions. TomH -Original Message- From: Tom Horstmann [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2008 11:15 PM To: 'Josh Miller' Cc: mysql@lists.mysql.com Subject: RE: innodb/myisam performance issues The rows in this table are accessed concurrently as any activity on the site is recorded/added/updated to this table. We have several others which serve similar purposes, (sessions, totaltraffic, etc...). Is the performance lag occurring with read-only queries and updates/inserts to the InnoDB table? Is the table mostly read or more written? You could set innodb_flush_log_at_trx_commit=2 if you may loose the latest InnoDB writes in case of a MySQL crash. It should give you much less IO for writes on your InnoDB tables. Please see http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html for a detailed description. Please also read about innodb_flush_method at this site and possibly try other settings. TomH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
On Thu, Sep 4, 2008 at 4:26 PM, Josh Miller [EMAIL PROTECTED] wrote: We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. O_DIRECT may not be the best setting for your hardware. You might want to go back to the default. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? What you really need to do is look at which queries are slow and run EXPLAIN plans for them. Most big performance problems like you're describing are due to index issues, so that's where you should be looking. Server tuning comes lat - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
Tom Horstmann wrote: Addendum.. Please also try increasing your innodb_log_file_size to a much higher value if you have lots of writes/transactions. Maybe 250MB is a good first try. You need to delete/move the InnoDB logs before restart. Not sure about this, but please also set innodb_log_buffer_size. Try something between 16-32MB if you have many transactions. Ok, we've increased the innodb_log_file_size to 500M, and that has not changed the IO wait at all so far (after 1 hour). Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
Perrin Harkins wrote: What you really need to do is look at which queries are slow and run EXPLAIN plans for them. Most big performance problems like you're describing are due to index issues, so that's where you should be looking. Server tuning comes lat We definitely need to work on re-designing the queries and indexes. We have a less than 50% index usage rate which is disastrous. We'd like to prove InnoDB and move onto that storage engine for the transaction support, MVCC, etc.. but we're finding that performance is poor. Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb/myisam performance issues
Perrin said it right. If your app needs InnoDB (transaction, row level locks...) write it that way. Don't expect performance from a MyIsam compliant app when using InnoDB. TomH -Original Message- From: Josh Miller [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2008 12:42 AM To: Tom Horstmann Cc: mysql@lists.mysql.com Subject: Re: innodb/myisam performance issues Tom Horstmann wrote: Addendum.. Please also try increasing your innodb_log_file_size to a much higher value if you have lots of writes/transactions. Maybe 250MB is a good first try. You need to delete/move the InnoDB logs before restart. Not sure about this, but please also set innodb_log_buffer_size. Try something between 16-32MB if you have many transactions. Ok, we've increased the innodb_log_file_size to 500M, and that has not changed the IO wait at all so far (after 1 hour). Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
On Thu, Sep 4, 2008 at 6:43 PM, Josh Miller [EMAIL PROTECTED] wrote: We'd like to prove InnoDB and move onto that storage engine for the transaction support, MVCC, etc.. but we're finding that performance is poor. Well, thousands of large InnoDB database users prove that the engine itself has good performance, so I'd say you're really at the stage of working on your own indexes now. You probably don't need to change your queries, just the indexes. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
Here are a couple ideas: * Decrease innodb_autoextend_increment to 8 or even 4. You may see additional IO wait because you're pre-allocating space in chunks disproportinate to what you immediately need, causing bursty performance. * If your remaining MyISAM tables don't need it, take 2GB of the key_buffer alocation and put it towards the innodb buffer pool What are the system's specs? What's it's underlying storage? What flags were used when you created the filesystem(s)? What OS/Version of MySQL are you running? Could you send us some iostat output? Thanks and good luck, -Aaron On Thu, Sep 4, 2008 at 1:26 PM, Josh Miller [EMAIL PROTECTED]wrote: Good afternoon, I have recently converted a large table from MyISAM to InnoDB and am experiencing severe performance issues because of it. HTTP response times have gone from avg .25 seconds to avg 2-3 seconds. Details follow: PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one that serves images, one master DB that serves all reads/writes, backup DB that only serves for backup/failover at this time (app being changed to split reads/writes, not yet). The one table that I converted is 130M rows, around 10GB data MyISAM to 22GB InnoDB. There are around 110 tables on the DB total. My.cnf abbreviated settings: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer= 3G sort_buffer_size = 45M max_allowed_packet = 16M table_cache = 2048 tmp_table_size= 512M max_heap_table_size = 512M myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 thread_cache_size = 300 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 600M thread_concurrency = 8 max_connections = 2048 sync_binlog = 1 innodb_buffer_pool_size = 14G innodb_log_file_size = 20M innodb_flush_log_at_trx_commit=1 innodb_flush_method = O_DIRECT skip-innodb-doublewrite innodb_support_xa = 1 innodb_autoextend_increment = 16 innodb_data_file_path = ibdata1:40G:autoextend We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? The plan is to convert all tables to InnoDB which does not seem like a great idea at this point, we're considering moving back to MyISAM. Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innoDB, myISAM and Foreign Keys
Paul West [EMAIL PROTECTED] wrote: Here are some easy questions I hope you can answer. I have been creating innoDB tables in mysql, creating contraints and foreign keys and it runs swimmingly. The server I am now doing mySQL on won't allow me to create other than myISAM tables. Is it true that only innoDB tables support Foreign keys constraints? Yes. Is there an alternative to FK contraints for myISAM tables? No. Is there something I can configure (locally) to allow the creation of innoDB tables? If you use 3.23 version you should install MySQL-Max and specify innodb_data_file_path variable: http://dev.mysql.com/doc/mysql/en/InnoDB_in_MySQL_3.23.html If what i suspect is true, that myISAM tables don't cater for FK contraints, then there usefulness is lost on me. Why have a database if you can't keep the information linked / accurate?! (I'm sure myISAM does have an upside, don't flame me ;) ) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB / MyISAM
-- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 29, 2003 11:29 AM To: Joe Stump Subject: Re: InnoDB / MyISAM Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I've been looking into switching to InnoDB. I have a few questions though. 1.) Can I do joins between InnoDB / MyISAM tables? 2.) Do FK restraints work if the FK referenced is in a MyISAM table? I'm trying to get the best of both worlds. I'd have a data table with FULLTEXT and then have join tables, etc. InnoDB so I can have transactions and FK restraints. I've looked through the archives and couldn't find any answers to these questions. BTW, I'm using 4.0-gamma right now. --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
