Joomla templates JV Spino

HOT NEWS

News - Collaboration: Wii Sports Resort - Friday, 21 May 2010 07:07

News - Carousel: Aenean accumsan - Friday, 21 May 2010 07:07

News - Carousel: Sociis natoque - Friday, 21 May 2010 07:07

News - Carousel: Suspendisse - Friday, 21 May 2010 07:07

News - Carousel: Pellentesque - Friday, 21 May 2010 07:07

News - Carousel: Praesent neque - Friday, 21 May 2010 07:07

About Joomla! - The Project: Typography - Friday, 21 May 2010 09:40

Newsfeeds

Planet MySQL

Planet MySQL - http://www.planetmysql.org/

ZFS on Linux and MySQL
I am currently working with a large customer and I am involved with servers located in two data centers, one with Solaris servers and the other one with Linux servers. The Solaris side is cleverly setup using zones and ZFS and this provides a very low virtualization overhead. I learned quite a lot about these technologies while looking at this, thanks to Corey Mosher.On the Linux side, we recently deployed a pair on servers for backup purpose, boxes with 64 300GB SAS drives, 3 raid controllers and 192GB of RAM. These servers will run a few slave instances each of production database servers and will perform the backups. The write load is not excessive so a single server can easily handle the write load of all the MySQL instances. The original idea was to configure them with raid-10 + LVM, making sure to stripe the LV when we need to and align the partition correctly.We got decent tpcc performance, nearly 37k NoTPM using 5.6.11 and xfs. Then, since ZFS on Linux is available and there is in house ZFS knowledge, we decided to reconfigure one of the server and give ZFS a try. So I trashed the raid-10 arrays, configure JBODs and gave all those drives to ZFS (30 mirrors + spares + OS partition mirror) and I limited the ARC size to 4GB. I don’t want to start a war but ZFS performance level was less than half of xfs for the tpcc test and that’s maybe just normal. We didn’t try too hard to get better performance because we already had more than enough for our purpose and some ZFS features are just too useful for backups (most apply also for btrfs). Let’s review them.SnapshotsZFS does snapshot, like LVM but… since it is a copy on write filesystem, the snapshots are free, no performance penalty. You can easily run a server with hundreds of snapshots. With LVM, your IO performance drops to 33% after the first snapshot so keeping a large number of snapshots running is simply not an option. With ZFS you can easily have:one snapshot per day for the last 30 daysone snapshot per hour for the last 2 daysone snapshot per 5min for the last 2 hoursand that will be perfectly fine. Since starting a snapshot take less than a second, you could even be more zealous. Pretty interesting to speed up point in time recovery when you dataset is 700GB. If you google a bit with “zfs snapshot script” you’ll many scripts ready for the task. Snapshots work best with InnoDB, with MyISAM you’ll have to start the snapshot while holding a “flush tables with read lock” and the flush operation will take some time to complete.CompressionZFS can compress data on the fly and it is surprisingly cheap. In fact the best tpcc results I got were when using compression. I still have to explain this, maybe it is related to better raid controller write cache use. Even the fairly slow gzip-1 mode works well. The tpcc database, which contains a lot of random data that doesn’t compress well showed a compression ration of 1.70 with gzip-1. Real data will compress much more. That gives us much more disk space than we expected so even more snapshots!IntegrityWith ZFS each record on disk has a checksum. If a cosmic ray flip a bit on a drive, instead of crashing InnoDB, it will be caught by ZFS and the data will be read from the other drive in the mirror.Better availability and disk usageOn purpose, I allocated mirror pairs using drives from different controllers. That way, if a controller dies, the storage will still be working. Also, instead of having 1 or 2 spare drives per controller, I have 2 for the whole setup. A small but yet interesting saving.All put together, ZFS on Linux is a very interesting solution for MySQL backup servers. All backup solutions have an impact on performance with ZFS the impact is up front and the backups are almost free.The post ZFS on Linux and MySQL appeared first on MySQL Performance Blog.
MySQL Applier For Hadoop: Implementation
This is a follow up post, describing the implementation details of Hadoop Applier, and steps to configure and install it. Hadoop Applier integrates MySQL with Hadoop providing the real-time replication of INSERTs to HDFS, and hence can be consumed by the data stores working on top of Hadoop. You can know more about the design rationale and per-requisites in the previous post. Design and Implementation:Hadoop Applier replicates rows inserted into a table in MySQL to the Hadoop Distributed File System(HDFS). It uses an API provided by libhdfs, a C library to manipulate files in HDFS.The library comes pre-compiled with Hadoop distributions.It connects to the MySQL master (or read a binary log generated by MySQL) and:fetches the row insert events occurring on the masterdecodes these events, extracts data inserted into each field of the rowuses content handlers to get it in the format required and appends it to a text file in HDFS.Schema equivalence is a simple mapping:Databases are mapped as separate directories, with tables in them as sub-directories. Data inserted into each table is written into text files (named as datafile1.txt) in HDFS. Data can be in comma separated format; or any other delimiter can be used, that is configurable by command line arguments. The diagram explains the mapping between MySQL and HDFS schema.The file in which the data is stored is named datafile1.txt here; you can name is anything you want. The working directory where this datafile goes is base_dir/db_name.db/tb_name. The timestamp at which the event occurs is included as the first field in each row inserted in the text file.The implementation follows these steps:- Connect to the MySQL master using the interfaces to the binary log#include “binlog_api.h” Binary_log binlog(create_transport(mysql_uri.c_str())); binlog.connect();- Register content handlers/*Table_index is a sub class of Content_handler class in the Binlog API*/ Table_index table_event_hdlr; Applier replay_hndlr(&table_event_hdlr, &sqltohdfs_obj); binlog.content_handler_pipeline()->push_back(&table_event_hdlr); binlog.content_handler_pipeline()->push_back(&replay_hndlr);- Start an event loop and wait for the events to occur on the masterwhile (true) { /* Pull events from the master. This is the heart beat of the event listener. */ Binary_log_event *event; binlog.wait_for_next_event(&event); } - Decode the event using the content handler interfaces class Applier : public mysql::Content_handler { public: Applier(Table_index *index, HDFSSchema *mysqltohdfs_obj) { m_table_index= index; m_hdfs_schema= mysqltohdfs_obj; } mysql::Binary_log_event *process_event(mysql::Row_event *rev) { int table_id= rev->table_id; typedef std::map<long int, mysql::Table_map_event *> Int2event_map; int2event_map::iterator ti_it= m_table_index->find(table_id);- Each row event contains multiple rows and fields.Iterate one row at a time using Row_iterator.mysql::Row_event_set rows(rev, ti_it->second); mysql::Row_event_set::iterator it= rows.begin(); do { mysql::Row_of_fields fields= *it; long int timestamp= rev->header()->timestamp; if (rev->get_event_type() == mysql::WRITE_ROWS_EVENT) table_insert(db_name, table_name, fields, timestamp, m_hdfs_schema); } while (++it != rows.end());- Get the field data separated by field delimiters and row delimiters. Each row contains a vector of Value objects. The converter allows us to transform the value into another representation.mysql::Row_of_fields::const_iterator field_it= fields.begin(); mysql::Converter converter; std::ostringstream data; data << timestamp; do { field_index_counter++; std::vector<long int>::iterator it; std::string str; converter.to(str, *field_it); data << sqltohdfs_obj->hdfs_field_delim; data << str; } while (++field_it != fields.end()); data << sqltohdfs_obj->hdfs_row_delim;- Connect to the HDFS file system. If not provided, the connection information (user name, password host and port) are read from the XML configuration file, hadoop-site.xml. HdfsFS m_fs= hdfsConnect(host.c_str(), port);- Create the directory structure in HDFS. Set the working directory and open the file in append mode. hdfsSetWorkingDirectory(m_fs, (stream_dir_path.str()).c_str()); const char* write_path= "datafile1.txt"; hdfsFile writeFile; - Append data at the end of the file. writeFile= hdfsOpenFile(m_fs, write_path, O_WRONLY|O_APPEND, 0, 0, 0); tSize num_written_bytes = hdfsWrite(m_fs, writeFile, (void*)data, strlen(data)); Install and Configure:Follow these steps to install and run the Applier: 1. Download a Hadoop release (I am using 1.0.4); configure and install (for the purpose of the demo, install it in pseudo distributed mode). Flag 'dfs.support.append'must be set to true while configuring HDFS(hdfs-site.xml). Since append is not supported in Hadoop 1.x, set the flag 'dfs.support.broken.append' to true. 2. Set the environment variable $HADOOP_HOME to point to the Hadoop installation directory.3. CMake doesn't come with a 'find' module for libhdfs. Ensure that the 'FindHDFS.cmake' is in the CMAKE_MODULE_PATH. You can download a copy here. 4. Edit the file 'FindHDFS.cmake', if necessary, to have HDFS_LIB_PATHS set as a path to libhdfs.so, and HDFS_INCLUDE_DIRS have the path pointing to the location of hdfs.h. For 1.x versions, library path is $ENV{HADOOP_HOME}/c++/Linux-i386-32/lib , and header files are contained in $ENV{HADOOP_HOME}/src/c++/libhdfs. For 2.x releases, header files and libraries can be found in $ENV{HADOOP_HOME}/lib/native, and $ENV{HADOOP_HOME}/include respectively.For versions 1.x, this patch will fix the paths: --- a/cmake_modules/FindHDFS.cmake +++ b/cmake_modules/FindHDFS.cmake @@ -11,6 +11,7 @@ exec_program(hadoop ARGS version OUTPUT_VARIABLEHadoop_VERSION # currently only looking in HADOOP_HOME find_path(HDFS_INCLUDE_DIR hdfs.h PATHS $ENV{HADOOP_HOME}/include/ + $ENV{HADOOP_HOME}/src/c++/libhdfs/ # make sure we don't accidentally pick up a different version NO_DEFAULT_PATH ) @@ -26,9 +27,9 @@ endif() message(STATUS "Architecture: ${arch_hint}") if ("${arch_hint}" STREQUAL "x64") - set(HDFS_LIB_PATHS $ENV{HADOOP_HOME}/lib/native) + set(HDFS_LIB_PATHS $ENV{HADOOP_HOME}/c++/Linux-amd64-64/lib) else () - set(HDFS_LIB_PATHS $ENV{HADOOP_HOME}/lib/native) + set(HDFS_LIB_PATHS $ENV{HADOOP_HOME}/c++/Linux-i386-32/lib) endif () message(STATUS "HDFS_LIB_PATHS: ${HDFS_LIB_PATHS}")5.Since libhdfs is JNI based API, it requires JNI header files and libraries to build. If there exists a module FindJNI.cmake in the CMAKE_MODULE_PATH and JAVA_HOME is set; the headers will be included, and the libraries would be linked to. If not, it will be required to include the headers and load the libraries separately (modify LD_LIBRARY_PATH). 6. Build and install the library 'libreplication', to be used by Hadoop Applier,using CMake. Download a copy of Hadoop Applier from http://labs.mysql.com.'mysqlclient' library is required to be installed in the default library paths. You can either download and install it (you can get a copy here), or set the environment variable $MYSQL_DIR to point to the parent directory of MySQL source code. Make sure to run cmake on MySQL source directory. $export MYSQL_DIR=/usr/local/mysql-5.6 Run the 'cmake' command on the parent directory of the Hadoop Applier source. This will generate the necessary Makefiles. Make sure to set cmake option ENABLE_DOWNLOADS=1; which will install Google Test required to run the unit tests. $cmake . -DENABLE_DOWNLOADS=1Run 'make' and 'make install' to build and install. This will install the library 'libreplication' which is to be used by Hadoop Applier. 7. Make sure to set the CLASSPATH to all the hadoop jars needed to run Hadoop itself. $export PATH=$HADOOP_HOME/bin:$PATH $export CLASSPATH=$(hadoop classpath) 8. The code for Hadoop Applier can be found in /examples/mysql2hdfs, in the Hadoop Applier repository. To compile, you can simply load the libraries (modify LD_LIBRARY_PATH if required), and run the command “make happlier” on your terminal. This will create an executable file in the mysql2hdfs directory... and then you are done! Now run hadoop dfs (namenode and datanode), start a MySQL server as master with row based replication (you can use mtr rpl suite for testing purposes : $MySQL-5.6/mysql-test$./mtr --start --suite=rpl --mysqld=--binlog_format='ROW' --mysqld=--binlog_checksum=NONE), start hive (optional) and run the executable ./happlier, optionally providing MySQL and HDFS uri's and other available command line options. (./happlier –help for more info).There are useful filters as command line options to the Hadoop applier. Options Use -r, --field-delimiter=DELIMUse DELIM instead of ctrl-A for field delimiter. DELIM can be a string or an ASCII value in the format '\nnn' .Escape sequences are not allowed. Provide the string by which the fields in a row will be separated. By default, it is set to ctrl-A -w, --row-delimiter=DELIMUse DELIM instead of LINE FEED for row delimiter . DELIM can be a string or an ASCII value in the format '\nnn' Escape sequences are not allowed. Provide the string by which the rows of a table will be separated. By default, it is set to LINE FEED (\n) -d, --databases=DB_LISTDB-LIST is made up of one database name, or many names separated by commas. Each database name can be optionally followed by table names. The table names must follow the database name, separated by HYPHENS Example: -d=db_name1-table1-table2,dbname2-table1,dbname3 Import entries for some databases, optionally include only specified tables. -f, --fields=LISTSimilar to cut command, LIST is made up of one range, or many ranges separated by commas.Each range is one of: N N'th byte, character or field, counted from 1 N- from N'th byte, character or field, to end of line N-M from N'th to M'th (included) byte, character or field -M from first to M'th (included) byte, character or field Import entries for some fields only in a table -h, --help Display help Integration with HIVE:Hiveruns on top of Hadoop. It is sufficient to install Hive only on the Hadoop master node.Take note of the default data warehouse directory, set as a property in hive-default.xml.template configuration file. This must be the same as the base directory into which Hadoop Applier writes.Since the Applier does not import DDL statements; you have to create similar schema's on both MySQL and Hive, i.e. set up a similar database in Hive using Hive QL(Hive Query Language). Since timestamps are inserted as first field in HDFS files,you must take this into account while creating tables in Hive. SQL Query Hive Query CREATE TABLE t (i INT); CREATE TABLE t ( time_stamp INT, i INT) [ROW FORMAT DELIMITED] STORED AS TEXTFILE; Now, when any row is inserted into table on MySQL databases, a corresponding entry is made in the Hive tables. Watch the demo to get a better understanding.The demo is non audio, and is meant to be followed in conjunction with the blog.You can also create an external table in hive and load data into the tables; its your choice! Watch the Hadoop Applier Demo >> Limitations of the Applier:In the first version we support WRITE_ROW_EVENTS, i.e. only insert statements are replicated. We have considered adding support for deletes, updates and DDL's as well, but they are more complicated to handle and we are not sure how much interest there is in this.We would very much appreciate your feedback on requirements - please use the comments section of this blog to let us know!The Hadoop Applier is compatible with MySQL 5.6, however it does not import events if binlog checksums are enabled. Make sure to set them to NONE on the master, and the server in row based replication mode.This innovation includes dedicated contribution from Neha Kumari, Mats Kindahl and Narayanan Venkateswaran. Without them, this project would not be a success.Give it a try! You can download a copy from http://labs.mysql.com and get started NOW!
High Availability for Drupal Part 1 - Investigating the Issues
Drupal is one of the most popular Content Management Systems (CMS) and is used increasingly in high-visibility sites, such as www.whitehouse.gov. This has brought a lot of attention on how to get the most performance out of Drupal and how to improve the availability of such sites. In this blog series I'll take you through the basics and on through to designing your own HA Drupal site. But first, we need to understand what the challenges are in getting Drupal (or indeed any CMS) working on multiple servers in such a way as to ensure high availability and performance. read more
MySQL Book in Chinese
One of my old students and lab assistants stopped by to show his fiancée the BYU-Idaho campus. It was a long trip since he lives in Macao, China. He kindly brought me a copy of my Oracle Database 11g and MySQL 5.6 Developer Handbook in simplified Chinese. He’s holding it in the photo. That makes three books translated into Chinese, which made my day. It’ll be interesting to see if the new MySQL Workbench: Data Modeling & Development book gets translated into Chinese too. Oddly, I never hear about this from the publisher first. The cover emphasized only the Dolphin, not the Oracle logo material. It made me wonder, how many MySQL users there might be in China. If anybody from China catches the post, it would be great to hear about the MySQL Community in China. Likewise, if anybody in China catches the post and reads the book, please let me know if you liked it. Naturally, let me know if you found any problems with it too. By the way, I keep an errata for the book here.
An old note on the Storage Engine API
Whenever I stick my head into the MySQL storage engine API, I’m reminded of a MySQL User Conference from several years ago now. Specifically, I’m reminded of a slide from an early talk at the MySQL User Conference by Paul McCullagh describing developing PBXT. For “How to write a Storage Engine for MySQL”, it went something like this: Develop basic INSERT (write_row) support – INSERT INTO t1 VALUES (42) Develop full table scan (rnd_init, rnd_next, rnd_end) - SELECT * from t1 If you’re sane, stop here. A lot of people stop at step 3. It’s a really good place to stop too. It avoids most of the tricky parts that are unexpected, undocumented and unlogical (yes, I’m inventing words here).