Planet MySQL

+0

-2

posted by Jonathan Levin on Mon 08 Feb 2010 22:54 UTC (Add To MySQL Librarian)
Tags:

(edit)

Today I reached 109k Queries per Second. I was quite impressed by it.
Some background on the situation.
I developed some stored procedures to process some rather large tables we had in our database.
I managed to get the stored procedures to be very efficient and quick.
I then wanted to test it out and tried to overload the server to see how much it could take.
Normally, the server would do around 1k at best with these kinds of tasks. I have recently been able to tweak it to 20k QPS. But today, for some reason, the cache managed to get itself in the right position and produced this result.

The Server:
A 4+ year old

[Read more...]

Don't forget the COMMIT in MySQL

+0

-0

posted by Geert Vanderkelen of team blog MySQL Support Team on Mon 08 Feb 2010 22:29 UTC (Add To MySQL Librarian)
Tags:

(edit) mysql, Python, myconnpy

Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you'll have to commit (or roll back) your inserts, deletes or updates.

I've seen it a few times now with people being surprised that no data is going into the tables. It's not so a silly problem in the end. If you are used to the defaults in MySQL you don't have to commit anything since it is automatically done for you.

Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off.

+3

-3

posted by Baron Schwartz (xaprb) on Mon 08 Feb 2010 19:36 UTC (Add To MySQL Librarian)
Tags:

(edit) postgresql, sql, checkpoint, checksums, CRC32, innodb, mysql, recovery, wal

I explored two interesting topics today while learning more about Postgres.

Partial page writes

PostgreSQL’s partial page write protection is configured by the following setting, which defaults to “on”:

