PlanetMysql.ru — информация о СУБД MySQL

It seems obvious that given the decreasing cost of storage and computation, there's going to be a significant increase in the volume of data that organizations accumulate over the next 10 years.  But the type of data being accumulated may be different from the areas where traditional DBMSs dominated.  It's not just about transactions; it's search patterns, on-line behavior, click-thru data, events fired off by smartphones, messages over Twitter & Facebook, log data of various kinds.

If an organization can figure out a better way identify prospects, or deliver more targeted ads, or optimize pricing decisions by analyzing terrabytes of data, they'd be crazy not to. Over the long term, companies that don't develop these capabilities will be at a competitive disadvantage.

As to what the implications are from a technological perspective, that's a whole different can of worms. I'm starting to see adoption of Big Data technologies like Hadoop, HDFS, Cassandra, MongoDB, XML databases, analysis with R, Pentaho, and loads of other technologies.  And MySQL continues to play a role here as do other traditional relational databases.  Over the next few months, I'm going to dig down deeper with people using these technologies to try and discern the emerging customer patterns.

If you're in this space or using some of these technologies, let me know your thoughts. What volume of data are you dealing with?  How many nodes or servers are you using?  Are you running on a public cloud, private cloud or hybrid? What technologies did you evaluate?  What about traditional DBMSs didn't work for this scenario? 

• Wikipedia: R, Hadoop, Cassandra, MongoDB
• Web sites: Cran, Hadoop, Cassandra, MongoDB
• Companies: Pentaho, Revolution Analytics, Cloudera, CouchOne, 10Gen, NorthScale, Riptano

PlanetMySQL Voting: Vote UP / Vote DOWN

A company wiki can be easily configured in under 10 minutes using Mediawiki the open source LAMP software that powers the top 10 website Wikipedia.

A company wiki is an ideal means for a centralized and user contributed documentation system. The following steps show you how to download, configure and get your Mediawiki site operational.

Software Pre-Requisites

• Installing MySQL on Oracle Enterprise Linux
• Installing a LAMP Stack on Oracle Enterprise Linux

Software Installation

su -
cd /tmp
wget http://download.wikimedia.org/mediawiki/1.16/mediawiki-1.16.0.tar.gz
cd /var/www/html
tar xfz /tmp/mediawiki*.tar.gz
mv mediawiki* wiki
chmod 777 wiki/config

NOTE: You should check the Mediawiki Downloads page for the latest version.

You can now visit http://localhost/wiki and you will be presented with a message of an un-configured Mediawiki environment. You can streamline the MySQL portion of this configuration with the following commands.

mysql -uroot -p -e "DROP SCHEMA IF EXISTS wikidb;CREATE SCHEMA wikidb"
mysql -uroot -p -e "CREATE USER wikiuser @localhost IDENTIFIED BY 'sakila'"
mysql -uroot -p -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX on wikidb.* TO wikiuser@localhost"

You can now complete the configuration on the Configuration Page. You will need to enter the following information.

• Wiki name = Example Wiki
• Contact e-mail = your email address
• Admin user password = enter password
• DB password = sakila

After successful installation, one fine step is needed.

mv wiki/config/LocalSettings.php wiki/
chmod 400 wiki/LocalSettings.php
chmod 500 wiki/config

You can now start using your new Wiki at http://localhost/wiki. You will find a both documentation at the links provided on the displayed home page and also at www.mediawiki.org.

MySQL Structures

Looking at the tables that are created by the installation process:

$ mysql -uwikiuser -psakila blog

