Planet MySQL

[NewsBot]

pgGearman 0.1 Released
20 July 2009, 1:59 pm

Similar to the MySQL and Drizzle user defined functions for Gearman, we now have PostrgeSQL functions too! These allow you to submit jobs to the job server from within your SQL query, trigger, or stored procedure. Here’s a snippet of how this looks in PostgreSQL:

shell$ psql test

test=# SELECT gman_do('reverse', 'Hello World!');

gman_do

--------------

!dlroW olleH

(1 row)

test=#

Special thanks to Selena Deckelmann for helping get these working!



Source: Planet MySQL

[NewsBot]

Sequences
20 July 2009, 1:24 pm

I’ve been editing a task description in our worklog: WL#827. It has to do with CREATE SEQUENCE, ALTER SEQUENCE, and using “sequence generators” to get the next number in a sequence. After the editing, I was able to set the private flag to ‘NO’, so now you can see the specification here: http://forge.mysql.com/worklog/task.php?id=827.

As always, one can “vote” on a worklog task that’s in forge.mysql.com, or comment on the corresponding feature request in bugs.mysql.com. I wouldn’t hold out much hope for this one, though. It’s been around since 2003, and we’ve always deemed that it’s higher priority to make minor changes to our auto_increment facility.



Source: Planet MySQL

[NewsBot]

[MySQL][Spider]Spider storage engine 0.14 released
20 July 2009, 12:41 pm

I'm pleased to announce the release of Spider storage engine version 0.14.(beta)

http://spiderformysql.com/

The main changes in this version are following.

- The standalone compilation with Spider storage engine is now available.

Please see "03_install_spider_storage_engine.txt" for more detail.

Tatsuhiko and Tetsuro,

Thank you for your advice!

Please see "99_change_logs.txt" in the download documents for checking other changes.

Spider storage engine was introduced at following with the usage.

The Data Charmer(English)

http://datacharmer.blogspot.com/2009/04/test-driving-spider-storage-engine.html

http://datacharmer.blogspot.com/2009/07/sharding-for-masses-spider-storage.html

?Giuseppe,

??Thank you for introducing Spider.

Unoh Labs(Japanese)

http://labs.unoh.net/2009/07/mysqlspider.html

Satoru,

??Thank you for introducing Spider.

Enjoy!



Source: Planet MySQL

[NewsBot]

Microsoft contributes to Linux kernel: a CAOS Theory Q&A
20 July 2009, 12:01 pm

Microsoft has announced that it is to contribute code to the Linux kernel development effort under the GNU General Public License (GPL) v2. What on earth does it all mean? Here’s our take on the situation. With thanks to Jay Lyman for his contribution to the following:



Q. This is a joke, right?

A. Not at all, although if any announcement is better suited to the image above, we can’t think of one. Microsoft has announced that it is going to contribute code to Linux under the GPLv2.

Q. What code is Microsoft contributing?

A. Microsoft is offering 20,000 lines of its own device drivers to the Linux kernel that will enable Linux to run as a guest on its Hyper-V virtualization technology. Specifically, the contributed loadable kernel modules enable Linux to run in ‘enlightened mode’, giving it efficiencies equivalent to a Windows virtual machine running on Hyper-V.

Q. Why is Microsoft doing this?

A. Red Hat and Novell’s Linux distributions already support enlightened mode, thanks to the development work done by both in partnership with Microsoft. One benefit for Microsoft of contributing to the kernel is that it reduces duplication of effort and the cost of supporting multiple, unique implementations of Linux. Once the code has been accepted into the kernel, Microsoft will use the kernel tree code as the basis for future virtualization integration development.

It also means that community Linux distributions will be able to use the code, which opens up more opportunities for Microsoft in the hosting market, where adoption of community Linux distributions such as Ubuntu, Debian and CentOS is significant. It also therefore slightly strengthens the challenge those community operating systems can make to Red Hat and Novell, which are more direct commercial challengers to Windows.

Make no mistake about it, Microsoft’s contribution is driven by its own interests. While it must serve and respond to enterprise customers that continue to drive the use of multiple operating systems and mixed environments, Microsoft also benefits by differentiating its Hyper-V virtualization technology from virtualization leader VMware. We believe Microsoft sees an opportunity to make virtualization with Windows more Linux-friendly than VMware.

