MySQL 中文网

I finally got some time to do some house cleaning. One of my nagging low-hanging fruit stuff was stop running jconsole on one screen to see the state of all my cassandra boxes. I created a ganglia script to graph what is above. Above I am showing all the cassandra servers and their total row read stages as a gauge. Meaning that basically I am graphing the delta of the change between ganglia script runs. This gives me the reads over time based on deltas between runs.


How I have it set up is:

All data exposed by JMX to produce tpstats and cfstats is graphed via ganglia. The pattern for each graph is as follows

cass_{stat_class}_{key}

stat_class - tpc, tpp, tpa means complete, pending, active respectively
key - would be message deserialization for instance.

For column family stats I graph the keyspace stats as well as the specific column family stats exposed by cfstats. For instance below:



If your interested in the scripts I'll send it to you or put it up on code.google.com, its written in perl OOP perl and takes the same approach of packaging that maatkit tool kit for mySQL by Xarb and crew does (puts all the "classes" in the file as the application).

GmetricDelegate is the parent package
GmetricCassandra extends GmetricDelegate and overloads getData as well as defines what is an absolute stats vrs a gauge.

As you can see the pattern I also have
GmetricInnoDB
GmetricMySQL

and so on.

then on each server I run

/usr/bin/perl -w /home/scripts/ganglia_gmetric.pl --module=GmetricCassandra

this then talks to Ganglia through gmetric to report the stats.
PlanetMySQL Voting: Vote UP / Vote DOWN

Join us for a live and interactive webinar session where we will demonstrate how to start an evaluation of the MySQL Cluster database in 5 easy steps, and then how to expand your deployment for web & telecoms-scale services.

Just register here:

http://www.mysql.com/news-and-events/web-seminars/display-566.html

Getting Started will describe how to:

• Get the software
• Install it
• Configure it
• Run it
• Test it

Scaling for HA and the web will describe how to:

• Review the requirements for a HA configuration
• Install the software on more servers
• Update & extend the configuration from a single host to 4
• Roll out the changes
• On-line scaling to add further nodes

When: Wednesday, September 08, 2010: 09:00 Pacific time (America)

Wed, Sep 08: 11:00 Central time (America) Wed, Sep 08: 12:00 Eastern time (America) Wed, Sep 08: 16:00 UTC Wed, Sep 08: 17:00 Western European time

The presentation will be approximately 45 minutes long followed by Q&A.

PlanetMySQL Voting: Vote UP / Vote DOWN

Here are some quick commands for installing the proper packages and requirements for the MySQL dbbenchmark program.

export PKG_PATH="ftp://openbsd.mirrors.tds.net/pub/OpenBSD/4.7/packages/amd64/" pkg_add -i -v wget wget http://dbbenchmark.googlecode.com/files/dbbenchmark-version-0.1.beta_rev26.tar.gz pkg_add -i -v python Ambiguous: choose package for python a 0: 1: python-2.4.6p2 2: python-2.5.4p3 3: python-2.6.3p1 Your choice: 2 pkg_add -i -v py-mysql pkg_add -i -v mysql pkg_add -i -v mysql-server ln -s /usr/local/bin/python2.5 /usr/bin/python gzip -d dbbenchmark-version-0.1.beta_rev26.tar.gz tar -xvf dbbenchmark-version-0.1.beta_rev26.tar cd dbbenchmark-version-0.1.beta_rev26 ./dbbenchmark.py --print-sql - login to mysql and execute sql commands ./dbbenchmark.py
PlanetMySQL Voting: Vote UP / Vote DOWN

Unless you set sync_binlog = 1, a system crash on the master will likely fail any slave with an “Client requested master to start replication from impossible position” error. Generally, this kind of situation requires manual intervention. When we see this, we make sure things indeed failed “past the end” of a binlog (i.e. the bit that didn’t get to the physical platter before the crash), reposition the slave to the next binlog, and use the Maatkit tools to ensure the slave is properly synced.

sync_binlog=1 is a problem in itself, because it makes the server not just do one fsync per commit, but several and that’s serious overhead. sync_binlog is actually not a boolean but a “fsync binlog every N commits” where 0 meaning “never”. So you could set it to 10 (fsync every 10 commits) and thus reduce the loss a little bit while not doing too much harm to performance. But it’s not ideal and won’t always prevent the above error.

