MySQL 中文网

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

“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

Mårten Mickos, the CEO of Eucalyptus and former CEO of MySQL AB, will be back on stage as the closing keynoter on September 19th at MySQL Sunday, one of the community events at the start of Oracle Open World 2010.

The opening keynote will be delivered by Edward Screven, Chief Corporate Architect at Oracle.

MySQL Sunday has a very rich schedule, and by the registration numbers it looks like it's going to be packed.

PlanetMySQL Voting: Vote UP / Vote DOWN

We’re proud to announce the next release of MySQL Workbench, version 5.2.27. This is the second maintenance release for 5.2 GA (Generally Available). This maintenance release does not introduce any new features, but focuses on general product improvement and usability.  We hope you will make MySQL Workbench your preferred tool for Design, Development, and Administration of your MySQL database applications.

As always, we want to thank everyone for the great feedback we have received. This helps us to continuously improve and extend the functionality and stability of MySQL Workbench – please keep up on approaching us with any ideas to develop our product even further.

MySQL Workbench 5.2 GA

• Data Modeling
• Query (replaces the old MySQL Query Browser)
• Administration (replaces the old MySQL Administrator)

Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.

http://dev.mysql.com/downloads/workbench/

To get started quickly, please take a look at this short tutorial.

MySQL Workbench 5.2 RC Tutorial

http://wb.mysql.com/?p=406

Workbench Documentation can be found here.

http://dev.mysql.com/doc/workbench/en/index.html

In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness.
This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in

http://wb.mysql.com/workbench/doc/

For a detailed list of resolved issues, see the change log.

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.

Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

- The MySQL Workbench Team

PlanetMySQL Voting: Vote UP / Vote DOWN

Ignacio Nin and I (mostly Ignacio) have worked together to create tcprstat[1], a new tool that times TCP requests and prints out statistics on them. The output looks somewhat like vmstat or iostat, but we’ve chosen the statistics carefully so you can compute meaningful things about your TCP traffic.

What is this good for? In a nutshell, it is a lightweight way to measure response times on a server such as a database, memcached, Apache, and so on. You can use this information for historical metrics, capacity planning, troubleshooting, and monitoring to name just a few.

The tcprstat tool itself is a means of gathering raw statistics, which are suitable for storing and manipulating with other programs and scripts. By default, tcprstat works just like vmstat: it runs once, prints out a line, and exits. You’ll probably want to tell it to run forever, and continue to print out more lines. Each line contains a timestamp and information about the response time of the requests within that time period. Here “response time” means, for a given TCP connection, the time elapsed from the last inbound packet until the first outbound packet. For many simple protocols such as HTTP and MySQL, this is the moral equivalent of a query’s response time.

The statistics we chose to output by default are the count, median, average, min, max, and standard deviation of the response times, in microseconds. These are repeated for the 95th and 99th percentiles as well. Other metrics are also available. Here’s a sample:

[root@server] # tcprstat -p 3306 -n 0 -t 1 timestamp count max min avg med stddev 95_max 95_avg 95_std 99_max 99_avg 99_std 1276827985 1341 24556 23 149 59 767 310 91 69 1030 107 112 1276827986 1329 12098 28 134 63 461 299 91 65 667 104 93 1276827987 1180 13277 22 202 93 873 439 103 79 1523 131 169 1276827988 1441 15878 27 180 139 672 427 116 79 1045 136 128 1276827989 1432 157198 26 272 138 4165 405 115 80 1092 134 123 1276827990 1835 25198 26 183 124 734 448 115 85 1141 137 141 1276827991 1242 6949 29 129 114 301 233 98 61 686 109 84 1276827992 1480 284181 25 442 127 7432 701 128 114 4157 173 293 1276827993 1448 9339 22 161 88 425 392 104 80 1280 126 140

tcprstat uses libpcap to capture traffic. It’s a threaded application that does the minimum possible work and uses efficient data structures. Your feedback on the kernel/userland exchange overhead caused by the packet sniffing would be very appreciated — libpcap allows the user to tune this exchange, so if you have suggestions on how to improve it, that’s great.