Q. What’s in it for Linux?

A. The interoperability benefits previously reserved for ‘approved’ Microsoft partners will now be available licensed under the GPLv2, and available for all Linux distributions - commercial or community - without the need for a formal partnership.

The contribution of device drivers to the Linux kernel as been a sticking point for the Linux development community in the past as developers have struggled to encourage vendors to contribute driver code to the kernel. Microsoft is therefore setting something of a precedent and could encourage other vendors that have been reticent to contribute their drivers to do so.

The seal of approval Microsoft has given to the GPLv2 is also not to be overlooked. If Microsoft can find a way to contribute to Linux projects, many other organisations may also be encouraged to do so.

Q. I guess Linux is no longer “a cancer” then?

A. Exactly. Back in 2001 Steve Ballmer told the Chicago Sun-Times* “Linux is a cancer that attaches itself in an intellectual property sense to everything it touches. That’s the way that the license works.”

Reviewing the statement in the context of today’s announcement demonstrates how much progress Microsoft has made in the intervening years to understand open source licenses. Contribution to Linux, or to any other project under the GPL, would have been unthinkable at the time, and is still barely believable today. The announcement is likely to challenge perceptions of Microsoft’s strategy when it comes to open source, Linux and the most popular open source license.

*The original article is no longer available online. Plenty of references are still available, however.

Q. What does this say about Microsoft’s overall strategy towards open source?

A. The contribution is a significant sign that Microsoft is now prepared to participate with open source projects on their own terms by using the chosen license of that project and making contributions directly to the chosen development forge of that project. Microsoft continues to use its own CodePlex project hosting site for code releases, but if an existing open source project uses SourceForge then Microsoft has acknowledged that the best way to engage with that community is on SourceForge. Don’t expect this to be the last contribution Microsoft does under the GPL.