full_page_writes (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint… Storing the full page image guarantees that the page can be correctly restored, but at a price in increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the

+1

-0

posted by Brian Aker on Mon 08 Feb 2010 18:42 UTC (Add To MySQL Librarian)
Tags:

(edit)

News Monday!

Matt Assay to JOIN Canonical as COO

This took me a bit by surprise at first. I don't find myself often agreeing with Matt. Most of what he tends to write/argue for is what I have referred to in the past as "crippleware". Canonical in recent time has taken to opening up their platform. I've been a strong advocate for Launchpad, it is a great service. I love that they opened it up in recent time. When it comes to infrastructure software on the size of LP, I don't believe that many others will ever install it. Slash, G-Forge, and the Livejournal software are examples of infrastructure software that approach the size or outweigh the LP codebase. They have rarely been successfully deployed by others. The advantage in the Launchpad software being open source is

+3

-0

posted by MySQL Performance Blog on Mon 08 Feb 2010 17:31 UTC (Add To MySQL Librarian)
Tags:

(edit) announce, Benchmarks, mysql

We have been using tpcc-mysql benchmark for long time, and there many results published in our blog, but that's just single workload. That's why we are looking into different benchmarks, and one
of them is TPCE. Yasufumi made some efforts to make TPCE working with MySQL, and we are making it available for public consideration.

You can download it from our Lauchpad Percona-tools project, it's
bzr branch lp:~percona-dev/perconatools/tpcemysql

Important DISCLAIMER:
Using this package you should agree with TPC-E License Agreement,
which in human words is:

You can't name results as "TPC Benchmark Results"
You can't compare results with results published on

+2

-2

posted by Darren Cassar on Mon 08 Feb 2010 17:08 UTC (Add To MySQL Librarian)
Tags:

(edit) Databases, Intermediate, os, Uncategorized, access, Automation, bash, expect, mysql

If you have multiple database servers with strange names, or if you have to hop over multiple machines to connect to any mysql database server, then you know what a pain it can be to administer such a setup. Thanks to some scripting, you can automate such tasks as follows:

Create an expect script:
/path/to/sshmysql.exp

#!/usr/bin/expect -f
#script by darren cassar
#mysqlpreacher.com

set machine [lindex $argv 0]

set timeout -1

spawn ssh username@$machine
match_max 100000
expect -exact “assword: ”
send — “password\r”
send — “sudo -k; sudo su – mysql\r”
expect -exact “sudo -k; sudo su – mysql”
expect -exact “assword:”
send — “password\r”

[Read more...]

Copyrights and wrongs

+0

-0

posted by The 451 Group on Mon 08 Feb 2010 16:17 UTC (Add To MySQL Librarian)
Tags:

(edit) Licensing, software, 451 group, 451caostheory, 451group, bradley m Kuhn, caostheory, copyright assigment, free software foundation, Linux, Mark Shuttleworth, matt aslett, mattaslett, matthew aslett, matthewaslett, mysql, open-source, opensource, Oracle, sun, The 451 Group, the451group

One of the issues I have with the Free Software approach is that advocates have habit of throwing the baby out with the bathwater when discussing issues that they see as in any way negative to free software.

I was reminded of this while reading Bradley M. Kuhn’s criticism of Mark Shuttleworth’s reported views on copyright assignment.

Having read the original interview with Mark, and then Bradley’s response, it is pretty clear that the two have very different perspectives on copyright assignment: Mark is speaking from the perspective of a commercial business, Bradley form that of a non-profit foundation.

The two entities have very different

+5

-0

posted by George Trujillo on Mon 08 Feb 2010 15:07 UTC (Add To MySQL Librarian)
Tags:

(edit) MySQL and Oracle

Ken Jacobs has been a fantastic advocate of the Oracle and MySQL user communites. I met Ken on the board of the Independent Oracle Users Group (IOUG). While Ken was the board liasson on the IOUG board, he was always supporting the Oracle user groups and made very important contributions throughout his time on the board and afterwards. After serving time on the board, Ken was still always

Ken we will miss you!

+7

-0

posted by Paul McCullagh on Mon 08 Feb 2010 11:34 UTC (Add To MySQL Librarian)
Tags:

(edit)

What does it take for someone, fiercely loyal to a company to suddenly leave? Ken Jakobs, Oracle employee number 18, a man that sincerely loves the company, has resigned! The only reason I can think of is an extreme snub!

I must say, I am very disappointed. The prospect of Ken running MySQL was a light at the end of the tunnel for the community. Why? Because Ken is a MySQL insider! He knows the project, he knows the community.

As an engine developer I have come to know Ken well over the last 4 years. He lead the InnoDB team and is largely responsible for the improvements made to the engine since the Oracle acquisition. At the yearly Engine Summit he was always professional and constructive in his suggestions, with a deep technical knowledge of the subject. His track record shows that he has always kept his

[Read more...]

Python, oursql and MacOS X 10.6 (Snow Leopard)

+2

-0

posted by Geert Vanderkelen of team blog MySQL Support Team on Mon 08 Feb 2010 10:09 UTC (Add To MySQL Librarian)
Tags:

(edit) mysql, macos, howto, community, Python

This post explains how to compile oursql and install it on MacOS 10.6. oursql is a Python database interface for MySQL, an alternative to MySQL for Python (i.e. MySQLdb) and MySQL Connector/Python.

First, find out which MySQL you installed. This can be either the 32-bit or the 64-bit version. To make sure, find the mysqld (e.g. in /usr/local/mysql/bin) and do the following in a Terminal window:


shell> file /usr/local/mysql/bin/mysqld
.../mysqld: Mach-O 64-bit executable x86_64

If you see x86_64, you got 64-bit, otherwise 32-bit. If you see both, then you have

+0

-0

posted by Jim Tommaney of team blog Calpont & InfiniDB Project Team on Mon 08 Feb 2010 09:35 UTC (Add To MySQL Librarian)
Tags:

(edit) 10k Scale Factor

I wanted to offer another InfiniDB load rate metric using the SSB lineorder fact table. In this case we are using a scale factor of 10,000 which translates to 60 Billion rows. As a point of reference, the recent Percona benchmark was at a scale factor of 1000 (6 billion rows) http://www.mysqlperformanceblog.com/2010/01/07/star-schema-bechmark-infobright-infinidb-and-luciddb/ .
The load rate per hour varied only slightly across the entire run, averaging about 4Read More...

Poor Matt, poor Ken

+2

-5

posted by Henrik Ingo on Mon 08 Feb 2010 09:11 UTC (Add To MySQL Librarian)
Tags:

(edit) community, microsoft, mysql, Open Source, ubuntu

Well, for Matt Asay, I should start by congratulating you for the new job and nice title! (Also, we learn some intelligence from Matt's blog: apparently Canonical is already close to the size of MySQL AB at the time of the Sun acquisition.)

Usually we are told to "ignore the trolls" and all that. The blogosphere unfortunately seems to be full of commentators who like to have share their opinion - even while they are entirely clueless. Sometimes, like the comments on Slashdot, it is ok and considered part of the entertainment. Sometimes it is harmless, because nobody reads that blog. And sometimes, it is just unacceptable:

+1

-0

posted by Kedar on Mon 08 Feb 2010 08:56 UTC (Add To MySQL Librarian)
Tags:

(edit) Technical, mysql, stored procedure

Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in some [...] Related posts:

Stored procedure – Execute query if table or Column exists

Stored procedure to Find database objects

MySQL Stored procedure – Split Delimited string into Rows

Ken Jacobs leaves Oracle

+1

-5

posted by Open Query on Mon 08 Feb 2010 07:44 UTC (Add To MySQL Librarian)
Tags:

(edit) Uncategorized, edward screven, ken jacobs, mysql, open query, Oracle

Matt Asay writes today in Oracle loses some MySQL mojo about Ken Jacobs leaving Oracle. For me, that’s a major bummer. Ken has been a long-time visitor of the MySQL Conference and that’s where I first met him: a friendly and knowledgeable person, on database technology in general but also about MySQL. When Innobase Oy got bought by Oracle, InnoDB got placed under Ken’s leadership and did pretty well there. We’d occasionally exchange emails, and I’ve always found him to be responsive and helpful.

I think it was kinda presumed by people that the technical part of MySQL at Oracle would also reside with Ken. Obviously now, that’s not going to be the case. What that means exactly, I don’t know as I am not familiar with the other person (Edward Screven). We’ve got to

+2

-0

posted by Robin Schumacher of team blog Calpont & InfiniDB Project Team on Mon 08 Feb 2010 02:15 UTC (Add To MySQL Librarian)
Tags:

(edit)

We received some nice feedback on our care and feeding of InfiniDB blog entry, and we appreciate all of you who were kind enough to respond. We did fail, however, to communicate a few other intentions we have regarding how we plan to release and label the InfiniDB software so here’s some more thoughts from us on this important matter:

For new releases, we plan to follow the traditional alpha, beta, RC framework. Alpha means an upcoming release is not yet feature complete and moreRead More...

How often should you use OPTIMIZE TABLE?

+3

-0

posted by Baron Schwartz (xaprb) on Sun 07 Feb 2010 22:39 UTC (Add To MySQL Librarian)
Tags:

(edit) sql, clustered indexes, defragmenting, fill factor, innodb, instrumentation, mysql, page splits

Many times I’ve heard people advise on “best practices” for a MySQL database. This often includes routine maintenance, such as “you should run OPTIMIZE TABLE on all of your InnoDB tables once a week to defragment them for better performance.”

But this advice is unsubstantiated and could even be detrimental. Here are some of the obvious problems that I can think of:

The optimized table compacts the primary key (clustered index) to its default 15/16ths fill factor per page. But other indexes will be built in pseudo-random order and are likely to end up just as fragmented afterwards as before. Which indexes are more important for performance? Maybe the primary key is just a dummy value that’s not even used, and the secondary indexes are the ones that would benefit from compacting.
Suppose the

+0

-0

posted by Venu Anuganti on Sun 07 Feb 2010 22:01 UTC (Add To MySQL Librarian)
Tags:

(edit) database, mysql, Windows, Bison errors on windows, bison: m4: Invalid argument, generating yacc files windows, how to build mysql on windows, Windows mysql build

In case if you working on Windows environment for MySQL development (sometimes I use visual studio for easy debugging); and in case if you change the parser code (sql_yacc.yy) or if you are working directly from development branch (bzr launchpad), then the build breaks to generate the yacc files (sql_yacc.h and sql_yacc.cc) with an error bison: M4: Invalid argument as shown below:

1>------ Build started: Project: sql, Configuration: Debug Win32 ------
1>Generating sql_yacc.h, sql_yacc.cc
2>------ Build started: Project: GenServerSource, Configuration: Debug Win32 ------
2>Generating sql_yacc.h, sql_yacc.cc
1>bison: m4: Invalid argument
1>Project : error PRJ0019: A tool returned an error code from "Generating sql_yacc.h, sql_yacc.cc"

[Read more...]

Oracle loses some MySQL mojo

+3

-2

posted by Matt Asay on Sun 07 Feb 2010 21:25 UTC (Add To MySQL Librarian)
Tags:

(edit)

Oracle veteran and MySQL sympathizer Ken Jacobs has resigned from the database giant, calling into question Oracle's ability to deliver on its MySQL promises.

Conditional Joins in MySQL

+0

-0

posted by Ilan Hazan on Sun 07 Feb 2010 19:43 UTC (Add To MySQL Librarian)
Tags:

(edit) Cool MySQL Queries

One way to do a “Conditional Join” in MySQL is by using a “LEFT JOIN”. Create a “LEFT JOIN” for each condition and combine the results into one column using an “IF” statement by the “SELECT” expression. Here’s an example: Suppose you have three tables: questions: a table consisting of question ids, timestamps, and whether or [...]

How To Set Up MySQL Database Replication With SSL Encryption On Ubuntu 9.10

+2

-0

posted by of team blog HowToForge on Sun 07 Feb 2010 17:05 UTC (Add To MySQL Librarian)
Tags:

(edit) ubuntu, mysql, Security

How To Set Up MySQL Database Replication With SSL Encryption On Ubuntu 9.10

This tutorial describes how to set up database replication in MySQL using an SSL connection for encryption (to make it impossible for hackers to sniff out passwords and data transferred between the master and slave). MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

Search

Profile

Top Voted (Last Week)

Top Voted (Last Month)

Top 20 Most Active Authors

Top 10 Most Active Team Blogs

MySQL Links

Other Links

Planetarium

Partial page writes