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

Last week was a banner week for MySQL at OSCON. We had many MySQL developers meeting with the MySQL community, conducting technical sessions, leading BOF sessions, working the exhibit hall, and confirming Oracle's leadership in the technical evolution of MySQL.  The highlight of the week was the unveiling of even more 5.6 early access InnoDB and Replication features that are now available for early adopters to download, evaluate and shape via labs.mysql.com.  

InnoDB is one of MySQL's "crown jewels" and beginning in 5.5 is now the default storage engine.  The following 5.6 feature improvements are in direct response to community and customer feedback and requests.  The new 5.6 early access features include:

• Full-text search
  
• REDO log files max size extended to 2 TB
• UNDO logs on their own tablespace
• Buffer Pool options for pre-loading/warming on re-start
• Improved auto-extension of .ibd files
• Support for smaller 4k, 8k page sizes

Replication is by far the most popular and widely used MySQL feature. The following feature improvements in 5.6 are also in direct response to community and customer feedback and requests.  The new 5.6 early access features include:

• New Binlog API
• Binlog group commit (completes InnoDB group commit implemented in MySQL 5.5)
• Durable Slave Reads
• Enhanced multi-threaded slaves

You can read about the details, including development blogs on how to get started with each in this new DevZone article.  My sincere thanks and appreciation to the InnoDB and Replication development teams for their leadership in technical innovation and mind share and for their dedicated work in providing these and other new features "early and often" to the MySQL community.  Stay tuned for more to come!

We can't say this "early and often" enough...thanks for your continued support of MySQL!
PlanetMySQL Voting: Vote UP / Vote DOWN

A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”

Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):

• sysbench –test=oltp –db-driver=mysql –oltp-table-size=1000000 –mysql-engine-trx=yes –oltp-test-mode=complex –oltp-read-only=off –oltp-dist-type=special –max-requests=0 –num-threads=8 –max-time=120 –init-rng=on run

MySQL Settings:

In the first test MySQL is set to the following ACID related settings. This will give us results for TPS performance without full ACID compliance – very common settings on a server that is handling blogs, ad serving, general business websites, and other roles where full ACID is not required and performance is valued over the benefits of full ACID. These are important settings when we look at the difference in performance when we change to full ACID in the second test.

• innodb_flush_log_at_trx_commit = 0
• sync_binlog=0
• transaction-isolation=REPEATABLE-READ

System configuration and InnoDB buffer pool size:

• XEON E5345 Series 2.33ghz 8-core, 16GB RAM, Local SATA 7.2K disks
• innodb_buffer_pool_size = 10G

Full result set from sysbench:

Summary OLTP test statistics:

• queries performed:
• transactions:                        172426 (1436.83 per sec.)
• read/write requests:                 3276664 (27304.51 per sec.)
• other operations:                    344882 (2873.91 per sec.)

Take away results:

We can simplify the results by looking at the following TPS results for this non-ACID test:

• transactions:                        172426 (1436.83 per sec.)

Let’s go ahead and run the test again with different ACID settings. This will give us the TPS results for full ACID compliance:

• innodb_flush_log_at_trx_commit = 1
• sync_binlog=1
• transaction-isolation=REPEATABLE-READ

We get the following results for TPS:

• transactions:                     3197   (26.58 per sec.)
• read/write requests:                 60743  (505.04 per sec.)
• other operations:                    6394   (53.16 per sec.)

Final Results:

So as you can see the difference between full ACID settings and not (on the same server with only those values on the cnf being changed) results in a huge difference in performance on this standard database server. We can now hand this data to the customer and they will know what impact the settings will have on their application’s performance and what to expect when running full ACID vs non-ACID.

More info on using sysbench here: http://sysbench.sourceforge.net

PlanetMySQL Voting: Vote UP / Vote DOWN