Microsoft is now becoming more proactive in how it engages with open source under a strategy it describes as ‘Open Edge’ (which we have previously mentioned here and here. Whereas Open Core is used by commercial open source vendors to offer proprietary extensions to open source code, Open Edge is Microsoft’s strategy to encourage open source development and application deployment on top of its suite of commercial software: Windows, Office, Exchange, Sharepoint, SQL Server etc.

The Open Edge strategy is rooted in attempting to ensure Microsoft’s commercial products continue to be relevant to the ecosystem of developers and partners that the company has attracted to its software platform. It is also a continuation of the realization that if customers and developers are going to use open source software, Microsoft is more likely to retain those customers if it helps them use open source on Windows et al.

For more details on Microsoft’s strategy towards open source, its partnerships with open source vendors, and its contributions to open source projects, see The 451 Group’s formal report on the contribution to Linux (the report will shortly be available via this link ).

Q. How is the contribution to the Linux kernel being handled?

A. The contribution is being made via an alliance with the Linux Kernel Driver Project and its maintainer, Greg Kroah-Hartman, who will steward the contribution into the Linux kernel code base. (Greg has a post up about it here).

Q. What are the intellectual property issues?

A. The copyright for the code will remain with Microsoft, with the contributor credit going to its engineering lead, Hank Janssen, group program manager at Microsoft’s Open Source Technology Center.

Q. And patents?

A. If we were putting money on the most likely conspiracy theory to emerge in response to this news it would be that this is a Trojan horse and Microsoft is contributing code to Linux that it will later claim patent rights over. Whether that is even theoretically possible depends on your understanding of the GPLv2.

The GPLv2 contains an implicit patent promise that some would say makes a Trojan horse impossible. However, the FSF obviously thought it necessary to introduce a more explicit patent promise with the GPLv3 to remove any doubt.

Ultimately this is a question for a lawyer, or an eloquence of lawyers (yes it is ironic, apparently). In the meantime, it is our understanding that Microsoft’s understanding is that contributing code using the GPLv2 includes a promise not to charge a royalty for, or assert any patents covering, the code being contributed.

Q. What about Microsoft’s prior claim that Linux infringes its patents?

A. Microsoft really dropped the ball on its communication of the suggestion that free software infringes over 200 of its patents, and tensions with free and open source software advocates are likely to continue to be tested by Linux-related patent agreements, such as the one struck with Melco Holdings last week, which have driven scepticism and mistrust of Microsoft among some key open source supporters.

Absent the company giving up on software patents altogether, we believe that in order to convince those FOSS advocates that it is serious about co-existence, Microsoft needs to find a way to publicly communicate details about those 200+ patents in such a way that is not seen as a threat and would enable open source developers to license, work around, or challenge them. We also believe that the company is aware of this, although finding a solution to the problem will not be easy. But then neither was contributing code to Linux under the GPLv2.



Source: Planet MySQL

[NewsBot]

How I stopped worrying and learned to love Twitter
20 July 2009, 11:04 am

I was warned by my brother a while ago that should I start tweeting, he would stage an intervention. I had already confessed to accounts with facebook, multiply, myspace, and several others (disclaimer/explanation: all of those sites are customers of my longtime employer). Twitter, to the uninitiated, looks like the crack cocaine of social networking that turns the weekend photo-poster into a hardcore jittering lifecaster. Nobody wants to see their family member come to that, right? But follow along, twitter has purpose. Or just skip to the bottom.

 I was never an active friendster user. The first site I used regularly was the more inward-facing multiply.com - and then mostly because it was an easy way to foist photos of my daughter on my extended family. Multiply is more of a community-based, relationship-savvy site than a place to find online friends. If I am cousin-of-John, it makes the assumption (with my consent) that I am interested in content by wife-of-John, brother-of-John, etc. Combined with who I have directly connected to, Multiply can quickly become a nice "walled garden" of family and friends content and connections. Easy media uploading, a slider I can set to "show me what the people close to me are up to", and that's really about all I needed.

 But then, like everyone else with a facebook account, sometime in the last 18 months a bizarre game of "this is your life" began. Invitations to connect came in from grade school friends, distant family, former co-workers, babysitters and fellow inmates. Scratch that last one.

 I'm in (facebook-only, mostly) contact with literally dozens of people that I hadn't talked to in 5-25 years, that I seriously doubt I would have ever heard from or about. Its really interesting, and I enjoy seeing the 2-3 things a week they note about their lives, the occasional photo or link to what they're doing. Unlike Multiply, facebook defaults to "everyone is an acquaintance" and gives you one firehose of updates sorted by when they were posted. Its fun to gaze over when I have a few minutes online and see who is up to what.

 Tweets then started to bleed in to the status update page. I knew of, and had zero interest in, this "text the world" service called Twitter. Why would I even use an online outlet to send a message to a friend of mine? I have an email address, cell phone number and at least one IM handle for most of my friends and families... can't I connect with them easily enough? So why on earth are there facebook updates like "hanging out with @tomjefferson in #philly #consitition #usa"? What's with all the @ signs and hashes?

Well, it was low-effort enough (no "friending", much less relationship definition, required for most posts) to review the updates for an individual person on twitter. Good friends who lived far away, co-workers involved in a crucial event, things like that (the celebs like Lance came later). To streamline the process of viewing those, I got an account.

 Twitter is the most stalker-friendly social networking site. Twitter by default does not even ask if you know or like anyone, only if you want to "follow" them. Seems kinda creepy for those unfamiliar with the Information Age. And it really is like a big water cooler on the internet. It's hard to resist joining in after a bit.

The photos of the kids still go to multiply, and I still watch facebook to keep up with a more extended group. But the most inane of updates and commentary are best put out to twitter, and here is why. My frustrated tweet when a desktop social networking client crashed on me:

pantoniades: #gwibber unstable on fedora 11. Need a new desktop Twitter client.

9:42 AM Jul 13th from Twitterrific  

Inane, right? On multiply I'd confuse more people than inform. I promise you most of my family doesn't understand the context for half of the words above. On facebook, that's just clutter. But on twitter, here's what happens next:

pauljakma: @pantoniades grab #gwibber 1.2.0 (e.g. from #Fedora rawhide) - works great

4:55 AM Jul 14th from Gwibber in reply to pantoniades

I don't know Paul Jakma. I'm guessing he's a gwibber developer or enthusiast. But more importantly, he's right. Yes, I could likely have found that tip in an IRC channel, bugzilla note or through some google search, but I was really not invested in this client. I got the fix, he kept somebody on his project, and neither one of us invested much effort (presumably he has a tickler on "#gwibber".

I haven't found any long-lost friends on twitter, and I'm not putting the photos of my kids goofing around in the bathtub on facebook. Perhaps there is one uber-site to rule them all, but I'm also quite happy with the three I've got. Provided I can dodge the van my brother sends to take me off to deprogramming.



Source: Planet MySQL

[NewsBot]

Using MySQL for Data Warehousing/BI/Analytics?
20 July 2009, 9:36 am

According to polls we do from time to time, data warehousing and analytics is one of the top use cases for the MySQL Server.  While knowing this is good, we’re now trying to get a better understanding of how you use MySQL for warehousing and analytics, so I’d love it if you’d respond via comment to this post or shoot me a mail at rschumacher@mysql.com. Thanks!



Source: Planet MySQL

[NewsBot]

The DB problem inherent to dynamic web pages
20 July 2009, 7:48 am

When building web sites, a popular demand is a maximum page load time.

For example, many would require < 0.5 seconds (or even less) for major pages loading time. Of course, there are numerous factors for page load time: network, caching, web servers, scripting language/code, database access and more.

Naturally I want to discuss the use of database access when creating web pages. I’ll be referring to dynamic web pages, such that are created by common languages as PHP, Java/J2EE, Ruby, ASP(.NET) etc.

A very common programming style is - what’s called in the Java jargon - using “scriptlets” as in the following JSP page:





Time now is <%= new java.util.Date() %>





The above replaces the “<%= new java.util.Date() %>” part with a text representation of the current time.

If I were to produce a dynamic content site, say, a WordPress blog, like the one you’re reading, I would need to generate several dynamic contents: the latest posts, the popular tags, the comments for this post, etc. These are generated by calling upon the database and running some queries. I suppose there’s nothing new in what I’ve explained so far.

The problem

When generating a “heavyweight” page, like some online newspaper or bookstore, there may be many queries involved. Are you logged in? Do we have recommendations for you? What are the latest topics? What have you been interested in before? Do you have friends online? What content have you produced on the website?

I’ve recently reviewed a site which generated > 500 queries per single page. I personally thought that was a very high number, but that was a necessity. The problem was: the page took 2 seconds to load.

Some tuning, rewriting and indexing later, time dropped to 0.6 seconds to load; but that was not fast enough. It was then that we got to a major conclusion:

All database calls are serialized. They need to be parallelized.

Remember that MySQL can only utilize a single thread for the computation of a single query (though more threads can handle IO in the meantime). This leads to only one CPU being used on your standard Linux distribution, for a given web page.

Really, that sounds just too obvious! But not so easy to achieve when doing “scriptlets”. The templating engine parses the scriptlets one by one, executing them in order. In fact, you assume it does so, so that you can rely on the outcome of the previous scriptlet in the next one. In Java, for example, it goes beyond that: a JSP page is rewritten as a normal Java Servlet class, where the “scriptlets” become the main code, and the HTML becomes just printing to standard output. So you get linear executing code.

Even with more sophisticated frameworks, the “normal” way of doing things is linear. For example, using the Spring framework, you have Java objects — controllers — which are responsible for web pages. You can avoid doing scpriting within your dynamic web pages, and only ask for data provided by those controllers. So, for example, using Spring + Velocity, a web page could look like this:





Login time as recorded in DB is: ${user.loginTime}





This (usually) translates to calling the getLoginTime() method on a pre-built user object. But just how does this method work?

Does it do lazy initialization, so that it calls upon the DB to get the answer?

Did the controller set up the value during some init() method?

Did the controller set up the value in response to the web page’s request parameter, parsing them one by one?

All the above options lead to linear, or serial execution.

How to parallelize?

Parallelization with web pages is not so simple, and requires understanding of multi threading programming. The programmer needs to be aware of race conditions, deadlocks, starvation issues, etc. (though, to be honest, in dynamic web pages context these do not usually become a real issue). Some programming languages provide good support for multi threaded programming. Java is one such language.

Let’s assume, then, that we need to spawn some 10 queries in response to a page request. With Jjava, we can write something like:

CountDownLatch doneSignal = new CountDownLatch(10);

Runnable task1 = new Runnable() {

public void run()

{

user.setLoginTime(this.jdbcTemplate.queryForInt("SELECT ... FROM ..."));

doneSignal.countDown();

}

} ;

Runnable task2 = new Runnable() {

public void run()

{

headlines = getSimpleJdbcTemplate().query("SELECT * FROM headline WHERE...",

new ParameterizedRowMapper() {

public Headline mapRow(ResultSet rs, int rowNum)

{

Headline headline = new Headline();

headline.setTitle(rs.getString("title");

headline.setUrl(rs.getString("url");

...

}

}

doneSignal.countDown();

}

} ;

...

Runnable task10 = new Runnable() {

...

doneSignal.countDown();

}

Executor executor = Executors.newFixedThreadPool(numberOfAvailableProcessors);

executor.execute(task1);

...

executor.execute(task10);

doneSignal.await();

// Now fill in the Model

The above code is simplified and presented in a way which is more readable. What it says is:

Let’s create the 10 tasks, but not execute them: just lay out the commands.

Each task, upon completion, lets the CountDownLatch know it has completed (but remeber we have not executed it yet).

We create or use a thread pool, using some n threads; n may relate to the number of processors we have.

We ask the pool to execute all threads. At the discretion of the pool, it will either run them all concurrently, or some sequentially - depending on how many threads are available.

We ask the CountDownLatch — a one-time barrier — to block, until all 10 tasks have notified they’re done.

We can now go on and do our stuff.

Spring has a built in TaskExecutor mechanism to provide solution similar to the thread pool above.

I’m mostly a C/C++/Java programmer; I have no knowledge on how this can be achieved in PHP, Ruby, ASP.NET or other languages. The above code is certainly not the most straightforward to use. I would like to see frameworks provide wrappers for this kind of solution, so as to support the common web developer with parallelization.



Source: Planet MySQL

[NewsBot]

Doxygen output for MySQL Cluster NDB API & MGM API
20 July 2009, 5:37 am

NDB API Documentation

A new page has been added to this site: NDB API Docs which presents the information from the header files for both the NDB API and the NDB Management API.

The material has been generated using doxygen and will be refreshed shortly after any new major, minor or maintenance release is made generally available (starting from MySQL Cluster 7.0.6).



Source: Planet MySQL

[NewsBot]

OpenSQL Camp 2009: CfP has ended, vote for your favourites until July 26th!
20 July 2009, 5:07 am



The Call for Papers for the OpenSQL Camp 2009 (European Edition) has ended yesterday — we received 27 excellent session proposals from various Open Source Database projects. I would like to say a big "Thank You" to everyone who submitted a talk! In the beginning I was a bit concerned that the conference would become too MySQL-centric, but this fortunately changed in the last few days.

Sadly we now have more than double the amount of sessions than we can actually host, which means that we will have to review and vote on the sessions to distill the final program. We've formed a small committee that will perform this task (currently consisting of Sheeri, Giuseppe and myself) and is in charge of finalizing the schedule. But you can help us with this! We are seeking your input on which sessions you would like to attend until Sunday, July 26th (12:00pm PST).

The system basically asks for "one vote per person per session", giving you a chance to influence the program yourself: you can cast one vote for each session by either stating you would be interested in attending it (+1) or not interested (-1). Additionally, you can add a comment for each vote to provide a reasoning for your choice. Ideally, you should of course plan to actually attend our event, but you are welcome to tell us what you would have been interested in, in case you can't make it to the Camp. See the OpenSQL Camp wiki page for more details and instructions. You can submit your votes via Twitter or the OpenSQL Camp mailing list.

We look forward to your input!



Source: Planet MySQL

[NewsBot]

Recovery after DROP [ TABLE | DATABASE ]
20 July 2009, 12:42 am

In your recovery practice we often face the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can’t help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm – files are deleted in this case.

So first step after DROP is to restore files, and for ext3 file system there are two utilities which can help of you are fast (and lucky) enough.

First one is ext3grep http://code.google.com/p/ext3grep/, with some instruction on this page http://www.xs4all.nl/~carlo17/howto/undelete_ext3.html.

And also there is TestDisk, with wide list of functionality, one of them is to restore deleted files.

I don’t know such tools for xfs, so in this case DROP will be fatal.

But better advise is do your backups

Entry posted by Vadim |

No comment

Add to: | | | |



Source: Planet MySQL

[NewsBot]

MySQL Performance: Why Ahead Flushing for InnoDB?...
20 July 2009, 12:22 am



Recently I've done several performance studies to find the most optimal

way to fix the "furious flushing" observed during all my benchmarks with

InnoDB. The initial report about this work you may find

here including initially proposed patches. But analyzing test

workloads with the latest MySQL performance build I think there is a

need to go more far...

First of all let's me present you the result I've got on the Read+Write

workload @dbSTRESS - first part of graph represents the TPS level

obtained with MySQL 5.4, and the second part - with the latest MySQL

performance build (#46):



This Read+Write workload is running non-stop during one hour within the

same conditions in both cases:

16 CPU cores

32 concurrent user sessions

innodb_thread_concurrency=16

innodb_log_file_size=1024M

As you may see, reaching 10,000 TPS over 6,000 represents a huge

gain! 66% to be exact. On the same time you may observe even more

pronounced periodic performance drops due "furious flushing" (see

also previous posts ).

Why it happens?...

The worst case during a high Read+Write activity on InnoDB arrives when

the redo log has no more free pages! What does it mean?.. - it means

that the oldest redo log data are still referencing on that moment to

some modified (dirty) pages which are still kept in the Buffer Pool and

these pages are still not flushed to the disk! So, redo log cannot

recycle the oldest blocks until the oldest pages are not flushed!.. As

you may imagine, what's going next? - At any price redo log

should obtain free blocks to continue working (otherwise you may observe

a total freeze of your database activity). So at any price it involving

dirty pages flushing as fast as possible - it calls buf_flush_batch()

function - and from my observations it may call it more than several thousands

per second(!) - so we are far away here from respecting a system I/O

capacity or other limitations - the goal here is to avoid a database

activity freeze at any price!..

However, a short freeze is happening anyway - and as the result we may

observe a periodic performance drops (my stats simples on the previous

graph where 10 sec - and on the lower time interval it may be seen

probably even better)..

Any solution currently implemented to deal with such a problem?..

you may set InnoDB dirty pages percentage limit to the very low value (innodb_max_dirty_pages_pct)

and force dirty pages flushing to be more aggressive

you may use Percona's "Adaptive Checkpoint" patch (or XtraDB instead

of InnoDB as well! :-))

BUT! As I demonstrated

previously , on the really high Read+Write workload the Master

Thread is never leaving a purge loop, so the innodb_max_dirty_pages_pct

settings is never checked, as well Adaptive Checkpoint code (as it

currently implemented) is not reached either...

So, what to do here?..

Supposing the purge loop is really necessary by design, my idea was to

add a sort of "Ahead Flushing" inside of the purge loop. Why inside?.. -

Until while Master Thread is leaving purge loop at least everything is

still working as expected. The problems are coming when this loop

become infinitive! :-)

My initial

solution contained just 2 lines to add into the purge loop:

if (UNIV_UNLIKELY(buf_get_modified_ratio_pct() > srv_max_buf_pool_modified_pct/2 )) {

n_pages_flushed= buf_flush_batch(BUF_FLUSH_LIST, PCT_IO(20), ut_dulint_max);

}

Which means: if the current level of dirty pages percentage is reached a

half of the dirty pages limit setting - flush the next part of dirty

pages aligned with 20% of I/O capacity settings.

Of course such a code changes is preferable to be optional and enabled

via my.conf settings (for ex.: ahead flusing on/off, starting dirty

pages percentage, how much of I/O capacity to use, etc.).. Then spending

hours discussing with Mikael the

probe patch was born - it's adapting automatically the flushing

frequency according to the reached dirty pages percentage level and also

respecting the max I/O capacity settings given by the user (Mikael

absolutely insisted on this point)... Sounds very good! The only problem

- it seems to be too light yet to face the "furious flushing" observed

during the high Read+Write workload! And with the latest performance

build the flushing become truly "furious" :-)

To go further, instead of the "light" solution I've added my initially

proposed changes into the latest performance build and here is it what

I've observed (first part of graph is corresponding to the default code,

the second one - with added Ahead Flushing code):



It's still not optimal yet, but looks better then default:

TPS level became more stable

same with a writing activity

There are also positive changes on the checkpoint age and log writes

stability:



Same for:

pages activity

dirty pages level



 

Well, it's already looks good, but should be much more better :-)

Looking for the optimal solution

first of all not only the percentage of dirty pages should be checked

but also the checkpoint age value! - the redo log may be out of the

free blocks much more earlier than the dirty pages level will be

reached!

probably the mix with Percona's Adaptive Checkpoint will be the most

optimal?..

as well more instrumentation on the InnoDB code will be needed to

increase internals visibility - for example it'll be nice to add few

more statistic values into "show innodb status" saying how many times

there was no more space in the redo log, how many times the buffer

flush was called, etc...

also: may we leave the purge loop time to time or not?..

What do you think?...

Any comments are welcome! :-)