mysql> show tables;
+-------------------+
| Tables_in_wikidb  |
+-------------------+
| archive           |
| category          |
| categorylinks     |
| change_tag        |
| external_user     |
| externallinks     |
| filearchive       |
| hitcounter        |
| image             |
| imagelinks        |
| interwiki         |
| ipblocks          |
| job               |
| l10n_cache        |
| langlinks         |
| log_search        |
| logging           |
| math              |
| objectcache       |
| oldimage          |
| page              |
| page_props        |
| page_restrictions |
| pagelinks         |
| protected_titles  |
| querycache        |
| querycache_info   |
| querycachetwo     |
| recentchanges     |
| redirect          |
| revision          |
| searchindex       |
| site_stats        |
| tag_summary       |
| templatelinks     |
| text              |
| trackbacks        |
| transcache        |
| updatelog         |
| user              |
| user_groups       |
| user_newtalk      |
| user_properties   |
| valid_tag         |
| watchlist         |
+-------------------+
45 rows in set (0.00 sec)

mysql> SELECT table_name,engine,table_rows FROM information_schema.tables WHERE table_schema = 'wikidb';
+-------------------+--------+------------+
| table_name        | engine | table_rows |
+-------------------+--------+------------+
| archive           | InnoDB |          0 |
| category          | InnoDB |          0 |
| categorylinks     | InnoDB |          0 |
| change_tag        | InnoDB |          0 |
| external_user     | InnoDB |          0 |
| externallinks     | InnoDB |          0 |
| filearchive       | InnoDB |          0 |
| hitcounter        | MEMORY |          0 |
| image             | InnoDB |          0 |
| imagelinks        | InnoDB |          0 |
| interwiki         | InnoDB |         95 |
| ipblocks          | InnoDB |          0 |
| job               | InnoDB |          0 |
| l10n_cache        | InnoDB |       3686 |
| langlinks         | InnoDB |          0 |
| log_search        | InnoDB |          0 |
| logging           | InnoDB |          0 |
| math              | InnoDB |          0 |
| objectcache       | InnoDB |          2 |
| oldimage          | InnoDB |          0 |
| page              | InnoDB |          1 |
| page_props        | InnoDB |          0 |
| page_restrictions | InnoDB |          0 |
| pagelinks         | InnoDB |          0 |
| protected_titles  | InnoDB |          0 |
| querycache        | InnoDB |          0 |
| querycache_info   | InnoDB |          0 |
| querycachetwo     | InnoDB |          0 |
| recentchanges     | InnoDB |          0 |
| redirect          | InnoDB |          0 |
| revision          | InnoDB |          1 |
| searchindex       | MyISAM |          0 |
| site_stats        | InnoDB |          1 |
| tag_summary       | InnoDB |          0 |
| templatelinks     | InnoDB |          0 |
| text              | InnoDB |          1 |
| trackbacks        | InnoDB |          0 |
| transcache        | InnoDB |          0 |
| updatelog         | InnoDB |          0 |
| user              | InnoDB |          1 |
| user_groups       | InnoDB |          2 |
| user_newtalk      | InnoDB |          0 |
| user_properties   | InnoDB |          0 |
| valid_tag         | InnoDB |          0 |
| watchlist         | InnoDB |          0 |
+-------------------+--------+------------+
45 rows in set (0.01 sec)

Sort URL for this post rb42.com/oel-install-mediawiki

PlanetMySQL Voting: Vote UP / Vote DOWN

I recently had a run-in with very popular PHP ecommerce package which makes me want to voice a recurring mistake I see in how many web applications are architected.

What is that mistake?

The ecommerce package I was working with depended on caching.  Out of the box it couldn’t serve 10 pages/second unless I enabled some features which were designed to be “optional” (but clearly they weren’t).

I think with great tools like memcached it is easy to get carried away and use it as the mallet for every performance problem, but in many cases it should not be your first choice.  Here is why:

• Caching might not work for all visitors - You look at a page, it loads fast.  But is this the same for every user?  Caching can sometimes be an optimization that makes the average user have a faster experience, but in reality you should be caring more that all users get a good experience (Peter explains why here, taking about six sigma).  In practice it can often be the same user that has all the cache misses, which can make this problem even worse.
• Caching can reduce visibility – You look at the performance profile of what takes the most time for a page to load and start trying to apply optimization.  The problem is that the profile you are looking at may skew what you should really be optimizing.  The real need (thinking six sigma again) is to know what the miss path costs, but it is somewhat hidden.
• Cache management is really hard – have you planned for cache stampeding, or many cache items being invalidated at the same time?

