ANN: Meet the MicroOLAP Database Designer for MySQL 2.1.0!!!
Improved and enhanced long awaited release is available. It introduces internal and architectural improvements. New compiler used as well as new exception tracer implemented. Some little changes and bug fixes are present as well. Full changelog: [!] New exception tracer tool used [!] New compiler used in development [*] SQL Executor: Error dialog now centered instead of left upper corner positioning [*] Undo\Redo functionality now works for Quick Insert actions in the Advanced SQL Editor [-] Designer doesn't restore itself to maximized state after restart bug fixed [-] Drag-and-drop multiple selected items causes all to go in top left hand corner bug fixed You're welcome to download the Database Designer for MySQL 2.1.0 right now at: http://microolap.com/products/database/mysql-designer/download/ Login to your private area on our site at http://microolap.com/my/keys/ to obtain your key if you have a license. Please don't hesitate to ask any questions or report bugs with our Support Ticketing system available at http://www.microolap.com/support/
MyISAM index missing rows
Hi Everyone, I have a MyISAM table with 25 billion rows (structure: id1 int, id2 int, score float), and after I create an index on id1, certain rows can no longer be found. I've posted a detailed summary of my problem at dba.stackexchange.com, but haven't had success with finding a solution thus far. Here's the URL to that post: http://dba.stackexchange.com/questions/47906/mysql-myisam-index-causes-query-to-match-no-rows-indexes-disabled-rows-match As that post describes, one oddity is with an EXPLAIN I run on the same query with indexes enabled vs. disabled. When disabled, rows = 25 billion; when enabled, rows = 170 million. Based on this, I'm wondering if some restriction is causing only 170 million rows to index. (Of course, I could be completely misinterpreting this EXPLAIN result). Here is my server version: 5.5.31-0ubuntu0.12.04.2 (Ubuntu), which should also be 64-bit (i've verified by running file /usr/sbin/mysqld (says ELF 64 bit..) Any help is greatly appreciated! Just let me know if you need more details Sincerely, Dolan Antenucci
RE: MyISAM index missing rows
certain rows can no longer be found -- Do CHECK TABLE. (It will take a lng time.) It may tell you to REPAIR TABLE, which will also take a lng time; but it will be necessary. (This is a strong reason for going to InnoDB. But it will be 2x-3x bigger on disk.) -Original Message- From: Dolan Antenucci [mailto:antenucc...@gmail.com] Sent: Monday, August 12, 2013 10:26 AM To: mysql@lists.mysql.com Subject: MyISAM index missing rows Hi Everyone, I have a MyISAM table with 25 billion rows (structure: id1 int, id2 int, score float), and after I create an index on id1, certain rows can no longer be found. I've posted a detailed summary of my problem at dba.stackexchange.com, but haven't had success with finding a solution thus far. Here's the URL to that post: http://dba.stackexchange.com/questions/47906/mysql-myisam-index-causes- query-to-match-no-rows-indexes-disabled-rows-match As that post describes, one oddity is with an EXPLAIN I run on the same query with indexes enabled vs. disabled. When disabled, rows = 25 billion; when enabled, rows = 170 million. Based on this, I'm wondering if some restriction is causing only 170 million rows to index. (Of course, I could be completely misinterpreting this EXPLAIN result). Here is my server version: 5.5.31-0ubuntu0.12.04.2 (Ubuntu), which should also be 64-bit (i've verified by running file /usr/sbin/mysqld (says ELF 64 bit..) Any help is greatly appreciated! Just let me know if you need more details Sincerely, Dolan Antenucci -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Concurrent read performance problems
Please provide SHOW CREATE TABLE and SHOW TABLE STATUS for each table. It smells like there is an inconsistency in the datatype of facts.accounts.id and what it is JOINing to. Also provide the full SELECT. How much RAM do you have? -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Sunday, August 11, 2013 2:16 PM To: Brad Heller Cc: Johnny Withers; MySQL General List Subject: Re: Concurrent read performance problems Good to hear. A word of warning, though: make sure you don't have more connections allocating those buffers than your machine can handle memory- wise, or you'll start swapping and performance will REALLY go down the drain. A query/index based solution would still be preferred. Could you for instance materialize that subselect and periodically refresh it? Other tricks may also be available. Brad Heller b...@cloudability.com wrote: Johan, your suggestion to tweak max_heap_table_size and tmp_table_size fixed the issue. Bumping them both to 512MB got our performance back on-par. I came up with a way to avoid the contention using a complex set of temp tables, but performance was abysmal. By reverting to the more straight-forward query with the subselect as well as tweaking the max_healp_table_size and tmp_table_size I saw no resource contention causing slowdowns, as well as a 12x performance boost. Thanks for your help! *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman vegiv...@tuxera.bewrote: True, which is why I said I suspected file-based sort :-) At one million rows, that seems to be an accurate guess, too. Still on the phone, though, and in bed. I'll read the thread better tomorrow, but you might get some benefit from cutting out the subselect if that's possible. If you have plenty of memory, have a look at the max_temp_table_size and max_heap_table variables, too; those decide when the sort goes to disk. Johnny Withers joh...@pixelated.net wrote: Just because it says filrsort doesn't mean it'll create a file on disk. Table schema and full query would be helpful here too http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort -mean-in-mysql/ On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote: Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like there is a lot of filesort goin' on here. Also note that I'm only using the first two fields of the covering index (intentionally). +--+-++---+ -- ++-+ -- +-+ -- + | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | +--+-++---+ -- ++-+ -- +-+ -- + |1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1004685 | Using temporary; Using filesort | |2 | DERIVED | accounts | range | PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | NULL | 3 | Using where; Using index; Using temporary; Using filesort | |2 | DERIVED | facts | ref | covering | covering | 4 | facts.accounts.id| 334895 | Using where | +--+-++---+ -- ++-+ -- +-+ -- + *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be wrote: On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:*