Source: Planet MySQL

[NewsBot]

The Blog v.2.0
19 July 2009, 10:19 pm

Long time ago, in 2002 I decided to create my own point of presence in the Internet. Back then I’ve got pretty nice domain (kovyrin.kremenchug.net), hacked up a few pages on php, added a guestbook and that was it. Many years it was almost static and I did a few updates on my resume page few times a year. Later I’ve switched the site to wordpress to make it easier to manage my resume and stuff…

And 3 years ago in March 2006 I’ve decided to start my own blog. I too a standard template and started the blog on a separate domain while the domain was on its own domain name… This spring my wife made me a great birthday present – he’s created me a custom blog design that has all the stuff I wanted from my own web site for a long time. My friend Dima Shteflyuk has helped me with creating a wordpress template from Tanya’s mockups and here we are – now I’ve decided to merge my blog and my web site into a single web entity called http://kovyrin.net/. Welcome to my new blog/site/whatever!









Source: Planet MySQL

[NewsBot]

Crashing bugs
19 July 2009, 12:38 pm

What does it mean to release a server with no known crashing bugs? I don't know. A lot of this depends on your perspective. Perhaps old releases of MySQL were done with no known crashing bugs, but this means that testing for current releases is much more rigorous. If you don't believe me then read some of the bug updates from Shane and others to understand how good they are at testing it.

