Grief

We encounter grief at various stages in life. It could be personal loss,disappointment or loss of a beloved one. When at each stage we encounter grief it changes a part of us. However small cause may be it changes I guess at least one neuron connection. I have had numerous such encounters. The most significant is the one when I lost my grandfather. Today I lost a second one and just watching him lie there still evoked the trauma that I underwent when I lost my first grandfather. At some moments I could see him lie there and not my second grandfather. It was in way creepy. But in few moments I realized it was the sign of how much I missed him and the size of his influence on me. I realize this will also change me a bit. Teaching me something that I would realize at a later stage. Grief changes and teaches.

xtrabackup incremental backups

Tags

broken pipe, bug, incremental, innobackupex, mysql, percona, xtrabackup

We wanted to optimize and test backups for one of our new large scale setup before we could finalize on the backup plan. Our challenge was the data volume was almost 30x more than our normal volumes. Since this involved large volume of data we thought this might be a good candidate to test incremental backups. We wrote a wrapper script to save the states between full backups and incremental backups and did some tests with smaller data sets. It worked perfectly fine. The version of xtrabackup we were testing was 1.6.2.

The incremental backups were completing well within 30minutes. We set out to test what would happen if the amount of incremental diff was large. While doing this test, we started getting backup failures with the following error

20110726_225312.log-110727 00:56:10 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
20110726_225312.log-innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
20110726_225312.log-innobackupex: subdirectories of ‘/var/lib/mysql’
20110726_225312.log:innobackupex: Error: Broken pipe at /var/backups/xtrabackup/bin/innobackupex line 336.
20110726_225312.log-Backup failed.
20110726_225312.log-Deleting bad data directory…
20110726_225312.log-Done. Removed /ddmysql/backups/20110726_225312

Initially we thought this was a one of case and made quite a few reruns. It was failing consistently. We wanted to get to the bottom of this. This was important for us to finalize the backup plan. We ran the innobackupex script through a debugger to find out where it was failing. The broken pipe error was getting triggered when the script was trying to send a ping to the mysql server so that the connection doesn’t timeout after the number of seconds mentioned in “mysql_keep_alive_timeout“  variable. This was set to around 1800. Our conclusion was when the incremental was of smaller size, the script never got to send the timeout ping. We reran the script with a much larger timeout value for a larger incremental data set and the backup was completed successfully.

The script seems to lose the connection handle during  course of execution and when it tries to ping on the handle it encounters a broken pipe. Wondering if there are other tried and tested ways to overcome this issue. Bug has been logged with the xtrabackup team to see if there are other options.

Dreams

Tags

care, dream, education, gingham, give, help, need, project, school, support

This is a phenomenon that is also applicable to our government schools in Tamilnadu. In our village  schools it is considered great if 5% of the students move up into high school from middle school. The world of these kids are so small that they are intimidated by thought of dreaming big. If they cross that chasm and dream big, they are faced with numerous hurdles that they eventually drop off somewhere along our system.  The Gingham team has got together to do our bit to expand the horizon of the kids to dream big and to urge the kids who have a dream to follow through. Please visit our website that we have setup for project "Gingham"  and let us know your thoughts. Even your smallest act of support by sharing/contributing/giving feedback will help a deserving kid to make his/her dream come true. Please help.

P.S -  This video was not created by our team but captures the essence so well.

Life Matters

Tags

cuddalore, government, gyan, important, life, need, orphanage, poor, school, support

The number of “Aha!” moments that I have experienced in life are not that many. And I tend to forget those moments in course of time. I would like to narrate one such moment which I don’t want to forget and will not forget. Of late I have been conscious of the fact that life is passing by at break neck speed. Weeks and months whizz by in a cycle.

As we enter each week we wait expectantly for the weekend and while we laze away the weekend, we start waiting expectantly for the next weekend. During these cycles we spend enormous amounts of time, energy and money to better our lives, our families and friends. We seem to add so much value to our organizations, our teams, our families. We rate ourselves so high that we even think that we are indispensible to the company, team or project during our moments of self pride and honor. We always keep thinking about the next big thing that is going to happen in our life. We wait expectantly gathering our resources while we pass away our current successes as if they are insignificant. We never reach a state where something is enough. There is always something better to pursue for in life. I totally agree that there is nothing wrong in having better expectations and goals. Today’s success is defined by status, money, power, position, fame and acclaim. That’s the mantra of modern day society. Anything that doesn’t matter to the modern day society is laid to rest by the wayside.

