Last week, I pointed you to a paper I recently wrote on how to use InfiniDB with Microsoft Excel. Of course, I realize there are plenty of people who don’t use Microsoft office tools and use OpenOffice instead, so I’ve just posted another paper on how to use InfiniDB with OpenOffice Calc (OO’s complement of Excel) and Base (OO’s twin of MS Access). FWIW, I found OpenOffice easier and more straightforward to use with InfiniDB and other RDBMS's than Microsoft (and I'm prRead More...

Oracle recently announcent MySQL-5.5.2-m2 milestone, and I have a predition that MySQL-5.5 will be announced as GA on MySQL UC 2010 in April.

So let's make quick on scalability characteristics we should expect from new release.

I made sysbench oltp benchmarks on 10 mln rows (worth 2.5GB of data), on our Dell R900 system ( 16 cores, 32GB of RAM, FusionIO + RAID10, but disk in this case does not matter).

There is results for various threads (results are in tps, more is better)

I think it is good results, and there couple conclusions we can come with

• InnoDB now can scale up to 16 threads pretty well
• InnoDB can keep load up to 128 threads, and for 256 threads the result is …

I have not caused a fist fight in a while, so it's time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL's key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and a host of smaller ones. If you make some assumptions that are very hard to prove, there actually is a very limited use for the statistics from which the ratio is derived (but not the ratio itself, which you should ignore). Read on for the details.

In this article, I will use key buffer, key_buffer, and key cache interchangeably. However, I will be careful about the difference between "rate" and "ratio". In this article, the key cache miss rate is defined as the number of misses per unit of time, with the units of operations per second. The key cache miss ratio is the ratio between reads from the disk and reads from the cache; it is a dimensionless …

In this article I’m showing how to retrieve result based on today, week and month using mysql query. To learn the techniques just visit http://thinkdiff.net/mysql/getting-rank-today-this-week-and-this-month/

In my previous article I’ve shown how to get rank using mysql query. Now I’m showing how to retrieve result based on today, week and month using mysql query. Actually I also implemented this in my quiz project so I’m sharing this with you.

For the table structure please look my previous article http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

Task 1: I’ve to retrieve those users rank who played the game today.
Solution: Look at the query

SELECT uid, participated, correct, wrong from quiz_user
    WHERE DAYOFMONTH(CURDATE())=extract(day from updated)
    ORDER BY correct DESC, participated ASC
    limit 30

So the above query returns the result of those users who played today. Here

CURDATE() …

Happy birthday LinuxJedi!

I would like to say a happy birthday to one of my colleagues and friends in the MySQL Support Team LinuxJedi (also known as Andrew Hutchings) who have the pleasure to enjoy his 30 years birthday today.

Many things can be said about Mr LinuxJedi. One thing I have learned is that even if snow can stop everything in England, nothing can stop him.

Yesterday I took this picture in my garden...

...and now I am not surprised it is melting here.

When I stopped by England in May for some training together with LinuxJedi I saw this sign...

…

We have at least four birthdays here at SmugMug.  I started working on the first lines of code for the project, which was decidedly not a photo-sharing site at the time, in October of 2001.  Then in August of 2002, we decided to shift gears and build a photo-sharing site “as a side business.”  Finally, we launched SmugMug and signed up our first paying customers in November of 2002.

But as far as the government is concerned, February 27th, 2002, is the day we were born.  It’s our official incorporation date and probably marks the day I finally wore my father’s resolve down enough that he agreed to come on board.  :)  (Which, incidentally, is also the best decision I’ve made running this company).

So Happy Birthday SmugMug!  You’re 8!

Here’s to many more profitable, independent, customer- and employee-focused years!

…

In this article you’ll learn how to get user’s rank in mysql query. Check the link http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query

Some days ago I was working in a quiz project. Where user will play quiz and for each correct answer they will earn points. One of the task of this quiz application was, to get rank of a particular user. Now I am showing how could I solve this problem using mysql query.

Here is the table structure:

CREATE  TABLE IF NOT EXISTS `quiz_user` (
`uid` BIGINT UNSIGNED NOT NULL ,
`participated` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`correct` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`wrong` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`created` DATETIME NULL ,
`updated` DATETIME NULL ,
PRIMARY KEY (`uid`) )
ENGINE = InnoDB

This table contains user records of quiz game. Here is an examples:

There are set of commands that MySQL itself interprets. You may use “help” or “\h” at the mysql> prompt to list them. Below are the 5 most useful MySQL Command…

The post 5 useful MySQL Command Options-pager-prompt-rehash-tee-system first appeared on Change Is Inevitable.