I judge quality by whether I can depend on the code in production. Both 4.0 and 5.0 have been stable in production for me and both have had crashing bugs. I am sure that MySQL 5.0 releases have had more known bugs than 4.0 releases as 5.0 has more features, a larger community testing it and is run on much larger hardware (more RAM, more disks, multicore, more load). Note that my perception of 5.0 is based on using it with many features disabled including query cache, subqueries, stored procedures, triggers and views.

The quality metric that I am interested in is the number of major releases for a feature to become stable in production and feature complete. I don't mind when this number if more than 1 as long as I can disable the feature. But this number should never exceed 2.



Source: Planet MySQL

[NewsBot]

MySQL Performance: Once again about InnoDB thread concurrency...
19 July 2009, 4:37 am



Even this topic was covered several times already, the question is still

open - use or not to use InnoDB thread concurrency?.. And the only true

answer here - "It depends" :-) and I'll try to make a short summary

about the current state here.

The main current InnoDB bottlenecks are all mostly due to a high

contention on internal locks. As all multi-threaded (as well

multi-process) applications giving a concurrent access to its resources,

InnoDB have to guarantee there is only one thread may modify a given

resource on the same time. This guarantee is achieved by locking a

resource in exclusive mode and implemented with mutexes. Depending on

implementation, some locks may become very hot with a growing access

