optimmysql.blogspot.com
Optim MySQL: July 2007
http://optimmysql.blogspot.com/2007_07_01_archive.html
Sunday, July 22, 2007. OLAP Paradise - WITH ROLLUP. Though this is an age old concept, but after seeing the ignorance factor of many regarding this, I thought of writing a bit about it. ROLLUP is a GROUP BY modifier that adds extra rows to the output which contain summary data. At each level of aggregation the aggregated column is filled with a NULL value. Let's see a small example. I know the values are too small for the data presented, but just to keep it readable. Query OK, 0 rows. Http:/ www.yaho...
optimmysql.blogspot.com
Optim MySQL: November 2007
http://optimmysql.blogspot.com/2007_11_01_archive.html
Thursday, November 22, 2007. GET LOCK and FAMILY. Again I have a well neglected and documented feature of MySQL. As we all often need and use locks in MySQL, we tend to forget (or not bother about) MySQL internals and how they cause trouble. For example, try something like LOCK TABLE . WRITE on an InnoDB table in a transaction and see the same transaction getting timed out while waiting for a lock on one of the rows, Ref: [Bug 5998]. The secret is: use GET LOCK function of MySQL. GET LOCK(. This is one w...
optimmysql.blogspot.com
Optim MySQL: August 2007
http://optimmysql.blogspot.com/2007_08_01_archive.html
Wednesday, August 8, 2007. InnoDB Repeating AUTO INCREMENT Values. This is a continuation of my earlier post with the same title. Recently while solving a problem where values of an auto increment fields were getting repeated, a well documented and often neglected feature of InnoDB came forward. Lets first have a glimpse of the system and the problem. As I said, this is a well documented. If you have such a system, where you rely too much on the auto increment values from InnoDB, delete values frequently...
optimmysql.blogspot.com
Optim MySQL: June 2008
http://optimmysql.blogspot.com/2008_06_01_archive.html
Thursday, June 19, 2008. Temporary tables as seen by replication slave. Few days back, one of my colleagues posted a good question. It sounds something like this;. Temporary tables are session based that means under different sessions we can create temporary tables with similar names. Now since slave thread is singleton, how does it manage to keep them separate? Mysql SHOW BINLOG EVENTS IN. Log-bin.000016 389 Query 2515922453 488 use. Log-bin.000016 488 Query 2515922453 582 use. Test`; INSERT INTO. Instr...
optimmysql.blogspot.com
Optim MySQL: Variable's Day Out #16: innodb_log_file_size
http://optimmysql.blogspot.com/2008/08/variables-day-out-16-innodblogfilesize.html
Thursday, August 28, 2008. Variable's Day Out #16: innodb log file size. Innodb log file size= value. Integer: Range: 1M - 4G. 1M will be adjusted to 1M. This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than 4GB. InnoDB requires these logs for recovery in case of a crash. So how come the size of these logs effect server performance? If you just change the size of this variable, MySQL will crib...
optimmysql.blogspot.com
Optim MySQL: September 2007
http://optimmysql.blogspot.com/2007_09_01_archive.html
Wednesday, September 12, 2007. Customized Order By Sequence - A Small Hack. What if you have to customize the sequence of order by clause on a column. Say, you have a integer column. That can have values ranging from 1 to 5 and you want to get the rows in the sequence 4,2,5,1,3. So what do you do? Here is a simple trick using the. FIELD(weight, 4, 2, 5, 1, 3);. Other approach can be to use CASE. WHEN . END statement as mentioned in the MySQL Documentation. The above code can be rewritten as:. Well, InnoD...
optimmysql.blogspot.com
Optim MySQL: August 2008
http://optimmysql.blogspot.com/2008_08_01_archive.html
Thursday, August 28, 2008. Variable's Day Out #16: innodb log file size. Innodb log file size= value. Integer: Range: 1M - 4G. 1M will be adjusted to 1M. This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than 4GB. InnoDB requires these logs for recovery in case of a crash. So how come the size of these logs effect server performance? If you just change the size of this variable, MySQL will crib...
optimmysql.blogspot.com
Optim MySQL: March 2008
http://optimmysql.blogspot.com/2008_03_01_archive.html
Saturday, March 29, 2008. Variables Day Out #3: max connect errors. Range: 1 - 4294967295. This variable determines how many interrupted connections can occur from a host. If the number of interrupted connections from this host surpasses this number, that host is blocked from further connections. All of the following, as listed here. Will cause the counter to be incremented. Client program did not call mysql close(). Client had been sleeping more than wait timeout. Client uses incorrect password. I gener...
optimmysql.blogspot.com
Optim MySQL: September 2008
http://optimmysql.blogspot.com/2008_09_01_archive.html
Sunday, September 7, 2008. This week I'm changing gears of my life. On Friday (5th September), I yodeled and bled purple for one last time. It has been 2 years and 8 months when I first joined Yahoo! Directly from college during which the size of Yahoo! Here at Bangalore, has almost tripled. Definitely the most rewarding and learning time for me as I worked and interacted with some really cool, supportive and intelligent people. Starting Monday I'm joining mKhoj. While enjoying at mKhoj. Sort tags uniq -c.
optimmysql.blogspot.com
Optim MySQL: Translation is Fun!!
http://optimmysql.blogspot.com/2008/08/translation-is-fun.html
Tuesday, August 12, 2008. Morning, I saw Monty's post. Asking for contribution to drizzle's i18n efforts. I did checked out Hindi language and well I must say translation is a fun activity. If you think that will be as easy as using some online translation tool (I tried Google Translate. As of now I'm doing Hindi (already 80 translations down) and next I'm gonna pick Punjabi. Wow! Posted by Parvesh Garg. Bangalore, Karnataka, India. I lead the core ad serving group at mKhoj Solutions. Sort tags uniq -c.
