Thanks to Jayant, I had to clean coffee off my screen, but keyboard is a bit more tricky. This is Postgres advocacy at its best:

The base timings are almost 5 times more in mysql as compared to pgsql.

Then in comments, Harrison points out:

From the numbers, it is hard to believe that PostgreSQL is really fsync’ing properly there. I doubt a laptop HDD can do 1000 writes per second. Even assuming we have perfect group commit going on, with two threads it is still 500 per second for the first test.

InnoDB’s numbers are about right on for writes per second for a cheap commodity hdd.

Jayant’s answer is:

You may be right. But postgres is a more advanced and reliable database as compared to mysql.

OSSCube’s internationally renowned team of MySQL Experts - Sonali Minocha and Rakesh Kumar, lead a successful workshop on setting up MySQL High Availability for your MySQL Database servers. Scheduled on the second day of the conference, September 20th, the workshop was jam packed with enthusiastic participants.

Sonali, Asia’s first MySQL certified DBA and an internationally renowned MySQL expert, …

Any corruption in MyISAM table is a terrible situation for Database Administrator. These MyISAM tables are the most important components in the MySQL Database to store personal as well as professional data, and the corruption in these tables may inaccessibility to the database. In this situation, a database backup allows you to restore the database and access the records in the table. Duplicate file (Backup) of the database is mostly stored on a different storage media, the changes of database corruption still exist. In that situation, you will need to perform MySQL database repair by the using of third party software.

Some error messages that enable you to identify that your MyISAM table (student) is corrupt:

“Student.frm is locked against change”
Or
“Got error message student from table handler”

Or
“Can't find file student.MYI (Errcode: nnn)”

The record …

The current MySQL table checksum is very simple: it's basically the same as CRC32(CONCAT(all data in the table)).
Since long ago we've had customers complain about the algorithm, but one doesn't change such things every day.
Now the time has come to make the change.
The only question is how much should the checksum formula be changed? Is it sufficient to just fix Bug#39474 or should we take the opportunity to do more?

• Is crc32 a good enough function for a checksum? Should we start using some other hash function?
• Should checksum change when table metadata changes? I.e. when you change the underlying data type? What about changing the table comment? Order of columns in the table?
• Any other issues we should address along the way?

Your input is much appreciated!

There are multiple architectures that can be used to achieve highly available database services, each differentiated by the levels of uptime they offer.  These architectures can be grouped into three main categories:
- Data Replication
- Clustered & Virtualized Systems
- Shared-Nothing, Geographically-Replicated Clusters

As illustrated in the figure below, each of these architectures offers progressively higher levels of uptime, which must be balanced against potentially greater levels of cost and complexity each incurs. 

Simply deploying a high availability architecture is not a guarantee of actually delivering HA.  In fact, a poorly implemented and maintained shared-nothing cluster could easily deliver lower levels of availability than a simple data replication solution.

 



To reduce risk and accelerate the evaluation process, …

Today, while helping someone find some of the code that implements SHOW VARIABLES I was reminded at how insanely complex it all got with the information_schema implementation and the changes to the SHOW commands that came with it. Specifically, I was trying to find exactly how the SHOW VARIABLES output gets populated — what the source data is.

By way of example, I present the code to implement it in 5.1.51 (the new way), and in 4.1.21 (the old way). I, at least, have a much easier time sorting out the old way. They both start with the same basic entry point, in the parser, in show: within sql/sql_yacc.yy.

SHOW VARIABLES in MySQL 5.1.51 sql/sql_yacc.yy

The show token just does some initialization.

10062 show:
10063           SHOW
10064           {
10065             LEX *lex=Lex;
10066             lex->wild=0;
10067             lex->lock_option= TL_READ;
10068             mysql_init_select(lex);
10069 …

During my visit to Moscow, Russia next month I’m going to give a full day training/tutorial presentation on Diagnosing and Fixing MySQL Performance Problems . This even will be based on updated and expanded tutorial from MySQL Conference & Expo which was so popular it was sold out earlier this year. This also will be the only full day training event open to the public during this trip. There are few conference talks, user meetings and private training engagements which I’m doing on this trip as well. I’ll blog about these later.

Entry posted by peter | No comment

Add to: …

Dear Community,

Percona Server version 5.1.50-rel11.4 is now available for download.

The changes in this release include:

New features

The primary purpose of this release is to update to the latest version of MySQL 5.1.

• Percona Server 5.1.50-rel11.4 is now based on MySQL 5.1.50.

Fixed bugs

• innodb_use_sys_stats_table – The SYS_STATS table is now used only for non-temporary tables.

The binary packages are available on our website.

Percona Server can be installed from either our  …

We’re gearing up to release v0.5 of GenieDB right now, and the biggest new feature in v0.5 is the generic indexing framework.

Originally, GenieDB provided indices on fields of records. As we do not mandate a per-table schema, each record in a table could have totally different fields; so when an index is created on the field “foo” of the table “bar”, we index any record in “bar” that has a “foo” field under that value in the index. If the record did not have a “foo” field, then it doesn’t appear in the index at all (whereas records with a NULL value for “foo” are indexed under NULL, of course).

However, expecting future interesting developments, we made sure this part of the code – figuring out how a given record should appear in a given index – would be easy to extend in future. In general, there could be any function from records to zero or more index entries.

For …

I have a problem, It's been months since I used MySQL and (I believe) I had set it up with a root password.  Now I can't log on to MySQL as root MySQL user and create a new user or manage an existing user (I can log onto server)...

Familiar situation. :)

Do so:

 service mysql stop

wait until MySQL shuts down. Then run

mysqld_safe --skip-grant-tables &

then you will be able to login as root with no password.

mysql -uroot mysql 

In MySQL command line prompt issue the following command:

UPDATE user SET password=PASSWORD("abcd") WHERE user="root"; 

FLUSH PRIVILEGES;

At this time your root password is reset to "abcd" and MySQL will now
know the privileges and you'll be able to login with your new password:

mysql -uroot …