What alternative approach should be taken?

Caching should be seen more as a burden that many applications just can’t live without.  You don’t want that burden until you have exhausted all other easily reachable optimizations.

What other optimizations are possible?

Before implementing caching, here is a non-exhaustive checklist to run through:

• Do you understand every execution plan of every query? If you don’t, set long_query_time=0 and use mk-query-digest to capture queries.  Run them through MySQL’s EXPLAIN command.
• Do your queries SELECT *, only to use subset of columns?  Or do you extract many rows, only to use a subset? If so, you are extracting too much data, and (potentially) limiting further optimizations like covering indexes.
• Do you have information about how many queries were required to generate each page? Or more specifically do you know that each one of those queries is required, and that none of those queries could potentially be eliminated or merged?

I believe this post can be summed up as “Optimization rarely decreases complexity. Avoid adding complexity by only optimizing what is necessary to meet your goals.”  – a quote from Justin’s slides on instrumentation-for-php.  In terms of future-proofing design, many applications are better off keeping it simple and refusing the temptation to try and solve some problems “like the big guys do”.

---

Entry posted by Morgan Tocker | No comment

Add to: | | | |

PlanetMySQL Voting: Vote UP / Vote DOWN

A company blog can be easily configured in under 10 minutes using Wordpress, a popular open source LAMP product that runs a reported 12+ million blogs including those found at CNN, NY Times, Wall Street Journal (WSJ), ZDNet, MTV, People Magazine, Playstation and eBay.

A company blog is a great way for the dissemination of information to your user base as well as enabling a means of user feedback via comments.

The following steps show you how to download, configure and get your Wordpress blog operational.

Software Pre-Requisites

• Installing MySQL on Oracle Enterprise Linux
• Installing a LAMP Stack on Oracle Enterprise Linux

Software Installation

su -
cd /tmp
wget  http://wordpress.org/latest.tar.gz
cd /var/www/html
tar xfz /tmp/latest.tar.gz
mv wordpress blog

You can now visit http://localhost/blog and you will be presented with a message of an un-configured Wordpress environment. You can streamline the MySQL portion of this configuration with the following commands.

cd blog
sed -e "s/database_name_here/blog/;s/username_here/blog_user/;s/password_here/sakila/" wp-config-sample.php > wp-config.php
mysql -uroot -p -e "CREATE SCHEMA blog"
mysql -uroot -p -e "CREATE USER blog_user @localhost IDENTIFIED BY 'sakila'"
mysql -uroot -p -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE on blog.* TO blog_user@localhost"

Returning now to http://localhost/blog you simply only need to specify a Title, password and email address, click Save and your Blog at http://localhost/blog is complete and operational.

MySQL Structures

Looking at the tables that are created by the installation process:

$ mysql -ublog_user -psakila blog

mysql> show tables;
+-----------------------+
| Tables_in_blog        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

mysql> SELECT table_name,engine,table_rows FROM information_schema.tables WHERE table_schema = 'blog';
+-----------------------+--------+------------+
| table_name            | engine | table_rows |
+-----------------------+--------+------------+
| wp_commentmeta        | MyISAM |          0 |
| wp_comments           | MyISAM |          1 |
| wp_links              | MyISAM |          7 |
| wp_options            | MyISAM |        109 |
| wp_postmeta           | MyISAM |          1 |
| wp_posts              | MyISAM |          2 |
| wp_term_relationships | MyISAM |          8 |
| wp_term_taxonomy      | MyISAM |          2 |
| wp_terms              | MyISAM |          2 |
| wp_usermeta           | MyISAM |         13 |
| wp_users              | MyISAM |          1 |
+-----------------------+--------+------------+
11 rows in set (0.00 sec)