If you’ve been reading up on the various NoSQL offerings and have wanted to try out one but don’t know how to get started, this is one of the easiest ways. I chose MongoDB for this example because I’m going to start using it for a project that needs features that MySQL isn’t as fast at: namely denormalized data with billions of rows. MongoDB has plenty of drivers for other scripting and high-level languages but I’ll focus on the PHP driver today. If there is interest I can do a write up on Python usage later. This example is limited to CentOS, Fedora, and Redhat 5 servers that use the yum package management system. For more information you can reference their download page: http://www.mongodb.org/display/DOCS/Downloads

First install the prerequisites:

• sudo yum install gcc php php-pear

Then install the mogo php extension via pecl and add the extension to the ini file. (Increase the php.ini memory_limit to 32M if necessary). Then we restart apache to have the extension loaded. You can check your extensions via the phpinfo(); command in php.

• sudo pecl install mongo
• sudo echo “extension=mongo.so” >> /etc/php.ini
• sudo /etc/init.d/httpd restart

For the last part we add the yum repo and get the actual MongoDB server and client installed

• cd /etc/yum.repos.d
• sudo emacs mongodb.10gen.repo
• paste in the following. This uses the repo for 64bit servers. See their download page for other architecture repo settings.

[10gen]
name=10gen Repository
baseurl=http://downloads.mongodb.org/distros/centos/5.4/os/x86_64/
gpgcheck=0

• sudo yum install mongo-stable-server
• sudo /etc/init.d/monod start
• chkconfig mongod on

That’s it, you’re ready to start using Mongo! You can check the logfile to make sure things are running smoothly: “sudo cat /var/log/mongo/mongod.log”

PlanetMySQL Voting: Vote UP / Vote DOWN

Saw this interesting paper about highly concurrent programming methods and figured the word should be spread! It’s not new material but it’s a good read. See the full article here: http://www.usenix.org/events/hotos03/tech/full_papers/vonbehren/vonbehren_html/

“Highly concurrent applications such as Internet servers and transaction processing databases present a number of challenges to application designers. First, handling large numbers of concurrent tasks requires the use of scalable data structures. Second, these systems typically operate near maximum capacity, which creates resource contention and high sensitivity to scheduling decisions; overload must be handled with care to avoid thrashing. Finally, race conditions and subtle corner cases are common, which makes debugging and code maintenance difficult.

Threaded servers have historically failed to meet these challenges, leading many researchers to conclude that event-based programming is the best (or even only) way to achieve high performance in highly concurrent applications. “

PlanetMySQL Voting: Vote UP / Vote DOWN