We build statically linked binaries with the preferred version of libpcap, which means there are no dependencies. You can just run the tool. In the future, packages in the Percona repositories will provide another means for rapid installation via yum and apt.

tcprstat is beta software. Several C/C++ experts reviewed its code and gave it a thumbs-up, so many eyes have been on the code. We’ve performed tests on servers with high loads and observed minimal resource consumption. I personally have been running it for many weeks on some production servers without stopping it and have seen no problems, so I am pretty sure it has no memory leaks or other problems. Nevertheless, it’s a first prototype release, and we want much more testing. We might also change the functionality; as we build tools around it, we discover new things that might be useful. When we’re happy with it and you’re happy with it, we’ll take the Beta label away and make it GA.

The tcprstat user’s manual and links to downloads are on the Percona wiki. Commercial support and services are provided by Percona. Bug reports, feature requests, etc should go to the Launchpad project linked from the user’s manual. General discussion is welcome on the Google Group also linked from the user’s manual.

[1] Historical note: we initially called this tool rtime, but did not publicize it. However, some of you might have heard of “rtime” before. This is the same tool.

Entry posted by Baron Schwartz | No comment

Add to: | | | |

PlanetMySQL Voting: Vote UP / Vote DOWN

On September 19, 2010, Oracle is hosting MySQL Sunday, a half-day technical conference jam-packed with the latest on MySQL, the world's most popular open source database. The sessions will offer you insights into the latest MySQL technical innovations and community developments. Check out the agenda.

 

Keynotes

We are very excited that Marten Mickos, CEO, Eucalyptus Systems, will be joining us to deliver the closing keynote at MySQL Sunday, in addition to Edward Screven, Oracle's Chief Corporate Architect and Head of the MySQL business.

 

Secure your seat

MySQL Sunday is open to all Oracle OpenWorld, JavaOne, and Oracle Develop attendees, including those with the value-priced Discover pass ($75 if you register by September 18). You will be asked if you are attending MySQL Sunday during the conference registration process. Register today.

• When: Sunday, September 19, 12:30 p.m. - 5:00 p.m.
• Where: San Francisco Marriott Marquis

 

PlanetMySQL Voting: Vote UP / Vote DOWN

There was a lot of talk about this being the next menace after email spam. I’m not actually sure what it’s called for VoIP systems, but my Asterisk setup has started to be attacked over the last few days. Lots of entries like:

[Aug 27 19:20:30] NOTICE[18826] chan_sip.c: Registration from '"742"<sip:742@a.b.c.d>' failed for '208.109.86.187' - No matching peer found
...
[Aug 31 10:13:10] NOTICE[18826] chan_sip.c: Registration from '"1002" <sip:1002@a.b.c.d>' failed for '41.191.224.2' - Wrong password

Lots of messages get logged a second and I noticed this as suddenly CPU load on my PC jumped up quite a bit.

For the moment I’ve routed these addresses via the interface lo0 so they won’t bother me any more, but I need to come up with a better solution.

First I’m curious if applications like Asterisk or FreeSwitch have any built-in anti-abuse controls to recognise bad behaviour and to disable those abusers. I’m pretty sure that I’ve not read about anything for Asterisk, and I’m currently reading the FreeSWITCH book I bought but haven’t come across this mentioned yet.  Seems that applications like this may need to have these controls added at some time, just as sendmail, postfix and most mail servers have had to adjust to a hostile world.

The other option of course is to use a firewall or packet filter to limit the incoming traffic rate from a single IP to port 5060 or whereever the SIP connection is being accepted so that when going over the limit the ip will be blocked for some time. iptables can do this I think so I’m going to have to read about how to configure and set that up.

There are other applications designed to watch logs and use them to automatically add temporary blocks. fail2ban is one of these. I’ll also have to see if I can configure it for this task.

So if this has happened to you how do you protect your VoIP systems from that hostile world of the Internet?

PlanetMySQL Voting: Vote UP / Vote DOWN

The development cycle is moving right along for the community’s newest MySQL benchmarking script. I’m pleased to announce that we now officially support FreeBSD (version 8.1 tested) so go ahead and download now and test your FreeBSD, Linux, or OSX MySQL server! Click here for the download.