Additional References

• Installing Wordpress
• Wordpress Client Showcase

Short URL for this post rb42.com/oel-install-wordpress

PlanetMySQL Voting: Vote UP / Vote DOWN

Did that ever happen to you in production?

Just happened to one of our customers. Want to know what we did?

For demo purposes I'll use sandbox here (so the ./use is actually executing mysql cli). Oh and mind it is not a general-purpose best-practice, but rather a break-and-enter hack when the server is flooded. So, when this happens in production, the problem is - how do you quickly regain access to mysql server to see what are all the sessions doing and how do you do that without restarting the application? Here's the trick:

And here's the result:

Credit for the gdb magic goes to Domas.

Few notes:

• You would usually have one connection reserved for SUPER user, but that does not help if your application is connecting as a SUPER user (which is a bad idea anyway).
• This worked for me on 5.0.87-percona-highperf, but use it at your own risk and better test it before you actually have to do it in production.
• This example assumes you had less than 5000 max_connections configured

---

Entry posted by Aurimas Mikalauskas | No comment

Add to: | | | |

PlanetMySQL Voting: Vote UP / Vote DOWN

Piper Jaffray has published a 300+ page study on the cloud computing industry based on a recent survey undertaken of 100 CIOs. Bottom line, cloud computing is expected to grow significantly over the next five years. 

    Survey respondents expect the mix of cloud computing to escalate strongly to 13.5% in five years. This equates to a five-year CAGR of 19.2%, or 23.9% when we also incorporate IDC’s forecast that total software budgets will grow 4.7% annually. In other words, software spending will grow gradually in the next five years, but the mix of spend allocated to cloud-based applications will likely surge rapidly. Another way to think about the data is that the Cloud Computing market is expected to grow five times as fast as the broader software market: 23.9% vs. 4.7%.

If anything, I think the prediction is conservative and the impact could be much larger in magnitude when mainstream adoption occurs.  But the risk is that adoption takes longer, just as it did for open source software.  And as the report indicates, open source is powering much of the cloud computing that's going on:

    The next-generation Cloud Computing data centers are NOT running Microsoft Windows; they are increasingly leveraging the compelling economics of open source components. For example, the data centers powering Amazon, Google, and salesforce.com all run on Linux and other open source technologies. In fact, Red Hat’s operating system and the MySQL database are key components to many of the leading-edge Clouds being developed today. 

Why is this occurring? Because open source leverages a global community development process which results in a product that evolves rapidly, provides transparency into the source code dynamics, and surpasses other products in terms of security and reliability – all at a lower total cost of ownership (TCO) than traditional offerings.

• RedMonk: Who's Winning in the Cloud Marketing Battle? 
• Information Week: Piper Jaffray Sees Gold Rush in Cloud

PlanetMySQL Voting: Vote UP / Vote DOWN

I’ve been looking over the documentation lately and trying to find ways to improve the installation experience for new users. That said, I’ve written a short but useful description of the easiest way to install all of the Perl and PHP requirements for Kontrollbase. You can find it here: http://kontrollsoft.com/kontrollbase/userguide/installation-install_overview.php#simple – or in the Installation [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

If you are running your MySQL databases on the Amazon EC2 compute cloud, Zmanda Recovery Manager (ZRM) for MySQL can perform fast full backups of these databases by using Elastic Block Store (EBS) Snapshots. ZRM takes only a momentary read lock on the MySQL database during the creation of the snapshot, in order to ensure consistency of the backed up database archive. MySQL Backups using Amazon EBS snapshots are differential backups, meaning that only the blocks that have changed since your last full backup (via EBS snapshot) will be saved. For example, if you have a database with 100 GBs of data, but only 5 GBs of data has changed since your last snapshot, only the 5 additional GBs of snapshot data will be stored back to Amazon S3 during the current full backup run.