The moment made me reaffirm that there is more to life than the success criteria’s that we define for ourselves. Recently me and my friends visited few governments schools and homes in Cuddalore as part of a study. We were accompanied by one of my friend’s father to these places. In Cuddalore there is a home that is being run by the government just for girls who are orphans and whose parents are incapable of taking care of them. My friend’s father is a retried Head Master of one of the government schools. We reached the Girls home in a cab and he swiftly got out of his car being all excited to meet the kids. And what unfolded after that was totally out of the world experience for me. I could feel so much happiness and satisfaction by just watching the scene. The Girls were getting ready for their evening study and the moment they say him all hell broke loose. They all ran towards him, surrounded him , engulfed him showering with greetings and enquires about how he is doing and the smaller ones asking why he left. Words cannot describe the scene. The joy and the jumping is inexplicable.  The greetings and happiness was so blissful and warmth that I didn’t want the evening to end. Some even went to the extent of requesting him to come back. There were so happy to see their beloved teacher back in their hut even if it was momentary. This might sound like a cliché but we shall never understand the depth of it unless we experience it first hand. While coming back, I kept replaying the whole scene again and again in my mind. He has been their source of guidance and hope. He has touched the lives of not just his family / friends. He has inspired many children to greater levels. He doesn’t limit his circle of impact to only to people who can reciprocate. He has extended his knowledge to anyone and everyone He has crossed paths with. In His world the criteria of success is “Life Matters” , but not his. It is the Life of each and every kid that He comes in touch with is what matters. Even after His retirement, he doesn’t want to stop. Now the only question I keep asking myself is “What is my sphere of influence”.

Your compass is YOU

Tags

choice, compass, decision, direction, nagging feelings, personal

We always have difficulty in making choices. There is always pressure on us deciding between 2 things (read anything)  even when the differences between the choices aren’t  that much. The problem of choice and its associated decisions can make us all stressed out and worrisome. The same can be said for feelings that are nagging at the back of our mind about something done to us or something that your friend forgot to do and ignored or something that was not done to your liking. I have always struggled with both, choices and nagging feelings. Our first instinct is always confrontation . In the case of choices we always confront ourselves with the choices and try to evaluate and decide which way to go. In the case of nagging feelings we might try to confront or blurt with the other person / persons involved about what we think or feel. It is always dangerous to do something when we are not sure what we want or what we actually feel. Forever I have struggled with these dilemmas. One of my friend whom I met recently inspired me to an idea that has worked so well for me in the past few months. It is very simple but very handy.

 

“Observe your feelings about the choices or events over a period of time”

 

Let me explain. We can break this down into simple 4 steps :

• We have to have an item to evaluate or think over. It can be a choice , event or an experience.
• Define what a favorable outcome will look like wrt to that choice/event/experience.
• Give it some time, list the feelings and thoughts that you have about your point of contention over a period of time , say how do you feel about it in 2 weeks, how you feel in a months time based on available time.
• Eventually by the end of your slotted time your feelings would have started to kick in enabling you to make a move in the right direction.

Decisions or choices made impulsively might not work in our best interests in the long run. The above approach has helped me decide upon during my numerous outrageous moments of dilemma. Let me know what works for you in your moments of choice.

Peace out !

running mysql with “memlock”

The documentation says that we need to run mysql as “root” user for it to be effective. The code also indicates the same thing where it tries to do a getuid call to check if the server is running as root user (sql/mysqld.cc:  if (locked_in_memory && !getuid())). The general rule of thumb is that we don’t want to run mysql or rather any database as root user since that leads to vulnerabilities.

Historically memlock seems to cause issues on some OS flavors where “mlockall” implementation is not stable. This is documented in mysql code with the below warning message.

The "–memlock" argument, which was enabled, uses system calls that are

unreliable and unstable on some operating systems and operating-system

versions (notably, some versions of Linux).  This crash could be due to use

of those buggy OS calls.  You should consider whether you really need the

"–memlock" parameter and/or consult the OS distributer about "mlockall"

bugs

On machines where only the database is hosted, if the memory sizing and parameters are set  optimally we might never want to use memlock.  It might be effective  to use “memlock” in machines where the database co-exists with  the application code and possibility of the database memory buffers getting swapped out is very high. Even on optimally sized standalone database machines due to various reasons (Jeremy has a nice blog post on this), there are chances of the database memory buffers getting swapped.

“memlock” seems to be a good option to pin the database in memory. But given the caveat that the servers needs to run as root user makes this option not usable in production since we don’t want to open up to vulnerabilities. Is there a workaround to use “memlock” in production or rather what would be the recommended way of using it.  