This is a small revision and will only be available through SVN. However, it is an important one to speak about as it solves a former issue when running the application on a screen smaller than 1024px wide. While most users may not have noticed this since they have larger monitors it has been noticed [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

When I saw Shlomi's post on why not to use apt-get or yum for MySQL, I thought immediately that his conclusions are quite reasonable. What you get from the Linux distributions is not the same thing that you find in the official MySQL downloads page. Now, whether you value more the completeness of the server or the ease of administration through the distribution installation tools, it's up to you and your business goals. We at the MySQL team have organized a meeting with the Linux distributions with the intent of finding out which differences and problems we may have with each other, and to solve them by improving communication. What follows is a summary of what happened in Brussels during the meeting.

Summary

Linux distributions ship MySQL products (Server, GUI Tools, connectors, Cluster) with different criteria and different grade of maturity, according to their own goals.
Due to lack of communication and policy conflicts, the distros almost always ship outdated versions of MySQL server and MySQL Cluster. The lag between the shipped version and the latest product shipped by MySQL ranges from a few months to several years.
By mutual understanding, the distros will now try to ship recent versions of Cluster
(7.x) in a separate package.

Participants

(see some more pictures from the meeting).

Giuseppe Maxia, MySQL Community Team Lead, Italy
Tomas Ulin, MySQL VP Engineering, Sweden
Harmut Holzgraefe, MySQL Support, Germany
Lars Heill, MySQL Build, Norway (Trondheim)
Joro Kodinov, MySQL Engineering / 5.1, Bulgaria
Oden Eriksson, Mandriva, Sweden
Mathias Gug, Canonical/Ubuntu, Canada
Robin H. Johnson, Gentoo Linux, Canada
Michal Hrušecký, Novell/openSUSE, Czech Republic
Geir Høydalsvik, MySQL QA, Norway (Trondheim)
Norbert Tretkowski, Debian Linux, Germany
Kaj Arnö, MySQL VP Community Relations, Germany

Main issues from the distributions:

d1. Security bugs are invisible until MySQL releases a fix. They would like to get visibility of the bug report, to become aware of the problem and eventually help fixing it. We are looking into this matter.

d2. Due to lack of communication, the distros were running the test suite with different parameters. Gentoo packages the server with UTF-8 as default character set, and this causes several tests to fail. Our QA team is looking into it.

d3. Bug databases are different for each distros. They usually solve problems on their own, or send the issue upstream (to the MySQL team at Sun, now Oracle) when it is a legitimate bug.

d4. Debian and Ubuntu don't apply all our patches to the server that they ship. They only apply security bugs and fix for bugs that don't introduce new or changed functionality. This is, IMO, mostly a matter of terminology, since the new functionality is only added as a side effect of fixing a bug. For example, when we fixed Bug#49222: Mark RAND() as unsafe, there is a change in functionality. Now RAND() is logged in ROW format, as it should have been in the first place. It is indeed a new functionality, but as a user I would rather have this bug fix in my server, than adhering to the strict rules of no changes.

d5. GUI tools are still shipped as current although they aren't actively supported, with patches provided by OpenSuse.

d6. While we provide specifications for .rpm packages, we don't do that for .deb ones. Debian/Ubuntu ask if we can include them in our code.

Main issues from the MySQL team:

m1. Cluster packages are outdated. Mainly for miscommunication, some distros are building the cluster binaries with the server package, thus shipping quite old and non-functional cluster binaries. After an explanation on the Cluster roadmap, the distros agreed to ship 7.x binaries from now on. We agreed that we will modify the build scripts in the server to avoid compiling the cluster binaries unintentionally.

m2. MySQL Workbench is not included in the stable releases. There are two reasons: it is not GA yet, and its source includes non-GPL code (for Windows and Mac) that needs to be removed before being used by Debian and derivatives. Moreover, Debian communicates that some DBAs don't like the idea of deploying a design tool for daily database administration.

Conclusions

All in all, I feel that this meeting was a success. We achieved a lot during the proceedings, solving problems ranging from simple communication mismatches to neglected bugs. And meeting in person with the ones who deal actively with MySQL in the Linux distros is quite a rewarding experience.
From a technical standpoint, I hadn't realized that every distribution is shipping a different server. That is quite a challenge for the common users who may need to choose between versions in several sites. However, this meeting has also shown me that all the participants have very high quality standards, and the difference in shipped versions is mostly due to the peculiarity of many shipping calendars.
Thanks to all the participants. We fixed many issues, and we had lots of fun at the same time. We should do that more often!
PlanetMySQL Voting: Vote UP / Vote DOWN

It didn’t take long, but the advanced analytics reporting code has been added to the command line reporter version of Kontrollbase. Click here to see an example of the tuning and analytics report output (html format). Now you have all of the reporting features (minus the growth over time) features that are offered in the [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

In the past few weeks I've been implementing advanced search at Plaxo, working quite closely with Solr enterprise search server. Today, I saw this relatively detailed comparison between Solr and its main competitor Sphinx (full credit goes to StackOverflow user mausch who had been using Solr for the past 2 years). For those still confused, Solr and Sphinx are similar to MySQL FULLTEXT search, or for those even more confused, think Google (yeah, this is a bit of a stretch, I know).

Similarities

• Both Solr and Sphinx satisfy all of your requirements. They're fast and designed to index and search large bodies of data efficiently.
• Both have a long list of high-traffic sites using them (Solr, Sphinx)
• Both offer commercial support. (Solr, Sphinx)
• Both offer client API bindings for several platforms/languages (Sphinx, Solr)
• Both can be distributed to increase speed and capacity (Sphinx, Solr)

Here are some differences

• Solr, being an Apache project, is obviously is Apache2-licensed. Sphinx is GPLv2. This means that if you ever need to embed or extend (not just "use") Sphinx in a commercial application, you'll have to buy a commercial license.
• Solr is easily embeddable in Java applications.
• Solr is built on top of Lucene, which is a proven technology over 7 years old with a huge user base (this is only a small part). Whenever Lucene gets a new feature or speedup, Solr gets it too. Many of the devs committing to Solr are also Lucene committers.
• Sphinx integrates more tightly with RDBMSs, especially MySQL.
• Solr can be integrated with Hadoop to build distributed applications
• Solr can be integrated with Nutch to quickly build a fully-fledged web search engine with crawler.
• Solr can index proprietary formats like Microsoft Word, PDF, etc. Sphinx can't.
• Solr comes with a spell-checker out of the box.
• Solr comes with facet support out of the box. Faceting in Sphinx takes more work.
• Sphinx doesn't allow partial index updates for field data.
• In Sphinx, all document ids must be unique unsigned non-zero integer numbers. Solr doesn't even require a unique key for many operations, and unique keys can be either integers or strings.
• Solr supports field collapsing to avoid duplicating similar results. Sphinx doesn't seem to provide any feature like this.

Related questions

• http://stackoverflow.com/questions/1284083/choosing-a-stand-alone-full-text-search-server-sphinx-or-solr
• http://stackoverflow.com/questions/1132284/full-text-searching-with-rails
• http://stackoverflow.com/questions/737275/pros-cons-of-full-text-search-engine-lucene-sphinx-postgresql-full-text-searc

Conclusion

In my experience, Solr is very-very fast on the query side. It is also very powerful. The indexing side is very CPU and memory intensive and is an unfortunate side effect of having such a feature-rich, fast application. Nevertheless, I highly recommend Solr.

For disclaimer purposes, I have not had much experience with Sphinx and, again, all credit for this comparison goes to mausch.

Similar Posts:

• Hidden Features Of Perl, PHP, Javascript, C, C++, C#, Java, Ruby, Python, And Others [Collection Of Incredibly Useful Lists]
• Delicious.com [Quietly] Rolls Out Domain And Url Searching/Filtering. Finally!
• Top 10 Reasons Why Digsby ROCKS
• My MySQL Conference Schedule
• MySQL Indexing Considerations Of Implementing A Priority Field In Your Application

PlanetMySQL Voting: Vote UP / Vote DOWN

Hi all,

Last night I spent some time setting up a connection pool in GlassFish's admin console. Here's the backstory: I'm working on a training course for cloud computing, and as part of my module on assembling a virtual data center I ran into a few problems. I have two virtual servers running. One is running OpenSolaris and has GlassFish application server installed on it. The other is running Fedora Linux and has my MySQL database on it. The problems occurred when I tried to ping the database server from GlassFish. I got some strange messages. When a colleague of mine googled the problem, he found out that the problem was an OpenSolaris specific problem. After download several .jar files the error message said weren't there, I stumbled upon the solution:

1. The solution was to copy my regular MySQL driver .jar file from my local machine to the glassfish-install/domains/domain1/lib/ext/ directory on my virtual server.
2. I typed this: scp -i mykeypair.pem ~/location/of/my.jar root@my-aws-public-dns:/glasssfish/domains/domain1/lib/ext/.
3. I then restarted GlassFish and the connection pool worked: I was able to ping my database server.

Cheers!

--James

PlanetMySQL Voting: Vote UP / Vote DOWN