ZRM automatically deletes EBS snapshots (containing full backups of MySQL) according to the configured retention policy. Just like other snapshot based full backups, ZRM intelligently correlates EBS Snapshots with incremental backups using MySQL logs, enabling you to recover your MySQL instances running on EC2 to any point in time.

Backups made using EBS snapshots can be recovered on the original EC2 instance or on a new EC2 instance. This also provides a quick and convenient mechanism to instantiate new MySQL database servers based on the database state from a desired point-in-time.

ZRM can run on the same EC2 instance as the MySQL database. On the other hand, if you have multiple EC2 instances with MySQL databases, you can run ZRM on one centralized EC2 instance dedicated for backup purposes. In this case, backup configuration and management for all MySQL databases is performed via Zmanda Management Console from this centralized backup server.

We have created an Amazon Machine Image (AMI) with ZRM pre-configured. This makes implementation of a MySQL backup solution on the cloud even simpler. We have used the “EC2 Small Instance” - which is powerful enough to backup most MySQL workloads in the cloud. This also makes it a very cost-effective option. This AMI is available to all ZRM customers, as part of the ZRM Enterprise subscription. You will need to create your own Amazon EC2 account, and pay standard per hour price to Amazon to run an instance based on this AMI. Note that you can configure your backup server instance to run only during the backup window. So, if you are backing up your databases once a week, and your backups takes less than an hour, then you can have this instance up only during that hour. EC2 pricing is per instance-hour consumed from the time an instance is launched until it is terminated. Each partial instance-hour consumed will be billed as a full hour. In addition to the EC2 compute capacity, you will pay standard storage charges for Amazon S3 (to store EBS Snapshots).

Join us on January 28th for a webinar on MySQL Backups (hosted by Sun/MySQL). Along with an introduction to Zmanda Recovery Manager, we will also discuss backing up MySQL applications on the cloud, and demonstrate the new ZRM AMI.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2 describes installing MySQL. You can also download a copy of the manual in various different formats at MySQL Documentation. This is also valuable for the time when the documentation may be be unavailable online.

Step 3. Download a GUI tool to help you in the design of your first MySQL Tables. There are a number of products available to do this, the MySQL Query Browser and WebYog are just two examples. If your bold, you can use the mysql client command line tool and use the CREATE TABLE command to create your table structures.

MySQL by itself is ineffective for producing a client facing end result unless you have an application purpose and therefore a general application to access the data in MySQL. Using a LAMP/WAMP stack is a good place to start. XAMPP is a good cross platform program that gives you MySQL and a PHP technology stack. You also get PhpMyAdmin included with XAMPP which is a good web based design tool. I don’t mention earlier because it needs a running php/apache/mysql environment. If you elect to start with this stack, then you don’t need to install any GUI tools.

Finally, there a wealth of knowledge, not at least the MySQL Forums and the #mysql channel on irc.freenode.net which can be good places to get free beginner information.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2 describes installing MySQL. You can also download a copy of the manual in various different formats at MySQL Documentation. This is also valuable for the time when the documentation may be be unavailable online.

Step 3. Download a GUI tool to help you in the design of your first MySQL Tables. There are a number of products available to do this, the MySQL Query Browser and WebYog are just two examples. If your bold, you can use the mysql client command line tool and use the CREATE TABLE command to create your table structures.

MySQL by itself is ineffective for producing a client facing end result unless you have an application purpose and therefore a general application to access the data in MySQL. Using a LAMP/WAMP stack is a good place to start. XAMPP is a good cross platform program that gives you MySQL and a PHP technology stack. You also get PhpMyAdmin included with XAMPP which is a good web based design tool. I don’t mention earlier because it needs a running php/apache/mysql environment. If you elect to start with this stack, then you don’t need to install any GUI tools.

Finally, there a wealth of knowledge, not at least the MySQL Forums and the #mysql channel on irc.freenode.net which can be good places to get free beginner information.

PlanetMySQL Voting: Vote UP / Vote DOWN