Banner
Home News Feeds Planet MySQL
Newsfeeds
Planet MySQL
Planet MySQL - http://www.planetmysql.org/

  • MariaDB 10.1.1: Defragmenting unused space on InnoDB tablespace
    Introduction When you e.g. delete rows, these rows are just marked as deleted not really physically deleted from indexes and free space introduced is not returned to operating system for later reuse. Purge thread will physically delete index keys and rows, but still free space introduced is not returned to operating system and this operation can lead holes on page. If you have variable length rows, this could lead to situation where this free space can’t be used for new rows (if these rows are larger than old ones). User may use OPTIMIZE TABLE or ALTER TABLE <table> ENGINE=InnoDB to reconstruct the table. Unfortunately, running OPTIMIZE TABLE against an InnoDB table stored in the shared table-space file ibdata1 does two things: Makes the table’s data and indexes contiguous inside ibdata1 Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1 New defragmentation In MariaDB 10.1 we have merged Facebooks defragmentation code prepared for MariaDB by Matt, Seong Uck Lee from Kakao. Only major difference to Facebooks code and Matt’s patch is the fact that in MariaDB we decided not to introduce new literals to SQL and no changes to server code. Instead we use already existing OPTIMIZE TABLE and all code changes are inside InnoDB/XtraDB storage engines. To enable this new feature you need to add following to my.cnf (this requirement is to keep the original behavior of OPTIMIZE TABLE for those users that need it).[mysqld] innodb-defragment=1This new defragmentation feature works inplace, thus no new tables are created and there is no need to copy data from old table to new table. Instead this feature loads n pages and tries to move records so that pages would be full of records and frees pages that are fully empty after the operation. New configuration variables innodb_defragment: Enable/disable InnoDB defragmentation. When set to FALSE, all existing defragmentation will be paused. And new defragmentation command will fail. Paused defragmentation commands will resume when this variable is set to TRUE. Default value FALSE. innodb_defragment_n_pages: Number of pages considered at once when merging multiple pages to defragment. Range of 2–32 and default is 7. innodb_defragment_stats_accuracy: How many defragment stats changes there are before the stats are written to persistent storage. Set to 0 meaning disable defragment stats tracking. Default 0. innodb_defragment_fill_factor_n_recs:  How many records of space defragmentation should leave on the page. This variable, together with innodb_defragment_fill_factor, is introduced so defragmentation won’t pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective. Range of 1–100 and default 20. innodb_defragment_fill_factor: A number between [0.7, 1] that tells defragmentation how full it should fill a page. Default is 0.9. Number below 0.7 won’t make much sense. This variable, together with innodb_defragment_fill_factor_n_recs, is introduced so defragmentation won’t pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective. innodb_defragment_frequency: Do not defragment a single index more than this number of time per second.This controls the number of time defragmentation thread can request X_LOCK on an index. Defragmentation thread will check whether 1/defragment_frequency (s) has passed since it worked on this index last time, and put the index back to the queue if not enough time has passed. The actual frequency can only be lower than this given number. New status variables Innodb_defragment_compression_failures: Number of defragment re-compression failures Innodb_defragment_failures: Number of defragment failures. Innodb_defragment_count: Number of defragment operations. Example set @@global.innodb_file_per_table = 1; set @@global.innodb_defragment_n_pages = 32; set @@global.innodb_defragment_fill_factor = 0.95; CREATE TABLE tb_defragment ( pk1 bigint(20) NOT NULL, pk2 bigint(20) NOT NULL, fd4 text, fd5 varchar(50) DEFAULT NULL, PRIMARY KEY (pk1), KEY ix1 (pk2) ) ENGINE=InnoDB; delimiter //; create procedure innodb_insert_proc (repeat_count int) begin declare current_num int; set current_num = 0; while current_num &lt; repeat_count do INSERT INTO tb_defragment VALUES (current_num, 1, REPEAT('Abcdefg', 20), REPEAT('12345',5)); INSERT INTO tb_defragment VALUES (current_num+1, 2, REPEAT('HIJKLM', 20), REPEAT('67890',5)); INSERT INTO tb_defragment VALUES (current_num+2, 3, REPEAT('HIJKLM', 20), REPEAT('67890',5)); INSERT INTO tb_defragment VALUES (current_num+3, 4, REPEAT('HIJKLM', 20), REPEAT('67890',5)); set current_num = current_num + 4; end while; end// delimiter ;// commit; set autocommit=0; call innodb_insert_proc(50000); commit; set autocommit=1;After CREATE TABLE and INSERT operations we can see following from INFORMATION_SCHEMA:select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY'; Value 313 select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1'; Value 72 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed'); count(stat_value) 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split'); count(stat_value) 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) 0 SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB' and table_name like '%tb_defragment%'; table_name data_free_MB table_rows tb_defragment 4.00000000 50051 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` PRIMARY 25873 4739939 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` ix1 50071 1051775Now if we delete 3/4 of the records that will leave holes in pages and then we optimize table to execute defragmentation:delete from tb_defragment where pk2 between 2 and 4; optimize table tb_defragment; Table Op Msg_type Msg_text test.tb_defragment optimize status OK show status like '%innodb_def%'; Variable_name Value Innodb_defragment_compression_failures 0 Innodb_defragment_failures 1 Innodb_defragment_count 4After this we can see that some pages are freed and some pages merged:select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY'; Value 0 select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1'; Value 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed'); count(stat_value) 2 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split'); count(stat_value) 2 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) 2 SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB'; table_name data_free_MB table_rows innodb_index_stats 0.00000000 8 innodb_table_stats 0.00000000 0 tb_defragment 4.00000000 12431 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` PRIMARY 690 102145 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` ix1 5295 111263 Links WebScaleSQL Git repository https://github.com/webscalesql/webscalesql-5.6 Facebook Percona Live presentation: https://www.google.fi/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCQQFjAB&url=https%3A%2F%2Fwww.percona.com%2Flive%2Fmysql-conference-2014%2Fsites%2Fdefault%2Ffiles%2Fslides%2Fdefragmentation.pdf&ei=UgNKVNnZMcHhywP7qwI&usg=AFQjCNGREUpen21jCcy0bchUa6Ro83ol_A&sig2=MDZU2Ue9sX1kB9OusvdiFA

  • Dynamic Columns Tutorial – Part 2: Searching and Updating
    Fri, 2014-10-24 09:21maxmetherThis is a continuation of my previous blog, where we will focus on some more advanced features related to Dynamic Columns. For an introduction to Dynamic Columns please refer to my previous blog. Dynamic Columns within WHERE I started todays example by adding a few more items into my table, namely my two laptops to be able to produce more meaningful results. As with any function, dynamic column functions can generally be used in the WHERE clause of SELECT and other SQL statements which manipulate data. Let's execute another SELECT statement with COLUMN_GET() in the WHERE clause: SELECT id, name AS 'White Items' FROM items WHERE COLUMN_GET(attributes, 'colour' AS CHAR) = 'white'; +----+----------------------+ | id | White Items | +----+----------------------+ | 3 | Samsung Galaxy S5 | | 4 | Samsung Galaxy Pro 3 | +----+----------------------+Notice that we had to specify the data type within the COLUMN_GET(), the AS CHAR in this example. That lets MariaDB know what to expect and how to cast the values it finds. In this case, we instructed MySQL to cast the values as characters. Let's try another dynamic column function, but one that's designed primarily to be used in the WHERE--although it may be used elsewhere. Let's look at the COLUMN_EXISTS() function which merely checks if an attribute exists with a given name. For example we can look for all items that have an OS: SELECT name FROM items WHERE COLUMN_EXISTS(attributes, 'OS'); +---------------------------+ | name | +---------------------------+ | Samsung Galaxy S5 | | Dell Latitude E6510 | | Lenovo ThinkPad Carbon X1 | | Samsung Galaxy Pro 3 | +---------------------------+ 4 rows in set (0.00 sec) More interestingly this function can also be used to find items that do not have a specific attribute. For example let's look at all items that don't have a defined type (if there are such items: SELECT id, name AS 'Typeless Items' FROM items WHERE NOT COLUMN_EXISTS(attributes, 'type'); +----+-----------------+ | id | Typeless Items | +----+-----------------+ | 1 | MariaDB t-shirt | | 2 | MariaDB t-shirt | +----+-----------------+ 2 rows in set (0.00 sec)As you can see, that worked fine. We found two items that do not have a defined value for the type attribute in the attributes column. Updating Dynamic Columns So far we've used dynamic functions as part of SELECT statements to extract data or as part of INSERT statements when inserting the rows. You can also change the existing rows in a table, this can be done by using the Dynamic Column functions in UPDATE statements. If you want to add another name/value pair to a row that already contains a dynamic column, you can use the COLUMN_ADD() function. For example, in the previous example, we determined that there is no type for the MariaDB t-shirt items. I forgot that when inserting the first rows and I didn't realise that all items would eventually have a type. But that's not a big issue, I can now add a type to these items. Let's add a type of t-shirt. Here's how that is done with an UPDATE statement: UPDATE items SET attributes = COLUMN_ADD(attributes,'type','t-shirt') WHERE NOT COLUMN_EXISTS(attributes, 'type');That's simple. Here's how those items look now: SELECT name AS 'Item', COLUMN_LIST(attributes) AS 'Dynamic Column Names' FROM items WHERE name LIKE 'MariaDB%'; +-----------------+------------------------+ | Items | Dynamic Column Names | +-----------------+------------------------+ | MariaDB t-shirt | `size`,`type`,`colour` | | MariaDB t-shirt | `size`,`type`,`colour` | +-----------------+------------------------+Perfect, job well done. Just to verify we can run the NOT COLUMN_EXISTS WHERE clause again: SELECT id, name AS 'Typeless Items' FROM items WHERE NOT COLUMN_EXISTS(attributes, 'type'); Empty set (0.00 sec)That seemed to have worked fine. Ok, now what if we want to remove an attribute from a row in the table? We can use the COLUMN_DELETE() function with the UPDATE statement like this: UPDATE items SET attributes = COLUMN_DELETE(attributes,'type') WHERE name LIKE 'MariaDB%'; SELECT name AS 'Item', COLUMN_LIST(attributes) AS 'Dynamic Column Names' FROM items WHERE name LIKE 'MariaDB%'; +-----------------+----------------------+ | Item | Dynamic Column Names | +-----------------+----------------------+ | MariaDB t-shirt | `size`,`colour` | | MariaDB t-shirt | `size`,`colour` | +-----------------+----------------------+That removed the type attribute again from the two rows of t-shirts. Ok, but what if you want to change a specific attribute, can you do that? The answer is yes. MariaDB does not allow you to “overload” the attribute names, so you cannot have the same attribute twice in the same row. This means that when you use the COLUMN_ADD() function in an UPDATE statement to add an attribute that already exists on a specific row you are basically overwriting the old one. In that sense COLUMN_ADD() works more like the REPLACE statement than an INSERT statement.   That is all for this time, next blog will focus on search efficiency and other more advanced features of Dynamic Columns.   Tags: DeveloperHowtoMariaDB EnterpriseNoSQL About the Author Max Mether As a co-founder Max now manages the field services and training departments at MariaDB and helps advance the MariaDB and MySQL eco-systems around the world. Max is a frequent speaker at LinuxCon, meetups and other conferences around the globe.

  • Abdel-Mawla Gharieb: Galera Cluster and XA Transactions
    A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup. After some investigations, we found a lot of insert queries in state "query end" and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of "XA COMMIT": SQL> SHOW PROCESSLIST; 27 user host:33214 foodmart Query 14440 sleeping XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x SQL> SHOW ENGINE INNODB STATUS; TRANSACTIONS ============ ---TRANSACTION 2DE71D, ACTIVE 14459 sec 9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115 MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart sleeping XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x41544f4d ONE PHASE XA means eXtended Architecture and "XA COMMIT" statement is one of the distributed transactions (XA Transactions) commands which are clearly NOT supported in Galera Cluster and one of its limitations because of possible rollback on commit. The following command can be used to check if XA Transactions are being used by your application or not: SQL> SHOW GLOBAL STATUS LIKE 'Com_xa%'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | Com_xa_commit | 2828094 | | Com_xa_end | 2828221 | | Com_xa_prepare | 0 | | Com_xa_recover | 2205697 | | Com_xa_rollback | 42 | | Com_xa_start | 2828305 | +-------------------+---------+ 6 rows in set (0.00 sec) There are only two possible solutions for this problem: Get rid of all XA transactions in the application to get the Galera Cluster work. Use another HA solution (Active/passive, Master/Slave, ... etc) but not Galera Cluster. Conclusion XA transactions can not be supported in Galera Cluster and that is already stated clearly in the Galera Cluster Limitations. Before Moving to Galera Cluster, it is preferred to go through all its limitations and check whether your application can cope with them or not.

  • FromDual.en: MySQL Environment MyEnv 1.1.2 has been released
    Taxonomy upgrade extras: myenvoperationMySQL Operationsmulti instanceconsolidationBackupcatalogFromDual has the pleasure to announce the release of the new version 1.1.2 of its popular MySQL, Galera, MariaDB and Percona Server multi-instance environment MyEnv. You can download MyEnv from here. In the inconceivable case that you find a bug in MyEnv please report it to our Bugtracker. Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com. Upgrade from 1.1.1 to 1.1.2 # cd ${HOME}/product # tar xf /download/myenv-1.1.2.tar.gz # rm -f myenv # ln -s myenv-1.1.2 myenvIf you are using plug-ins for showMyEnvStatus create all the links in the new directory structure: cd ${HOME}/product/myenv ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/Changes in MyEnv 1.1.2 MyEnv The MySQL Backup Manager was removed from MyEnv and put into its own package which can be downloaded from FromDual Backup/Recovery Manager. OEM Agent is checked now based on process list and not oemagentctl status any more. Makes it much faster. Alias cdc for NDB Cluster directory removed. NDB Cluster is not supported any more. Deprecate alias v and replace by V. Error unlink(/var/lock/subsys/mysql): Permission denied is caught more nicely now. Unknown version in up guessing is improved. MD5 checksum made portable for Darwin OS (Mac OSX). MyEnv Installer innodb_flush_log_at_trx_commit default in template changed. Version guessing improved for installer. Better download support for Percona Server and MariaDB added. mkdir bug fixed. Version check for RedHat made better. Check for lsb_release and SELinux/AppArmor check added for faster finding problems during MySQL installation. Template my.cnf from website is used for creating an intance. Option hideschema is automatically added to the myenv.conf file now. Check and warning implemented if non mysql user is used. Error is caught when wrong user is used. mysql_install_db output made more verbose in case of errors for debugging. Default option changes from Add to Save after instance was changed. Missing users HOME directory is caught now. Question done? can be answered with y now. Comment about waiting during instance installation added. MyEnv Utilities Table offline/online scripts integrated into MyEnv utilities. alter_engine.pl does hide views from Primary Key check now. MySQL Backup Manager The MySQL Backup Manager was removed from MyEnv and put into its own package which can be downloaded from FromDual Backup/Recovery Manager.

  • MySQL 5.6 Full Text Search Throwdown: Webinar Q&A
    Yesterday (Oct. 22) I gave a presentation titled “MySQL 5.6 Full Text Search Throwdown.” If you missed it, you can still register to view the recording and my slides.Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:Q: Does Solr automatically maintain its index against MySQL? Do you have to hit the Solr server with a specific query to keep the index ‘warm’?There are several strategies for updating a Solr index. In my examples, I showed only a “full import” which is what you would do to create an index by reading all the source data.You can also perform a “delta import” periodically, to add a subset of the source data to an existing index, for example to add data that has changed since the last time you updated the Solr index. See the documentation for Using delta-import command and also Using query attribute for both full and delta import.The delta import would typically be something you would invoke from a cron job, perhaps every hour. But that means that a Solr search might not find data that has changed in MySQL more recently than the last delta import. Depending on the application, a delay of up to 60 minutes might be acceptable, or else maybe you have strict requirements that all data must be in sync instantly.You could also update the Solr index one document at a time using its Java API or web service API. This would require you to write code in your application. Every time you INSERT or UPDATE or DELETE a document in MySQL that you want to be kept in sync with the Solr index, you would write more code to do a similar operation in the Solr index. That way every single text change would be searchable nearly immediately.Q: Did you test Elasticsearch? (several people asked about this)I did not test Elasticsearch, but according to their technology overview: “Elasticsearch uses Lucene under the covers.” So I expect that this part of Elasticsearch performs similarly to what I saw from Apache Solr, which also uses Lucene internally.Q: One question I could not understand, how to maintain Sphinx index in sync with data? Can be it in real time?The Sphinx Search index does not automatically refresh as your MySQL data changes. You would have to write application code to invoke the indexing process. There’s a page in the Sphinx Search documentation about Live Index Updates, that gives an overview of the two methods, and links to further reading.This is definitely the most inconvenient aspect of Sphinx Search. Queries are very fast, but it’s expensive to do incremental updates to an index. So it’s ideal for indexing an archive of text that doesn’t change very frequently, but not as easy to use it for indexing rapidly-changing content.Q: I have over 800,000 PDF documents to index (in several languages), any recommendations?I said during the webinar that I recalled there exists tools to extract searchable text from a PDF file. I found one such project called Apache PDFBox includes this capability, and they have a page describing a helper class for doing PDF parsing and extraction combined with Lucene indexing. I haven’t used it myself, so I can’t comment on its performance for indexing 800,000 PDF documents, but it seems like you could write a Java program to iterate over your collection of PDF’s, and index them using this class.Q: What is your suggestion to use Sphinx Search for single column searches?You can use any SQL query in the sphinx.conf to define the source data to index. You can select one column, multiple columns, or even multiple columns from joined tables. The result from any SQL query you write can be used as the data source.Q: Which modules did you use with Sphinx Search? Did you use its built-in stemmers and metaphone package, etc.?I installed the default modules. I don’t know if there is a significant performance difference from using optional packages.Q: What about quality of results from each solution? I remember reading an article on percona.com several months ago comparing MyISAM fulltext vs InnoDB fulltext, and there were concerns about the results from InnoDB. Did you do any testing on this?Indeed, here’s a link to the excellent blog post by my colleague Ernie Souhrada in which he found some surprises in the results from InnoDB FTS: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!I was just doing some comparison for performance in the current MySQL 5.7 milestone. I didn’t compare the query results this time.Q: Is there any full text search in Percona Server with XtraDB?Percona Server is based on the upstream MySQL Community Edition of the respective version number. So Percona Server has the builtin FULLTEXT index types for MyISAM and InnoDB, and we have not changed this part of the code. Percona Server does not bundle Sphinx Search, but it’s not too difficult to install Sphinx Search as a complementary technology, just as you would install other packages that are commonly used parts of an application infrastructure, for example Memcached or HA-proxy.Q: Is MySQL going to improve the built-in InnoDB FTS in the near future?They are continuing to add features that improve FTS, for example:You can now write your own plugins for fulltext parsing (that is, parsing the input data to identify “words” to index; you may have your own idea about how to split text into words).Both B-tree and full-text types now uses bulk-loading to make it faster and more efficient to build the index.I’m not aware of any work to improve the performance of fulltext queries significantly.Q: What is the performance comparison between MyISAM and InnoDB for inline index updating?I didn’t test performance of incremental index updates this time. I only populated my tables from the StackOverflow data using LOAD XML, and then I created fulltext indexes on the populated tables. But I generally favor moving all important data to InnoDB, and not using MyISAM tables. It’s hard to imagine that the performance of index updates would be so much better that would convince me to use MyISAM. It’s more likely that the accuracy of search results would be a good reason to use MyISAM. Even then, I’d keep the original data in InnoDB and use MyISAM only as a copy of the data, to create a disposable fulltext index.Thanks again for attending my webinar! For more great content, please join Percona and the MySQL community at our conference events. The next one is Percona Live London 2014 on November 3-4. We also look forward to the Open Stack Live 2015 in Santa Clara, California April 13-14, in the same venue with Percona Live MySQL Conference and Expo 2015, April 13-16.Also watch more webinars from Percona in the future!The post MySQL 5.6 Full Text Search Throwdown: Webinar Q&A appeared first on MySQL Performance Blog.

Banner

 

Copyright © 2001 - 2013 K-Factor Technologies, Inc.



Site Meter

a href=