PlanetMySQL Voting: Vote UP / Vote DOWN

As previously mentioned, Darren Cassar has been working on a new automated installer for the DBbenchmark program. It’s now available for download: click here. All you need to do is save it to the directory that you want to install to and then make sure it’s executable: “chmod 700 installer.sh”, then run it “./installer.sh”.

PlanetMySQL Voting: Vote UP / Vote DOWN

How long it may take MySQL with Innodb tables to shut down ? It can be quite a while.
In default configuration innodb_fast_shutdown=ON the main job Innodb has to do to complete shutdown is flushing dirty buffers. The number of dirty buffers in the buffer pool varies depending on innodb_max_dirty_pages_pct as well as workload and innodb_log_buffer_size and can be anywhere from 10 to 90% in the real life workloads. Innodb_buffer_pool_pages_dirty status will show you the actual data. Now the flush speed also depends on number of factors. First it is your storage configuration – you may be looking at less than 200 writes/sec for single entry level hard drive to tens of thousands of writes/sec for high end SSD card. Flushing can be done using multiple threads (in XtraDB and Innodb Plugin at least) so it scales well with multiple hard drives. The second important variable is your workload, especially how dirty pages would line up on the hard drive. If there are a lot of sequential pages which are dirty Innodb will be able to use larger size IOs – up to 1MB flushing dirty pages which can be a lot faster than flushing data page by page.

So if we have system with single hard drive doing 200 IO/ssc, 48G buffer pool which is 90% dirty and completely random page writes we’ll look at 13500 seconds or about 5min per 1GB of Buffer pool size.
This is worse case scenario though it is quite common in practice to see shutdown time of about 1min per GB of buffer pool per hard drive.

Baron has written a nice post how to decrease innodb shutdown time which you may want to read on this topic.

Entry posted by peter | No comment

Add to: | | | |

PlanetMySQL Voting: Vote UP / Vote DOWN

So far the benchmarking script supports Linux, FreeBSD, and OSX. I’m installing virtual machines today to get ready for development on the next OS that the community wants to have supported. Vote today for your choice. Development will begin Friday 2010-09-03.

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
PlanetMySQL Voting: Vote UP / Vote DOWN

At yesterdays Eigenbase Developer Meetup at SQLstream’s offices in San Francisco we arrived at a new logo for LucidDB.  DynamoBI is thrilled to have supported and funded the design contest to arrive at our new mascot.  Over the coming months you’ll see the logo make it’s way out to the existing luciddb.org sites, wiki sites, etc.  I’m really happy to have a logo that matches the nature of our database - BAD ASS!

PlanetMySQL Voting: Vote UP / Vote DOWN

Often, the first step in evaluating and deploying a database is to load an existing dataset into the database. In the latest version, TokuDB makes use of multi-core parallelism to speed up loading (and new index creation). Using the loader, MySQL tables using TokuDB load 5x-8x faster than with previous versions of TokuDB.

Measuring Load Performance

We generated several different datasets to measure the performance of TokuDB when doing a LOAD DATA INFILE … command. To characterize performance, we vary

• rows to load
• keys per row
• row length (including keys)

All generated keys, including the primary, are random, 8-byte values. The remaining data, needed to pad out the row length to specified length, is text.

Two files files are produced as part of data generation.

1. data file, containing ‘|’ separated fields
2. sql file, containing the CREATE TABLE command corresponding to the generated data

For instance, if the number of keys is 3 and the row length is 256 bytes, the following SQL statement is produced:

CREATE TABLE load_table (\ val0 BIGINT UNSIGNED NOT NULL,\ val1 BIGINT UNSIGNED NOT NULL,\ val2 BIGINT UNSIGNED NOT NULL,\ pad VARCHAR(232) NOT NULL,\ PRIMARY KEY (val0),\ KEY valkey1 (val1),\ KEY valkey2 (val2)\ ) ENGINE=tokudb

We can make the data generation program available if anyone is interested.

Load Test

A simple shell script

