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: Wierd INSERT ... SELECT syntax problem
Thanks for the tip. I am looking at just making 16 separate queries. It will be easier to manage and faster to run. Dan On Sep 6, 2008, at 9:37 PM, Brent Baisley <[EMAIL PROTECTED]> wrote: Well, for your simple example, you can use query variables to add the "counters". SET @cntr:=0, @lastVal:='A' INSERT INTO tableB LOC,DATA SELECT CONCAT(LOC, CONCAT( IF(@lastVal=LOC, @cntr:[EMAIL PROTECTED], @cntr:=0), IF(@lastVal:=LOC,'',''))) LOC, CONCAT(DATA, @cntr) FROM tableA ORDER BY LOC That should add a sequential number to LOC and DATA that will reset to 0 whenever the value of LOC changes. Some of the IFs in there are just to suppress output of variable assignment. Hope that helps Brent Baisley On Sep 5, 2008, at 5:44 PM, Dan Tappin wrote: I have an existing data set - here is an example (the real one is more complex than this) LOCDATA - A1 B2 C3 D4 E5 F6 ... and I am looking to run some sort of INSERT ... SELECT on this to make a new table like this: LOCDATA - A010 A111 A212 A313 B020 B121 B222 B323 C030 C131 C232 C333 D040 D141 D242 D343 E050 E151 E252 E353 F060 F161 F262 F363 I basically want to take the data from each row, perform n number of operations on it and insert it into a new table. I could make a PHP script that does this but I figured there had to be a better way. Any ideas? Dan -- 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: use of wildcards or regular expressions in IFNULL, how to create a view that substitutes NULL by 0?
There is no way that I know of to reference every field in a table without using a stored procedure. If you really need to do something like that, and you will be adding new columns frequently, then it's most likely an indication that your table structure is not normalized. Those columns should probably be records with a column indicating what type of data it is. Brent Baisley On Sep 4, 2008, at 5:11 AM, drflxms wrote: Dear MySQL specialists, this is a MySQL-newbie question: I want to create a view of a table, where all NULL-values are substituted by 0. Therefore I tried: SELECT *, IFNULL(*,0) FROM table Unfortunately IFNULL seems not to accept any wildcards like * as placeholder for the column-name. REGEXP didn't work either - well maybe I made a mistake in the syntax? Everything works fine, when I write an IFNULL-command for every column in my table: SELECT *, IFNULL(b1,0) AS b1, IFNULL(b2,0) AS b2, IFNULL(b3,0) AS b3, ... But beside causing a lot of writing-work, this solution has the problem, that it doesn't reflect new columns in the original table in the view, as there is no corresponding IFNULL-command in the view. This is not acceptable in my case. So is there a way to use wildcards/regular expressions in IFNULL? Is there another way to create a view that substitutes every NULL-value with 0? I'd appreciate any kind of help very much! Kind regards and greetings from Munich, Felix -- 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: Wierd INSERT ... SELECT syntax problem
Well, for your simple example, you can use query variables to add the "counters". SET @cntr:=0, @lastVal:='A' INSERT INTO tableB LOC,DATA SELECT CONCAT(LOC, CONCAT( IF(@lastVal=LOC, @cntr:[EMAIL PROTECTED], @cntr:=0), IF(@lastVal:=LOC,'',''))) LOC, CONCAT(DATA, @cntr) FROM tableA ORDER BY LOC That should add a sequential number to LOC and DATA that will reset to 0 whenever the value of LOC changes. Some of the IFs in there are just to suppress output of variable assignment. Hope that helps Brent Baisley On Sep 5, 2008, at 5:44 PM, Dan Tappin wrote: I have an existing data set - here is an example (the real one is more complex than this) LOC DATA - A 1 B 2 C 3 D 4 E 5 F 6 ... and I am looking to run some sort of INSERT ... SELECT on this to make a new table like this: LOC DATA - A0 10 A1 11 A2 12 A3 13 B0 20 B1 21 B2 22 B3 23 C0 30 C1 31 C2 32 C3 33 D0 40 D1 41 D2 42 D3 43 E0 50 E1 51 E2 52 E3 53 F0 60 F1 61 F2 62 F3 63 I basically want to take the data from each row, perform n number of operations on it and insert it into a new table. I could make a PHP script that does this but I figured there had to be a better way. Any ideas? Dan -- 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: Selecting around a circular reference?
Did you try this? SELECT sum(fooditems.carb * mealitems.quantity) as sumcarbs, sum(fooditems.gi * ((fooditems.carb * mealitems.quantity) / sum(fooditems.carb * mealitems.quantity))), sum(fooditems.gl * mealitems.quantity), sum(fooditems.cal * mealitems.quantity) FROM meals INNER JOIN mealitems ON meals.meal_id = mealitems.meal_id INNER JOIN fooditems ON mealitems.fooditem_id = fooditems.fooditem_id WHERE meals.user_id = '$user_id' AND meals.date = '$meal_date'"; -Micah On 09/05/2008 03:41 PM, Brian Dunning wrote: I'm trying to calculate glycemic index of all food items eaten in a day with a single SELECT. The problem is the calculation for glycemic index of each item requires a total of all items' carbs. It's like a circular reference. Here's what I'm trying: SELECT sum(fooditems.carb * mealitems.quantity) as sumcarbs, sum(fooditems.gi * ((fooditems.carb * mealitems.quantity) / sumcarbs)), sum(fooditems.gl * mealitems.quantity), sum(fooditems.cal * mealitems.quantity) FROM meals INNER JOIN mealitems ON meals.meal_id = mealitems.meal_id INNER JOIN fooditems ON mealitems.fooditem_id = fooditems.fooditem_id WHERE meals.user_id = '$user_id' AND meals.date = '$meal_date'"; It replies "Unknown column 'sumcarbs' in 'field list'". See what I'm trying to do? Is there a way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyQuery 2.1 beta available
All! Sorry for crossposting again, but I hope that is OK. I have now released MyQuery 2.1 beta, the MySQL Query and Scripting tool. 2.1 introduces one major feature and a few minor ones, as well as a few fixes. - Major feature: Support for Multiple resultsets (from CALL commands usually). - Minor feature: Optional profress icon in the Windows "tray". - Minor feature: .sql File associations. - Minor feature: Wait cursor during processing. - Minor feature: A few more installer features. - Fixed a few memory leaks. - Prepared a bit more for Unicode, although I'm still far from ready done this. Download MySQL 2.1, inslcuding a complete windows installer (including PDF documentation), PDF documentation and sourcecode from: https://sourceforge.net/projects/myquery/ Happy SQL'ing /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm <___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