concurrency. And it's exactly what's happening with InnoDB...

Things where improved a lot with Google patches and continuing with

MySQL 5.4 version. But even if several lock bottlenecks were

successfully removed, the lock contention is still here. Why it's a

problem?.. - Usually when you have any kind of lock contention it's

never linear! And with a growing sessions/users concurrency you may

observe a dramatic performance drop (like your 32 users will work slower

rather only 2, etc.)..

When you deal with inevitable locking which in any case cannot be

avoided, but you still expect to keep the highest possible performance

level AND growing workload - the only possible solution you have is to

reduce a concurrency on the hottest locks. That's why I'm a part of

people who thinking that InnoDB thread concurrency option is a very good

feature! :-) I've already

wrote  about why, and here will just stop on two current InnoDB

thread concurrency models:

Default Model

The default model was greatly

described  by Percona Team. In short InnoDB places all threads

(sessions) into its execution wait queue and keeps no more than a given

number of active threads on the same time (set with innodb_thread_concurrency=N

option).

Positive/Negative:

(+) reducing a lock contention!

(+) each thread (session) is guaranteed to have a CPU time within a

given time period!

(-) uses a lot of locking itself, so not scaling very well...

Timer-Based Model

Introduced within MySQL 5.4, this model is looking very promising and

performs much more better! And you may read from Mikael's blog how