• creates the test table
• performs a LOAD DATA INFILE <datafile> INTO TABLE load_table FIELDS TERMINATED BY ‘|’
• returns execution time

For the experiments to be meaningful, we created datasets that do not fit in memory.

Results

We ran our benchmark on an Amazon Web Services c1.large node with 8 cores and 7 GB of memory. The test loads 100M rows (NOT pre-sorted). The data file was on a 2 disk RAID-0, the MySQL DB files on a different 2 disk RAID-0.

TokuDB Version 3 (~single-threaded) v. TokuDB Version 4 (multi-threaded) Keys Row Len v3 rows/s v4 rows/s Speedup 1 64 27K 142K 5.1 4 64 13K 82K 6.2 1 256 7K 54K 7.2 4 256 5K 43K 8.2 Other metrics

Several metrics can be used to measure performance:

• rows per second : data insert rate
• key-value pairs per second : indicates how fast the primary and secondary indexes are being created
• MB/s : how much raw data is being added to the database

Metrics for TokuDB v4:

Keys Row Len Rows/sec KV-pairs/sec MB/sec 1 64 142K 142K 9.1 4 64 82K 330K 5.3 1 256 54K 54K 13.9 4 256 43K 173K 11.1

These results show

1. significant parallelization (we believe larger CPU core count machines will see even larger benefits)
2. a significant jump in absolute load performance
3. speed-ups are not limited to tables with many keys – even the 1 key tables are 5-7x faster

We will report further results, especially speedups on larger CPU count machines, as they become available.

PlanetMySQL Voting: Vote UP / Vote DOWN

I am speaking at MySQL Sunday. The title for my talk is Success with MySQL and I will focus on things that  operations and users can do to make a MySQL deployment succeed. There are many interesting talks scheduled for Sunday, including several at the same time as mine. I hope to see you there.
PlanetMySQL Voting: Vote UP / Vote DOWN

I get a number of question about contentions/"stuck in..". So here comes some explanation to:

• Contention
• Thread Stuck in
• What you can do about it
  

In 99% of the cases the contentions written out in the out file of the data nodes (ndb_X_out.log) is nothing to pay attention to.

sendbufferpool waiting for lock, contentions: 6000 spins: 489200
sendbufferpool waiting for lock, contentions: 6200 spins: 494721

Each spin is read from the L1 cache (4 cycles on a Nehalem (3.2GHz), so about a nanosecond).
1 spin = 1.25E-09 seconds (1.25ns)

In the above we have:
(494721-489200)/(6200-6000)= 27 spins/contention
Time spent on a contention=27 x 1.25E-09=3.375E-08 seconds (0.03375 us)

So we don't have a problem..

