Chef Experiments - Create host files

I am in the process of experimenting with Chef and here's one of them

knife create cookbook host_file_update


Then create the recipe

recipes/default.rb

hosts = search(:node, "*:*")
template "/etc/hosts" do
  source "hosts.erb"
  owner "root"
  group "root"
  mode 0644
  variables(
    :hosts => hosts,
    :hostname => node[:hostname],
    :fqdn => node[:fqdn]
  )
end

And then the template file hosts.erb referenced above

templates/default/hosts.erb 

127.0.0.1   localhost

<% @hosts.each do |node| %>
<%= node['ipaddress'] %> <%= node['hostname'] %> <%= node['fqdn'] %>
<% end %>

Pretty useful, if you want to populate this automatically as and when you add servers.  One of the next things to try is see if we can make Chef pick the additional IPs (e.g service net in Rackspace cloud) and create separate entries for it

Mysql Information Schema

Re-publishing from my wiki.

• Show tables that use Barracuda disk format

select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND ( ROW_FORMAT='Compressed' OR ROW_FORMAT='Dynamic'); 

• Show me all tables that are InnoDB 

SELECT `table_schema`, `table_name` FROM `information_schema`.`TABLES` WHERE `Engine`='Innodb' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

• Show me all tables that are MyISAM 

SELECT `table_schema`, `table_name` FROM `information_schema`.`TABLES` WHERE `Engine`='MyISAM' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

• Print Queries to aid in conversion FROM MyISAM to InnoDB 

 use `information_schema`; SELECT CONCAT("ALTER TABLE `" , `TABLE_SCHEMA`, "`.`", `table_name`, "` Engine=Innodb;") AS "" FROM `information_schema`.`TABLES` WHERE `Engine`='MyISAM' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

You can save the above in a file and run this
mysql --batch < input.sql > out.sql 

• Show me a count of tables grouped by engine type 

SELECT `Engine`, count(*) as Total FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' GROUP BY `Engine`;

• Show me the datasize and index size of all tables grouped by engine type

SELECT `Engine`, COUNT(ENGINE), sum(data_length)/(1024*1024*1024) as 'Datasize-GB', sum(index_length)/(1024*1024*1024) as 'Indexsize-GB' FROM `information_schema`.`TABLES` GROUP BY `Engine`; 

• Show me the top 10 tables by size outside of information_schema and mysql 

SELECT TABLE_SCHEMA, TABLE_NAME,data_length/1024*1024 FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' ORDER BY `data_length` DESC LIMIT 10; 

• Tables without indexes

USE `information_schema`; SELECT CONCAT(TABLES.table_schema,".",TABLES.table_name) as name, `TABLES`.`TABLE_TYPE`,`TABLE_ROWS` FROM `TABLES` LEFT JOIN `TABLE_CONSTRAINTS` ON `TABLES`.`table_schema` = `TABLE_CONSTRAINTS`.`table_schema` AND `TABLES`.`table_name` = `TABLE_CONSTRAINTS`.`table_name` AND `TABLE_CONSTRAINTS`.`constraint_type` = 'PRIMARY KEY' WHERE `TABLE_CONSTRAINTS`.`constraint_name` IS NULL; Check for redundant indexes SELECT * FROM ( SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME`, GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`) AS columns FROM `information_schema`.`STATISTICS` WHERE `TABLE_SCHEMA` NOT IN ('mysql', 'INFORMATION_SCHEMA') AND NON_UNIQUE = 1 AND INDEX_TYPE='BTREE' GROUP BY `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME` ) AS i1 INNER JOIN ( SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME`, GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`) AS columns FROM `information_schema`.`STATISTICS` WHERE INDEX_TYPE='BTREE' GROUP BY `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME` ) AS i2 USING (`TABLE_SCHEMA`, `TABLE_NAME`) WHERE i1.columns != i2.columns AND LOCATE(CONCAT(i1.columns, ','), i2.columns) = 1 

• List character sets 

 SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `CHARACTER_SET_NAME`, `TABLE_COLLATION` FROM `INFORMATION_SCHEMA`.`TABLES` INNER JOIN `INFORMATION_SCHEMA`.`COLLATION_CHARACTER_SET_APPLICABILITY` ON (`TABLES`.`TABLE_COLLATION` = `COLLATION_CHARACTER_SET_APPLICABILITY`.`COLLATION_NAME`) WHERE `TABLES`.`TABLE_SCHEMA` !='information_schema' AND `TABLES`.`TABLE_SCHEMA` !='mysql' ;

• List average row length and index length 

SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `AVG_ROW_LENGTH`, `DATA_LENGTH`, `INDEX_LENGTH` FROM `TABLES` ORDER BY `AVG_ROW_LENGTH` DESC LIMIT 15; 

• Oldest tables with respect to update times 

SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `UPDATE_TIME` FROM `TABLES` WHERE `UPDATE_TIME` IS NOT NULL ORDER BY `UPDATE_TIME` LIMIT 10; 

• Tables with foreign keys 

SELECT * FROM `table_constraints` WHERE `constraint_type` = 'FOREIGN KEY' ; 

• List of indexes and their total count 

SELECT `INDEX_TYPE`, count(*) as NUM FROM `STATISTICS` group by `INFORMATION_SCHEMA`.`INDEX_TYPE`; 

• Get a summary of privileges 

SELECT * from `INFORMATION_SCHEMA`.`USER_PRIVILEGES`;

Rackspace cloud files - symlinks / aliases