exactly it works .

Positive/Negative:

(+) lock contention reduced even better!

(+) short sleeping used instead of locking, so scales better!

(-) no guarantee that each thread (session) will have a CPU time

within a given time period - may be a problem since 512 concurrent

user sessions (until 512 it's still ok)...

As usual, one image says more than many words:



Since 32 active user sessions setting innodb_thread_concurrency=16

helps a lot!

Main problem of both models

But both models are still suffering of the same problem: when any of

"active" threads (sessions) is doing an I/O operation (disk read) it

continues to keep its "active" state (and I/O operation may take tens

ms(!)) - while another session may during this time already get its data

cached from a buffer pool and finish its query!... So sometime you may

observe a significant global performance drop during a such moment!..  

- Ideally each session before doing an I/O operation should remove

itself from an "active" state and leave others working. So, there is

still a room for improvement :-)

The optimal solution

There is no optimal solution :-)

Always monitor your workload and check your buffer pool state, buffer

hits, page reads, I/O activity and number of active/concurrent sessions

If you have more than 32 concurrent sessions and low I/O read activity

- setting innodb_thread_concurrency=16 will help

Otherwise leave innodb_thread_concurrency=0

And always continue monitoring! :-)

Any comments are welcome! :-)



Source: Planet MySQL

[NewsBot]