mount: /dev/sdb1 already mounted or /mysql busy

Tags

disk, mount, troubleshooting

We added a 500GB 7.2K SATA/300 Hitachi Deskstar E7K500 16MB disk to one of our dev servers and partitioned using fdisk and formatted the partition with etx3. When we tried mounting the same, we got the following error :

[root@xyz user]# mount -t ext3  /dev/sdb1 /mysql
mount: /dev/sdb1 already mounted or /mysql busy

lsof didn’t provide any open files that might be linked to this problem or there was any “famd” running. Finally doing the following steps to remove the logical devices from the device-mapper driver helped us fix the problem.

[root@xyz user]# dmsetup ls
ddf1_44656c6c202020201028001510281f033832b7a2f6678dab   (253, 0)
ddf1_44656c6c202020201028001510281f033832b7a2f6678dab1  (253, 1)

[root@xyz user]# dmsetup remove ddf1_44656c6c202020201028001510281f033832b7a2f6678dab1
[root@xyz user]# dmsetup ls
ddf1_44656c6c202020201028001510281f033832b7a2f6678dab   (253, 0)

[root@xyz user]# dmsetup remove ddf1_44656c6c202020201028001510281f033832b7a2f6678dab

[root@xyz user]# dmsetup ls
No devices found

Mounting using the command “mount -t ext3  /dev/sdb1 /mysql”  after the above steps worked fine.

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 41 bytes) in /usr/share/cacti/site/lib/xml.php on line 32

Tags

apache2, cacti, php, templates

I was trying to setup cacti on my ubuntu desktop to gather metrics from few new database servers we were planning to replace. Installed cacti using the following command

apt-get install cacti

Then the next step was to download and the install the cacti mysql templates from http://code.google.com/p/mysql-cacti-templates/. Thanks to Baron, was able to get this up and running fast using the templates. But had one problem though, while importing the mysql template “cacti_host_template_x_mysql_server_ht_0.8.6i-sver1.1.7.xml” cacti bombed with the below error

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 41 bytes) in /usr/share/cacti/site/lib/xml.php on line 32

The fix was to edit “/etc/php5/apache2/php.ini” change the parameter “memory_limit” from 16M to 32M and restart apache using the command “/etc/init.d/apache2 restart“. Now cacti was able to import the template without any issues.

While configuring cacti, by mistake selected apache instead of apache2 during the configuration and was not able to access the cacti ui. Ran the following command to reconfigure cacti.

dpkg-reconfigure cacti

Lock wait timeout on slaves

Tags

innodb, lock wait timeout, mysql, performance, query tuning, slave

We had a one of our slave servers frequently stop replicating with the “Innodb Lock Wait Timeout” error. The slave IO thread would continue to fetch the binlogs while the slave SQL thread kept stopping with  the above mentioned error. The teams initial inclination was to change the innodb lock wait timeout variable from 50 secs to a higher value. It was a read-only slave. Our expectation was there would be no competing writes. Then we started listing what are the next steps possible and what could be wrong.

1. There could be a user with “super” privilege in the system that was running updates directly on the slave
2. A backup script that could be locking the tables out for backup
3. Increase the “innodb lock wait timeout variable“
4. Enable the innodb lock monitor

While we were working on the system, we noticed that there were few select queries that were running for 300+ secs of the following format.

SELECT * FROM s WHERE (s.id in (select s_id from i where o_id = 79168 AND ( u_id = 79168 OR u_id = 0 )))  ORDER BY s.id DESC limit 15

We wanted to see what was innodb upto since the tables in question were innodb. The “show engine innodb status”  showed us something really interesting.

—TRANSACTION 37ECF6, ACTIVE 1 sec, process no 17368, OS thread id 1263749456 fetching rows, thread declared inside InnoDB 481
mysql tables in use 2, locked 1
2242 lock struct(s), heap size 309232, 883550 row lock(s)
MySQL thread id 1948263, query id 17739683 localhost root preparing
SELECT * FROM s WHERE (s.id in (select s_id from i where o_id = 79168 AND ( u_id = 79168 OR u_id = 0 )))  ORDER BY s.id DESC limit 15

It was a select statement with sub query. The oddity comes from “mysql tables in use 2, locked 1“. Why does a select statement need to lock a particular table ? We replaced that particular query the with below one helped us alleviate the locking issue.

SELECT * FROM  s, i WHERE s.id=i.s_id and i.o_id = 79168 and (i.u_id = 79168 OR i.u_id=0) ORDER BY s.id DESC limit 15

