Venu Anuganti Blog

Yesterday I got the new Transcend SSD Express Card with 32GB that fits nicely with the Macbook Pro express card slot.

Even though the write speed is very low (8MB/s) for any standard express card; but read speed seems to be 18MB/s; so decided opt for this as it can serve as ready boost to my Windows operating system that is running under Vmware Fusion along with storing all my music files. I needed one, so that I can move all virtual machine files to this card; but due to the slow write speed; its hard at this time; and I may need to wait till we get the true SSD performance on Express card.

One funny thing is; Macbook seems to eject the card few hours once automatically; which seems to be odd to me and search does reveal that lot of other people also has the same issue. Not sure if its a bug from Macbook or Transcend.

Anyway, I will wait for the true SSD speed on express cards.

Yesterday I was going through the locks related code; and found that in 5.0; when you have a global read lock; then the SHOW TABLE STATUS actually gets blocked when it had its own read lock on the new session; and this is not the case with MySQL 5.1

For example; lets execute the following set of statements in two sessions with MySQL 5.0 (latest bazaar version with InnoDB enabled):

Session 1:

1
2
3
4
5
6
7
8
9
10

mysql> use test;
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.07 sec)
 
mysql> create table test.t1(c1 int)Engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

Session 2:

1
2
3
4
5
6

mysql> use test
Database changed
mysql> lock table t1 read;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show table status like 't1'\G

And you will notice that the last SHOW TABLE STATUS command actually gets blocked until you release the lock in session 1 or rollback the transaction or until lock wait timeout.

And when you execute the same set of statements with 5.1; then it won’t get blocked; and that is supposed to be what one expects as read-lock(global or session) shouldn’t block read-only query from any session.

Here is the code snippet in MySQL 5.0; which is causing the problem as it internally calls ha_commit_trans on the statement, which internally gets blocked on global read lock when closing the tables (sql_base.cc:close_thread_tables) where as in 5.1; this is entirely taken care; and ha_commit_stmt is not actually called from this function at all..

1
2
3
4
5
6
7
8
9
10
11

if (thd->locked_tables || prelocked_mode)
{
/*
Let us commit transaction for statement. Since in 5.0 we only have
one statement transaction and don't allow several nested statement
transactions this call will do nothing if we are inside of stored
function or trigger (i.e. statement transaction is already active and
does not belong to statement for which we do close_thread_tables()).
TODO: This should be fixed in later releases.
*/
ha_commit_stmt(thd);

I am not sure if this is a bug or not; but does not harm anybody; other than if one has mysqldump running in –single-transaction mode; then if your application is using individual READ LOCKs and processing any SHOW commands;  then it may be a problem. And in case if you already have READ LOCKS; then FLUSH TABLES WITH READ LOCK will anyway block till you release it explicitly

Its been a while I wrote anything on MySQL and/or Database related things as I was on a long two month vacation and immediately had a job change.

Today I was playing a bit in trying to setup up the ZRM community backup on Cent OS 5 to have a incremental backup mechanism; to see how it works as I never had a chance to use it so far. In the process, I could not find a single source of document which explains the basic steps needed on first-hand setup (or my search did not yield the right docs), so thought of writing and noting it down myself for any future reference. More or less, the instructions should be same for any Linux favor.

Here are the steps:

• First install perl-DBI and perl-XML-Parser modules

  sudo yum install perl-DBI perl-XML-Parser

• Download and install ZRM package.

  sudo rpm -ivh MySQL-zrm-2.0-1.noarch.rpm 

  Password:

  warning: MySQL-zrm-2.0-1.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 3c5d1c92

  Preparing...                ########################################### [100%]

     1:MySQL-zrm              ########################################### [100%]

  Checking and Migrating old ZRM data

• Create user called `backup_user` identified by `backup_user` in MySQL

  mysql> GRANT ALL ON *.* TO 'backup_user'@localhost IDENTIFIED BY 'backup_user';

  Query OK, 0 rows affected (0.00 sec)

  mysql> flush privileges;

  Query OK, 0 rows affected (0.00 sec)

• Add a new directory under /etc/mysql-zrm/ as ‘dailyrun’ and copy the mysql-zrm.conf to this directory

  sudo mkdir -p /etc/mysql-zrm/dailyrun

  sudo cp /etc/mysql-zrm/mysql-zrm.conf /etc/mysql-zrm/dailyrun/

• Start editing the file /etc/mysql-zrm/dailyrun/mysql-zrm.conf and fill some of basic information and leave backup-level=1 and backup-mode=raw. The conf-file self-explains on every parameter, so it becomes easy for anyone to setup even for first time.

• Start testing the backup using mysql-zrm-scheduler by issuing:

  sudo mysql-zrm-scheduler --now --backup-set dailyrun 

• Once tested, add it to the standard daily scheduler to backup at 1 AM:

  mysql-zrm-scheduler --add --interval daily --start 01:00 --backup-set dailyrun

I am not going further to explain on how to verify the backup and restore it back, even though they are straight forward by using the ZRM utilities provided by the Zmanda; and complete information can be found from here.

I brought the new iPhone 3G the same day I came back from India as the 1st generation iPhone was handed over to my sister during the India visit.

After installing few new iPhone applications from AppStore; I noticed the phone seems to crash on and off and sometimes the response to keystrokes literally used to take ~10s also. And not to say; some times I can’t even end the call that easily.

Finally I called the iPhone support, and after explaining everything the customer representative told me that the 2.1 software addresses all the issues (which actually released on the same day). So, after updating with 2.1 (one need iTunes 8 inorder to work with 2.1 updated iPhone); everything seems to be positive and works like a charm.

At least I don’t see any issues that I had in the last week; and good thing is; 2.1 also looks like fixed the batter drain issue; lets see how long the batter will stay from now onwards.

Today is my last day @Yahoo!. It is my first ever short stay in any company that I worked so far, but I do not regret for leaving.

I decided to leave two months back but as I had a vacation in the middle (India trip), I have to postpone till am back. During my stay at Yahoo! I learned enough from rest of the MySQL geeks but I miss the whole geek community, which I doubt any company will have such a great MySQL folks.

Personally what I learned from Yahoo! is that, I needed to be in a company and/or team; where the management knows what the engineers actually do and capable of delivering rather than what was presented by managers itself from down the layers.

Anyway, I have multiple offers to continue as DB internals developer along with DB architect positions; but I will evaluate carefully in the next few days before I make up my mind as opposed to what I did in my last job search.

Its been a while I had any update on the web site. I came to India two weeks back and staying in Bangalore and having fun. Last time I visited in December, 2006 and from that time to now; the things are totally different and real estate prices are sky high; I regret not being an active investor on lands in the right time; if I was; then I had a chance to retire from my current job field.

After seeing the current situation, I decided to visit Bangalore for every 6 months to look into different directions on investment side in parallel by keeping my software job.

I know that this was talked a lot and recently Mark Callaghan also gave a session in MySQL user conference 2008 about the real bottlenecks.

Other day I was testing my thread pool stuff with MySQL 5.1.24 + InnoDB plugin 1.0.1 along with other miscellaneous benchmark tests by making them CPU bound by keep the working set completely in memory to gauge the performance of threads overhead; and on 8-core box InnoDB seems to be doing better than 4-core. And then immediately I started few tests with mysqlslap by keeping complete data set in the buffer pool to get the proper timing on locking overhead.

Here is the comparison of performance on 8-core box with innodb_thread_concurrency is set to 32 and 0 for variable threads on 64-bit Redhat Linux 4 . The same box is used as 4-core by limiting the cores.

[read more...]