Crashes while using MyISAM with multiple key caches
19 July 2009, 12:51 am

Over last couple of years I have ran into random MySQL crashes in production when multiple key caches were used.   Unfortunately this never was frequent or critical enough issue so I could spend time creating repeatable test case  and search of the bug in the MySQL database did not find anything.    Recently we had this problem again and now discussed it with Monty’s team  – this time we found the bug for this issue.

It is no surprise why I could not find the bug easily – the bug is not really related to multiple key caches but to online key cache resize.  It is just this code most actively used in case you’re using multiple key caches.   It is very rare one would resize single key cache in production and it only triggers crash sometimes, while if you’re using multiple key caches there are often some scripts in place which adjust their size or change mappings of the tables.

No let me put my complain hat on.  Looking into this crash bug I see it was opened in early 2006, this is 3.5 years ago when I was still with MySQL living in Seattle area…. looks like century ago really.

As usually with such tricky bugs Shane Bester  steps in to create a test case to crash it soon.  Shane seems to be the best guy in the universe when it comes to making non repeatable bugs repeatable.

It took over a year to complete the bug fix.  For all this time bug existed in 4.1, 5.0 and 5.1 trees.      When bug was fixed and closed  (so you will not find this bug if you search for active bugs) but in reality it was only fixed in MySQL 5.1 while  even though bug closed MySQL 5.0 and MySQL 4.1  still did not have it fixed.

It was planned for it to be backported in MySQL 5.0 after MySQL 5.1 is in production for a few months but I trust this was forgotten.

Now this looks like the nastiest bug  type to me.  This is the bug in relatively rarely used feature – I’d expect  less than 1% of people would resize key cache while server is up or use multiple key caches.   The fix of it is not easy and may affect 100% of the users if bug is introduced.   It is also race condition bug so not all people using the feature will run into the bug – In my practice number of people using multiple key caches with relatively light load never ran into this issue.    

It is the hard choice what to do in such case but I think  closing the bug and leaving it unfixed at the same time is not a good idea.  At least I’d like to see the bug “cloned”  for version 5.0 and 4.1 so it is can be found as active bug and it is clear these versions still have it.   Or may be have some special bug status to indicate it was fixed only in later MySQL versions but left unfixed in former ones ?

I also would like to have no crash bugs in the server.   If there are some features which are known to be buggy and crashing I’d like to see them disabled as run time and enabled with –enable-buggy-features switch (I’m sure marketing can come up with better name for this one)

Entry posted by peter |

No comment

Add to: | | | |



Source: Planet MySQL