Innodb status output for this query :

—TRANSACTION 37ECF7, ACTIVE 5 sec, process no 17368, OS thread id 1263749456 fetching rows, thread declared inside InnoDB 352
mysql tables in use 2, locked 0
MySQL thread id 1948263, query id 17739687 localhost root Sending data
SELECT * FROM  s, i WHERE s.id=i.s_id and i.o_id = 79168 and (i.u_id = 79168 OR i.u_id=0) ORDER BY s.id DESC limit 15

Once the query was replaced and an index was added to o_id column, we were back to sanity.

change accelerator cache ratio

Tags

accelerator, cache, controller, HP DL180, hpacucli, mysql

I was given the task of checking the array accelerator cache ratio and see if it was set to optimal levels. Our ideal preference was a read/write ratio of 0/100.

The machine configuration is HP DL180 G5, 2 x Xeon L5420 2.50GHz, 15.7GB / 16GB 667MHz DDR2, 6 x 300GB-15K SAS.This machine was running mysql 5.1.36 using the innodb plugin.

The command line utility to check the controller configuration is “hpacucli”. Navigating using hpacucli is very straight forward.

“ctrl all show config detail” Will give you the entire controller configuration.

=> ctrl all show config detail

Smart Array P400 in Slot 5
Bus Interface: PCI
Slot: 5
Serial Number: P61630K9SW31NL
Cache Serial Number: PA82C0J9SW02H1
RAID 6 (ADG) Status: Enabled
Controller Status: OK
Chassis Slot:
Hardware Revision: Rev D
Firmware Version: 4.12
Rebuild Priority: Medium
Expand Priority: Medium
Surface Scan Delay: 15 secs
Post Prompt Timeout: 0 secs
Cache Board Present: True
Cache Status: OK
Accelerator Ratio: 0% Read / 100% Write
Drive Write Cache: Disabled
Total Cache Size: 256 MB
Battery Pack Count: 1
Battery Status: OK
SATA NCQ Supported: True

Array: A
Interface Type: SAS
Unused Space: 0 MB
Status: OK

Logical Drive: 1
Size: 1.4 TB
Fault Tolerance: RAID 5
Heads: 255
Sectors Per Track: 32
Cylinders: 65535
Stripe Size: 64 KB
Status: OK
Array Accelerator: Enabled
Parity Initialization Status: Initialization Completed
Unique Identifier: 600508B100104B39535733314E4C0003
Disk Name: /dev/cciss/c0d0
Mount Points: / 3.9 GB, none 12.0 GB, /var 3.9 GB, /tmp 3.9 GB, /home 1.3 TB
Logical Drive Label: A0432BCEP61630K9SW31NLD55F

physicaldrive 1I:1:1
Port: 1I
Box: 1
Bay: 1
Status: OK
Drive Type: Data Drive
Interface Type: SAS
Size: 300 GB
Rotational Speed: 15000
Firmware Revision: 0005
Serial Number: 3QP1BEP400009004UQCD
Model: SEAGATE ST3300656SS
PHY Count: 2
PHY Transfer Rate: 3.0GBPS, Unknown
physicaldrive 1I:1:2
Port: 1I
Box: 1
Bay: 2
Status: OK
Drive Type: Data Drive
Interface Type: SAS
Size: 300 GB
Rotational Speed: 15000
Firmware Revision: 0005
Serial Number: 3QP1ZZRN000090035Q2Q
Model: SEAGATE ST3300656SS
PHY Count: 2
PHY Transfer Rate: 3.0GBPS, Unknown
physicaldrive 1I:1:3
Port: 1I
Box: 1
Bay: 3
Status: OK
Drive Type: Data Drive
Interface Type: SAS
Size: 300 GB
Rotational Speed: 15000
Firmware Revision: 0005
Serial Number: 3QP20VCQ00009004XE2V
Model: SEAGATE ST3300656SS
PHY Count: 2
PHY Transfer Rate: 3.0GBPS, Unknown
physicaldrive 1I:1:4
Port: 1I
Box: 1
Bay: 4
Status: OK
Drive Type: Data Drive
Interface Type: SAS
Size: 300 GB
Rotational Speed: 15000
Firmware Revision: 0005
Serial Number: 3QP1ZZSB00009003MMWZ
Model: SEAGATE ST3300656SS
PHY Count: 2
PHY Transfer Rate: 3.0GBPS, Unknown
physicaldrive 1I:1:5
Port: 1I
Box: 1
Bay: 5
Status: OK
Drive Type: Data Drive
Interface Type: SAS
Size: 300 GB
Rotational Speed: 15000
Firmware Revision: 0005
Serial Number: 3QP1L4T000009004UQCV
Model: SEAGATE ST3300656SS
PHY Count: 2
PHY Transfer Rate: 3.0GBPS, Unknown
physicaldrive 1I:1:6
Port: 1I
Box: 1
Bay: 6
Status: OK
Drive Type: Data Drive
Interface Type: SAS
Size: 300 GB
Rotational Speed: 15000
Firmware Revision: 0005
Serial Number: 3QP196KG00009004S0ZH
Model: SEAGATE ST3300656SS
PHY Count: 2
PHY Transfer Rate: 3.0GBPS, Unknown