The requirement is to have multiple names to the same object. For starters, there is no inbuilt way to do aliases or multiple names to the same object. However, after some documentation trolling there is a way to achieve it, although it is not straightforward.

Cloud files offers large file (over 5G) support by allowing multiple segments to be uploaded and then a manifest that links the segments.

http://www.rackspace.com/blog/rackspace-cloud-files-now-supporting-extremely-large-file-sizes/

You can use this feature to sort of achieve symlinking/aliasing

Here's a small example

curl -D - \

     -H "X-Auth-Key: AUTH key" \

     -H "X-Auth-User: user" \

     https://lon.identity.api.rackspacecloud.com/v1.0



curl -X PUT -H 'X-Auth-Token: AUTH TOKEN' \

https://storage101.lon3.clouddrive.com/v1/<URL>/stream/imagedata1/1 --data-binary 'Image1'



curl -X PUT -H 'X-Auth-Token: AUTH TOKEN' \

-H 'X-Object-Manifest: sriramrajan.com/imagedata1/' \

https://storage101.lon3.clouddrive.com/v1/<URL>/stream/image1.txt --data-binary ''



curl -X PUT -H 'X-Auth-Token: AUTH TOKEN' \

-H 'X-Object-Manifest: sriramrajan.com/imagedata1/' \

https://storage101.lon3.clouddrive.com/v1/<URL>/stream/image2.txt --data-binary ''

This can be on CDN enabled containers as well and so once you do this, the following will be technically pointing to the same object.

<CDN URL>/image1.txt

<CDN URL>/image2.txt

MySQL Versions - A survey

Different versions

MySQL versions make interesting and at times confusing reading. Here's a list of the version soup.

• Version 5.2 was re-branded as version 6.0.
• Version 6.0 was then cancelled.
• Version 5.4 then replaced version 6.0.
• Version 5.4 was then re-branded as version 5.5.
• MySQL NDB cluster comes with it's own versioning system. As of this writing 7.2 is the latest release.
• Innodb, the most used engine in MySQL now has it's own versioning. MySQL 5.0 had Innodb version 1.0.xx and MySQL 5.5 has innodb version 1.1.xx.
• MySQL 5.6 will be the next release from MySQL(Oracle). For a full feature list see http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html

Forks and Patches

On top of this there are quite a few forks and patches.

• Drizzle was a fork of MySQL 6.0. Drizzle is probably the only true fork, has fully re-factored code and, is in active development.
• MariaDB is a release driven by Michael "Monty" Widenius, the original author of MySQL. MySQL 5.1 was the basis for MariaDB and as of this writing the latest version is Maria DB 5.5.
• Percona has several patches for both MySQL 5.1 and MySQL 5.5. Percona patches are a re-base of the main release and XtraDB is a re-base of InnoDB. Both Percona Server and XtraDB is also not a true forks of MySQL or Innodb. They have some very good performance & monitoring enhancements.
• Facebook also releases patches for MySQL.  These are done for specific Facebook requirements but as is often the case some of these patches eventually find their way into main releases.  These can be found at https://www.facebook.com/MySQLatFacebook
• OurDelta is another set of patches released by former MySQL employee Arjen Lentz.  This is now more aligned with MariaDB. These patches are similar to the Percona ones in terms of adding extra functionality to existing releases.

3rd Party Storage Engines

Outside of InnoDB and MyISAM, there are few 3rd party plugins that suit specific needs as well.

• TokuDB - A transactional engine which is available as a plugin. It provides some improvements for large databases or write heavy environments. More at http://www.tokutek.com/products/tokudb-for-mysql/
• PBXT -  A transactional engine like InnoDB and fully ACID compliant. It has not had much maintenance in the past 2 years and so is close to being defunct.
• InfiniDB - Open source engine by Calpont  is suited for data warehousing and read-intensive applications. More at http://infinidb.org/about-us/infinidb
• Infobright - Infobright engine for MySQL is another one aimed at date warehousing and large datasets. More at http://www.infobright.com/Products/MySQL-Integration/

Selecting the right one

• For most part, staying with the Oracle releases will suit most application needs. These releases are in active development, generally stable and have binary versions for most systems.
• MariaDB is binary compatible with MySQL and may suit certain needs. It also includes XtraDB from Percona. For a good comparison and the various incompatibilities, refer to http://kb.askmonty.org/en/mariadb-versus-mysql/
• Going with a fork like Drizzle will likely require some application change. Drizzle does not maintain server level compatibility but does talk the MySQL protocol. For a full list of differences refer to http://docs.drizzle.org/mysql_differences.html
• The Percona patches and versions provide a more seamless migration as they are generally fully compatible with the MySQL releases. If you are concerned about vendor support , Percona also provides that. Percona toolkit (formerly MaatKit) is a good addition as it provides some nice tools to automate a variety of tasks. This toolkit can be used with any MySQL release and so you don't need to run Percona server.
• The 3rd party engines come into a play only if are looking for specific features that come with the engines.

Links & further reading

• MySQL 5.6 features - http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html
• MariaDB Information - http://kb.askmonty.org/en/mariadb/
• OurDelta Homepage - http://ourdelta.org/
• Drizzle differences - http://docs.drizzle.org/mysql_differences.html
• Percona server - http://www.percona.com/software/percona-server/

URL fun

Some interesting ways, URLs work


http://sriramrajan.com - The conventional one

http://46.38.167.114 - The IP addresss

http://774285170 - The decimal converted

http://0x2e.0x26.0xa7.0x72 - The hexadecimal converted

http://0x2e26a772 -  Another hexadecimal variant