Another example (here is a lock guarding a job buffer (JBA = JobBuffer A, in short it handles signals for heartbeats and some other small things, all traffic goes over JobBuffer B).

jbalock thr: 1 waiting for lock, contentions: 145000 spins: 3280892543
jbalock thr: 1 waiting for lock, contentions: 150000 spins: 3403539479

(3403539479-3280892543)/(150000-145000)=24529 spins/contention
Time spent on a contention: 3.06613E-05 seconds (30.66us )

This is a bit higher than I would have expected and I think more analysis is needed. However, i tend not to get these contentions on a busy system.

Ndb kernel thread X is stuck in ..

Ndb kernel thread 4 is stuck in: Job Handling elapsed=100 Watchdog: User time: 82 System time: 667
Ndb kernel thread 4 is stuck in: Job Handling elapsed=200
Watchdog: User time: 82 System time: 668
Ndb kernel thread 4 is stuck in: Job Handling elapsed=300 Watchdog: User time: 82 System time: 669
Ndb kernel thread 4 is stuck in: Job Handling elapsed=400 Watchdog: User time: 82 System time: 670

Here the important is to look at how User time and System time behaves.
If User time is constant (as it is here - 82ms), but the System time is growing (667, 668 etc) which indicates that the OS kernel is busy.
Slow network? Sub-optimal kernel version? NIC drivers? swapping? some kernel process using too much cpu?

If User time is growing it is probably because the ndb kernel is overloaded.

What can you do about this?

• In config.ini:
  RealtimeScheduler=1
  LockExecThreadToCPU=[cpuids]
• check that cpuspeed is not running ( yum remove cpuspeed )
• .. and finally ask us to optimize more!
  

Also, pay attention if you get the contentions on an idle Cluster or a busy Cluster.
PlanetMySQL Voting: Vote UP / Vote DOWN

Join us at this live event in Milan to better understand what’s new with MySQL. You will learn more about the current and future state of MySQL, now part of the Oracle family of products. We will also cover Oracle’s investment in MySQL aiming to make it even a better MySQL.

In particular the following topics will be discussed:

• Oracle’s MySQL Strategy
• What’s New for:
• The MySQL Server
• MySQL Cluster
• MySQL Enterprise
• MySQL Workbench

Stay tuned because we are organizing a similar event in Rome that will be announced soon. Attendance is free, but you’ll need to register in advance. Seats are limited, register today!

When:

• September 28, 2010: 9:30am in Milan - Register here.

PlanetMySQL Voting: Vote UP / Vote DOWN

(English version) Herhalde bildiğiniz gibi, biz tamamen sanal şirketiz, yani ana merkez gib bir şeyimiz yok. Hepimiz evinden çalışıyor ve biz sadece bir ya da iki kez yılda gerçek hayatta buluşuyorus. Bu sene kararımız İstanbul‘a düştü.  Firmadaki tek Türk ben olduğumdan dolayı toplantının organizesi bana düştü.

İlk adım olarak böyle bir toplantının özelikleri nedir ve en iyi şekilde nasıl hazır edilir diye araştırdım. Kaj Arnö bunun ayrıntılarını blog’unda açıklamış bile: How to arrange a physical meeting in a virtual organisation. Kaj ile İstanbul’a 2008′de gelmiştik ve çeşitli konferanslar vermiştik.

İlk toplantı tarihi ile ilgili bir anket yaptık ve İstanbul’daki toplantı Ekim ayına karar verdik. Tam olarak, Perşembe, 7 Ekim’den Salı, 12 Ekim 2010′a kadar.

Yaklaşık 30 kişilik toplantıyı düzenlemek pek bir basit görev değil, bu nedenle toplantıyı hazırlanmak için bana bir asistan tahsis edildi – oldukçada ünlü bir asistan  – My Widenius. My (okunuşu Mü) MySQL’ın My’sü ve ona bir mariadb.org e-posta adresinle ulaşabilisiniz.

Eğer Ekim’in başında İstanbul’da iseniz, sizi bizim toplantıya davet etmekten mutluluk duyarız. Üç toplantı günleri olacaktır: Cuma 8 Ekim, Cumartesi 9 Ekim ve Pazar 10 Ekim 2010. Toplantıların çoğu herkese açık olacaktır. Misafirlerimiz olarak Facebook, Percona ve Intel bizlen olucak.

Ayrıca yerel (İstanbul) kullanıcı grupları ve bizim toplantıya ilgilenlerini arıyoruz. Sizi İstanbul’da görmek üzere, …

PlanetMySQL Voting: Vote UP / Vote DOWN

Sphinx search is a full text search engine, commonly used with MySQL.

There are some misconceptions about Sphinx and its usage. Following is a list of some of Sphinx’ properties, hoping to answer some common questions.

• Sphinx is not part of MySQL/Oracle.
• It is a standalone server; an external application to MySQL.
• Actually, it is not MySQL specific. It can work with other RDBMS: PostgreSQL, MS SQL Server.
• And, although described as “free open-source SQL full-text search engine”, it is not SQL-specific: Sphinx can read documents from XML.
• It is often described as “full text search for InnoDB”. This description is misleading. Sphinx indexes text; be it from any storage engine or external source. It solves, in a way, the issue of “FULLTEXT is only supported by MyISAM”. Essentially, it provided full-text indexing for InnoDB tables, but in a very different way than the way MyISAM’s FULLTEXT index works.

Sphinx works by reading documents, usually from databases. Considering the case of MySQL, Sphinx issues a SQL query which retrieves relevant data (mostly the text you want to index, but other properties allowed).

• Being an external module, it does not update its indexes on the fly. So if 10 new rows are INSERTed, it has no knowledge of this. It must be called externally to re-read the data (or just read the new data), and re-index.
  • This is perhaps the greatest difference, functionality-wise, between Sphinx and MyISAM’s FULLTEXT. The latter is always updated, for every row INSERTed, DELETEd or UPDATEd. The latter also suffers by this property, as this makes for serious overhead with large volumes.
  • There’s more than one way to make that less of an issue. I’ll write some more in future posts.
• Sphinx does not keep the text to itself; just the index. Sphinx cannot be asked “Give me the blog post content for those posts containing ‘open source’”.
  • Sphinx will only tell you the ID (i.e. Primary Key) for the row that matches your search.
  • It is up to you to then get the content from the table.
  • With SphinxSE (Sphinx Storage Engine for MySQL) this becomes easier, all-in-one query.
• It can keep other numeric data. Such data can be used to filter results.
• It provides with GROUP BY-like, as well as ORDER BY-like mechanism.
• It allows for ordering results by relevance.
• It allows for exact match search, boolean search, and more.
• It has an API & implementation for popular programming languages: PHP, Python, Perl, Ruby, Java.

The above describes Sphinx as a general fulltext search engine for databases. It does, however, have special treatment for MySQL:

• First and foremost, it knows how to query MySQL for data (duh!)
• If you don’t mind compiling from source, you can rebuild MySQL with SphinxSE: a storage engine implementation. This storage engine does not actually hold any data, but rather provides an SQL-like interface to the search daemon.
  • Thus, you can query for search results using SELECT statements, JOINing to document tables, retrieving results, all in one step.
  • If you do mind compiling MySQL, be aware that MariaDB comes with SphinxSE built in in newer versions.
• It implements the MySQL protocol. You can connect to the sphinx server using a MySQL client, and actually issue SQL statements to retrieve data. Not all SQL is supported. The valid subset is called SphinxQL.
• Snippets (excerpts) are supported via MySQL UDF.

PlanetMySQL Voting: Vote UP / Vote DOWN

Open Query is now three years old! We initially started with consulting and training services, and extended this with our proactive subscriptions that also offers system administration and monitoring.

So how is it going? Pretty well. We’ve been profitable from the start, without funding (beyond a few hundred $ startup costs paid by Arjen) or any credit – by choice. Our objective has never been to grow ridiculously in terms of revenue or number of customers, we simply charge reasonable prices for real service. Right now we have dozens of clients on an ongoing basis, a neat trickle of new clients, and Open Query sustains the livelyhood and lifestyle of a number of people.

For me (Arjen), the three year mark is particularly interesting, since most startups do not make it past their first two years. With our different approach to doing business, we’ve seen our fair share of skepticism. Not that we mind, if anything it’s encouragement
If you’d like to learn more about our business principles, see the Upstarta site.

PlanetMySQL Voting: Vote UP / Vote DOWN

Seriously, it did.  Sorta.

I use Workbench for my daily work, and it’s a great tool.  If you haven’t tried the 5.2 release yet, you should.  While performing some maintenance, I happened to issue a DELETE statement against a table which had no indexes (it was 10 rows), and Workbench complained:

Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

It turns out this is a new feature in 5.2.26 (and is still there in 5.2.27) – Workbench now uses the equivalent of –safe-updates mode for the mysql command-line client (also known as the –i-am-a-dummy option – seriously).  This wasn’t exactly convenient for me, especially since the DELETE was part of a larger script which I then had to revise and step through manually after it failed, but there’s an easy way to change this behavior.  If you’re like me, you might consider disabling this:

• Go to Edit -> Preferences
• Select the SQL Editor tab
• Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”

Despite the text, the –safe-updates mode affects more than UPDATE and DELETE statements without WHERE clauses – it requires such statements to explicitly use indexes.

I’m changing this behavior on my installation before I run into other problems.

I’m no dummy. 

PlanetMySQL Voting: Vote UP / Vote DOWN

In database modeling, a m:n relationship is usually resolved by an additional table. But what if this relation is used only for archiving and the number of links in the resulting table is not too high? In that context, I got the idea to store all referring ID's as CSV string directly into a TEXT column of one of the referring tables. I came to this idea, because otherwise I would have to build complicated foreign keys and this way I also save one additional table. Certainly, this only makes sense if the data is not frequently accessed as foreign key. Nevertheless, I would like to tackle the problem, even if the implementation is very MySQL-oriented.

Read the rest »

PlanetMySQL Voting: Vote UP / Vote DOWN

“Hey DBA!  Remember that change you made for my group  four or five months ago? Well, we were waiting for things to get better but they have not. Can you change it back ASAP?!?”

I know several of you  DBAs after reading the above are reaching for their antacids.  OR something stronger.

Keeping track of what changes were made to which systems in your head is bound to bite a hard working DBA sooner or later.

This is one of those oh-so-obvious tips that will be ignored by many but it will come back to haunt like a cheap RAID array.   Get a notebook, a pen, and keep it by you when you work.  Now anytime you make a material change or perform a maintenance function on one of your systems, note what EXACTLY the change you made, the reason for the change,  plus the day and date.  It will take some effort to do this the first two times but you this to become an ingrained habit.  Keeping track of running myisamcheck, changing the IP address of a replication slave, or setting up a MyIsam buffer cache is tedious but it will pay off when you are assailed with something similar to the sentence that starts this entry.

And for those of us with poor rotten hand writing — take the effort to make it legible.  You may need to read it at 3AM after twenty hours of fighting a problem. Or  the poor person covering for you while you are on vacation can not tell if you changed max_connections or max_connect_errors and decides to set them both to zero.

So why not store this data electronically?  Well, Murphy’s Law aside,  it is very handy to have something to refer to that is portable, does not need electrons to read, and is based on a technology even the most pointy haired boss can understand.

And it helps to have a print out of your my.cnfs taped into the back of the notebook.

Here are the last two entries in my log.

• August 30, 2010 — Installed UDF for calculating median value on IBMtest1.
• September 10, 2010 — System ‘A1′ : changed DBBC.NumBlocksPCT from 66 to 20 in Calpont.xml. PrimProc process was not able to allocate 66% but was able to get 20%. Will slowly walk up setting until it fails again to determine maximum setting.

PlanetMySQL Voting: Vote UP / Vote DOWN

Not much new this time: just summary of part 3 published and fixed mistake in chapter 10 (thanks, Shane!).

Summary.

In the third part we discussed methods of application debugging in cases when query plays secondary role in the problem.

I'd like to bring your attention we only discussed most frequent cases while MySQL server has a lot of parameters which of them can affect application. Analyze parameters which you use. One of the methods is run problematic query using MySQL server running with option --no-defaults and examine if results are different for MySQL server run with parameter which you use. If results are different analyze why parameter affects it and solve the problem.

...

Rest of the chapter is here.

PlanetMySQL Voting: Vote UP / Vote DOWN

Platform: MySQL 5.x

Most of the time it would be handy to have a native MySQL script which would allow one to kill the sleeping connections which are in sleep state for more than 180 sec..  On the other hand DBA's can use  "wait_timeout" etc parameters to control this..

[code]

 # -- Make sure you are logged as MySQL 'root' user or any user who have got super privileges 



DELIMITER $$
DROP PROCEDURE IF EXISTS `uKillSleepingSessions`$$


CREATE PROCEDURE `uKillSleepingSessions`()
COMMENT 'This routne is used to kill idle sessions'
READS SQL DATA


BEGIN


DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE uID bigint(4);

DECLARE my_cur CURSOR FOR
SELECT ID
  FROM information_schema.PROCESSLIST PL
WHERE PL.COMMAND='Sleep' AND PL.TIME > 180;


DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP


FETCH my_cur
INTO uID;


IF no_more_rows THEN
    CLOSE my_cur;
    LEAVE the_loop;
END IF;


SET @tmp_sql= CONCAT("KILL ",uID);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;


DEALLOCATE PREPARE s1;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;


END$$
DELIMITER ;



# Usage from mysql prompt/gui client
# Once called , it would cleanup idle connections which are sleeping for more than 180 sec
call uKillSleepingSessions();

[/code]

PlanetMySQL Voting: Vote UP / Vote DOWN