In the above output our point of interest was “Accelerator Ratio: 0% Read / 100% Write“. In this case it has been set to an optimal value. In case it wasn’t set to an optimal value it can be changed using the command “ctrl slot=5 modify cacheratio=0/100“

When you are stuck for a particular command, you can just run “help <command name>” for more input.

=> help aa

The following documentation pertains to your search:

<target> modify [arrayaccelerator=enable|disable]
Enables or disables the array accelerator for a given logical drive. The
target can be any valid logical drive target on a controller that supports
array accelerator management.

<target> modify [cacheratio=#/#|?]
Sets the array accelerator cache ratio for the controller. The first # is
the read cache %. The second # is the write cache %. The target can be any
valid controller.

<target> create [type=ld]
[drives=[#:]#:#,[#:]#:#,[#:]#:#-[#:]#:#],…|all|allunassigned]
[raid=6|5|1+0|1|0|?]
[size=#|?]
[stripesize=8|16|32|64|128|256|default|?]
[sectors=32|63|?]
[arrayaccelerator=enable|disable|?]
[drivetype=sas|satalogical|sata|saslogical|parallelscsi|?]

[type=] The type parameter specifies the device type that is being created.
A logical drive is the only device type supported at this time.

[drives=] The drives parameter specifies the physical drives to be used for
creating a logical drive on a new or existing array. If the drives specified
are all unassigned drives, then a new array will be created with a new
logical drive on it. If the drives specified are all assigned to an existing
array, then a new logical drive will be created on that array. The symbol
#:# stands for port:id or box:bay, depending on the controller. Some
controllers may also support port:box:bay and use the #:#:# syntax. The all
and allunassigned keywords both target all physical drives that are not
currently assigned to an array.

[raid=] The raid parameter sets the raid level of the logical drive. If not
specified, the default raid is the highest level possible. The availability
of certain raid settings depends on the number of drives designated in the
“drives=” parameter. For example, RAID 1 will only be available if two
drives are selected while RAID 1+0 will be shown for a selection of 4 or
more drives.

[size=] The size parameter specifies the size of the logical drive, the
implied units are MB. If not specified, the default is the maximum possible
size.

[stripesize=] The stripesize parameter sets the logical drive’s stripesize.
The implied units of stripe size are KB.

[sectors=] The sectors parameter specifies the sectors per track of the
logical drive. If not specified, the default is 32.

[arrayaccelerator=] The arrayaccelerator parameter specifies the array
accelerator state for the logical drive. If not specified, the default is
enable.

[drivetype=] The drivetype parameter specifies the drive
interface type. If there are multiple drive types when selecting all
physical drives, the desired drive type needs to be specified. Mixed drives
are not allowed on the same array or logical drive. If all drive types in a
controller are the same this parameter is not needed. The target can be a
controller or an array in the system.

Examples:
controller slot=3 logicaldrive 2 modify arrayaccelerator=enable
controller slot=1 modify cacheratio=25/75
ctrl slot=1 create type=ld drives=1:1,1:2,1:3,1:5 raid=6
ctrl slot=1 create type=ld drives=1:1-1:6,1:9,1:10-1:12 raid=6
ctrl slot=1 create type=ld drives=all drivetype=parallelscsi
controller slot=5 array A create type=ld raid=5 size=1000
controller slot=1 array C create type=ld raid=1 stripesize=32
ctrl slot=1 create type=ld drives=1:1,1:2,1:3,1:5 raid=?
ctrl slot=1 create type=ld drives=1:1,1:2,1:3,1:5 stripesize=?
ctrl slot=1 create type=ld drives=1:1,1:2,1:3,1:5 raid=1+0 stripesize=?
ctrl slot=1 create type=ld drives=1:1,1:2 raid=1 size=?
ctrl slot=1 create type=ld drives=1:1,1:2,1:3,1:5 raid=1+0 sectors=?