Courtesy of Darren Cassar and some generous coding this weekend, we’re going to be releasing a auto-installer / updater for the application which you can use to automate that part of the process. Stay tuned for information on that release.

PlanetMySQL Voting: Vote UP / Vote DOWN

Are you thinking...not one more blog, please! We have received a lot of feedback that we at Oracle need to be more vocal about our investment and progress with MySQL. The MySQL team at Oracle is very excited to launch a new blog where we will offer you the latest and greatest updates on product announcements, news, events, customers, activities, and overall progress about MySQL. You can be sure to find a mix of technical and business content.

As you continue to follow your favorite MySQL bloggers, we also hope that you will add "Oracle's MySQL Blog" to that list over time.

I manage MySQL product marketing at Oracle. You will meet a number of my colleagues in product marketing, product management, community relations and product development over time as you see them write through this blog.

Thanks for listening, and we look forward to your feedback.

Monica

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Scenario: Master-Master replication
Description: Master A is the active db server whilst Master B is a read only swappable db server hence both are creating binary logs. During backup I run “FLUSH LOGS” in order to have a simpler point in time recovery procedure if that case arises.
Problem: Flush logs is mean mean command :) …. it rotates not only my binary logs but my error log too (since I user error-log=blahblahblah in my my.cnf). Well given I flush logs every night my error log is cycled through every night, but unlike binary logs which have an incrimental number attached to the fine, error logs only have a `-log` attached to the filename and a second “FLUSH LOG” would just clear all error logs permanently. That is really not fun believe me!

So what is the solution? you could either:
1. Not use “FLUSH LOGS” (nah that aint happenin)
2. Not use –error-log (that aint happenin either cos I need to use it for my specific setup)
3. Create an ugly hack script which saves the error log or renames it or whatever (Ugly Ugly)
4. Create a variable in MySQL which says: flush-log-ignore-error-log which if set “FLUSH LOGS” will know not to mess around with my error log. Hence … create a feature request (nicer and friendlier solution) and submit a tested patch too whilst at it.

If you think it is not idea that “FLUSH LOGS” recycles through error logs or and you have been bitten by the way it works like me, then feel free to put your vote into http://bugs.mysql.com/bug.php?id=56385&thanks=sub so that MySQL sees the importance of it.

Because Sharing is Caring!

PlanetMySQL Voting: Vote UP / Vote DOWN

The bidding war between HP and Dell for 3PAR has created great theater. The rationale is simple, both HP and Dell want a complete set of products to sell into the new cloud space and 3PAR is the only bitsized morsel among EMC, IBM and Hitachi that addresses this space. What is the compelling advantage they offer in storage? Elasticity. 3PAR provides the ability for companies to add/remove storage in thin slices (AKA thin provisioning). How does this relate to ScaleDB? We do the exact same thing for databases in the cloud and we do it for the most popular database in the cloud, MySQL.

How does VMWare play into this? Their CEO Paul Maritz was on CNBC talking about the hybrid cloud and how companies want to run core cloud capabilities on premise and then use the public cloud providers to handle compute overflow during peak usage. This means that public cloud value to corporations, assuming Maritz is correct, is based largely on their ability to provide elasticity. It will no longer be sufficient for public cloud companies to provide reserved servers, because the reserved servers will be run in the company’s data center. The public cloud will add/remove servers to handle peaks in usage. So elasticity is EVERYTHING. ScaleDB is all about elasticity for the database.

It is also interesting to note from the Maritz interview that he sees the next wave of cloud (and hence the next wave of cloud consolidation) coming from the software sector. More specifically, the ability to take existing applications and make them run on the cloud. In other words, to make them elastic. Again, this is exactly what ScaleDB does. We take existing MySQL applications and make them elastic.

It is also interesting to note that HP and Dell have decimated their own R&D and are now looking to acquire that expertise from outside, and they are willing to pay for the expertise.

Another theme playing out in the background makes this situation even more interesting. Oracle has adopted a systems approach, where they combine their hardware and software:

“The heart of the interview focused on Oracle's interest in Sun. By combining Sun's expertise in hardware with Oracle's software, Ellison suggested, the combined company can become a powerful "systems" company that sells solutions to businesses. The competitor that Ellison wants to beat: IBM.”

Summary: Cloud is the next battle ground. It all starts with the hardware/infrastructure (e.g. 3PAR) and then moves upstream to software. Oracle will be focused on selling complete systems, alienating HP & Dell, among others. This is compounded by the fact that HP and Dell have decimated their R&D, so they are forced to partner/acquire. At the same time, if Maritz’s vision of public clouds becoming effectively excess capacity for handling peaks from corporations is realized, then elasticity in the cloud will become critical as well. This obviously plays to ScaleDB’s strengths.
PlanetMySQL Voting: Vote UP / Vote DOWN

Last week I followed an very interesting ORACLE webinar delivered by Chris Mason : The State of Btrfs File System for Linux
BTRFS was initiated by Chris Mason who used to be responsible for Reiserfs at Suse and now works for Oracle. The first release started in 2007. BTRFS has been merged into Linux kernel in 2009. Now there are developers from REDHAT, INTEL SUSE, IBM, HP ... storage vendors. The project is very active. Ubuntu is considering to use it soon as its default filesystem. BTRFS is licensed under the GPL license. An interesting to read short summary of the life of BTRFS : A short history of BTRFS
...

PlanetMySQL Voting: Vote UP / Vote DOWN

To get information from a database it is necessary to execute a query to get this data.

Usually an ordinary SQL editor is used to create queries. To use such editor, one should remember the syntax of the SELECT operator and the names of tables and columns.

Let’s use a visual instrument developed specially to design queries, and see that it’s much easier to create queries visually instead of typing them in an editor.

Task:

It’s necessary to show the salaries of the employees of departments situated in different cities for the 2008 year in descending order.

We will do this on a MySQL server database. The process of creating this database was described in the How to: Create MySQL Database in One Shot article. You can Download MySQL Demo Database (or for SQL Server Download SQL Server Demo Database).

Solution:

Let’s create an empty document in dbForge Query Builder for MySQL (dbForge Query Builder for SQL Server).

After this let’s drag tables from Database Explorer to the diagram, the order of tables during dragging doesn’t matter.

As we can see, the application joins these tables automatically.

Query Builder: Query Diagram

Now let’s select the columns you need to get data from.

Click the checkbox near the Loc column of the dept table on the diagram, and after that the SalAmount column of the sal table.

You can see the selected columns on the Selection tab.

Query Builder: Selection Tab

Now let’s select the sum function on this tab in the column with aggregate functions for the SumAmount column.

Query Builder: Aggregate

Now it is necessary to set grouping by the Loc column, but the application selected to group data by the Loc column automatically. Let’s make sure of that by going to the Group By tab.

Query Builder: Group By Tab

Now we should cut the selection and keep only data of the 2008 year in the result.

To do this, let’s go to the Where tab and click the button with the green plus on it. The “=” symbol should appear. Let’s click the first phrase – <enter a value>.

Query Builder: Enter Value

After this the Operand Editor form should appear. Let’s select the Date and Time group from the Function list and double click the year(date) function in the list. After that let’s choose and double click the SalDate column in the other list.

Query Builder: Operand Editor

Let’s close the form and click the second one. Let’s enter 2008 there.

Query Builder: Type Constant

It’s time to execute the query we’ve created visually. To do this, let’s click F5.

Query Builder: Query Result

Now let’s look at the structure of the query we’ve created. To do this, let’s open Document Outline and open all nods.

Query Builder: Document Outline

Now let’s look at the DML of the created query. To do this, let’s go to the Text tab.

Query Builder: SELECT Query

Conclusion:

As we can see, the usage of a visual instrument for building queries allows to solve the task visually without going deep into the refinements of syntax of the SELECT statement itself and of the specifics connected with differences between MySQL and SQL Server syntax, to look at the syntax of the created query, to decrease the duration of the data selection process, and to look at the structure of the available query as a tree.

PlanetMySQL Voting: Vote UP / Vote DOWN