Pedro's blog: db2, mysql, php, linux and performance

From the Giovani Collazo we get a git quick reference tip on how to use git everyday:

Everyday I use git on a local repository were I am working alone, under this scene there is no problem with overwritten others changes or using outdated code but, what if I am working with a repository were other coders are working too?

Here it comes the recipe:

• Checkout the master branch and pull of the most recent changes:
  

  $git checkout master
  $git pull

• Create a new branch to work on the new changes
  

  $git branch my-new-cool-branch
  $git checkout my-new-cool-branch

• Do your changes and afterwards you add and commit them
  

  $git status
  $git add .
  $git commit -a -m ‘Bug fix #1782 and new method getFullDescription’

• Now is when the magic comes, we are going to rebase with the master branch and then merge it
  

  $git checkout master
  $git pull
  $git checkout my-new-cool-branch
  $git rebase master

  If there is any conflict it will appear now! solve it in your branch and try again.

• The last step is to checkout with the master and merge our branch
  

  $git checkout master
  $git merge my-new-cool-branch

• Right now everything is fine so we can send our changes to github
  

  $git push

The source of the recipe is in spanish here:  http://elweb.co/como-uso-git-todos-los-dias/

Do you want to learn more about git? take a look to the entire Pro Git book, written by Scott Chacon and Ben Straub http://git-scm.com/book

The Respo wordpress theme is a cool theme for wordpress. It is free and it has a clean and simple design. After installing it you will find that the pictures of the posts in the blog ‘loop page’ are not working. To fix it I was researching a little in google and I found out a way to make it work for me:

1. Update the file wp-content/themes/respo/js/timthumb.php with the last version of the timthumb.php in the repository.
2. Comment the line 825:
   

   // $docRoot = @$_SERVER[‘DOCUMENT_ROOT’];

3. Check that every post has assigned is Featured Image.

After doing the 3 steps, the pictures are working in the blog ‘loop’s page’ and the problem with the respo timthumb.php script is fix!

Yesterday we had a surprise, something happen, take a look:

This is the IO Service time graph – by day, and you can see the difference around nine in the morning between yellow and orange color lines (sdb write and read  stats) and the blue and green color lines (sda drive write and read stats). The hard disk working as /dev/sdb failed around twenty past nine and we found out at seven pm.

The mdadm command said something about “faulty spare”, the output was (command: mdadm –misc –detail /dev/md1):

dev/md2:
Version : 0.90
Creation Time : Thu Dec 16 11:16:09 2010
Raid Level : raid1
Array Size : 1454122944 (1386.76 GiB 1489.02 GB)
Used Dev Size : 1454122944 (1386.76 GiB 1489.02 GB)
Raid Devices : 2
Total Devices : 2
Preferred Minor : 2
Persistence : Superblock is persistent
Update Time : Thu Apr 19 20:13:58 2012
State : clean, degraded
Active Devices : 1
Working Devices : 1
Failed Devices : 1
Spare Devices : 0
UUID : 36c6fe3e:6fbcc6a0:a4d2adc2:26fd5302
Events : 0.108754
Number   Major   Minor   RaidDevice State
0       8        2        0      active sync   /dev/sda2
1       0        0        1      removed
2       8       18        –      faulty spare /dev/sdb2

So, we opened a ticket to the hosting team and they take like 10 minutes to change the disk, awesome support! then, copying partitions and rebuilding raid1 array:

#sfdisk -d /dev/sda | sfdisk -f /dev/sdb

#mdadm /dev/md1 –manage –add /dev/sdb1
mdadm: added /dev/sdb1
# mdadm /dev/md2 –manage –add /dev/sdb2
mdadm: added /dev/sdb2
# mdadm –misc –detail /dev/md1 | grep sdb
1       8       17        1      active sync   /dev/sdb1
# mdadm –misc –detail /dev/md2 | grep sdb
2       8       18        1      spare rebuilding   /dev/sdb2

Syncing:
# cat /proc/mdstat
Personalities : [linear] [raid0] [raid1] [raid10] [raid6] [raid5] [raid4] [multipath] [faulty]
md1 : active raid1 sdb1[1] sda1[0]
10485696 blocks [2/2] [UU]
md2 : active raid1 sdb2[2] sda2[0]
1454122944 blocks [2/1] [U_]
[>………………..]  recovery =  0.1% (2138624/1454122944) finish=3913.2min speed=6183K/sec

And then swap!
# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/sda3                               partition       526236  0       -1
# mkswap /dev/sdb3
Setting up swapspace version 1, size = 538865 kB
# swapon -a
# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/sda3                               partition       526236  0       -1
/dev/sdb3                               partition       526236  0       -2
[root@ns24862 ~]# free
total       used       free     shared    buffers     cached
Mem:      12318872   12069548     249324          0     507552    9107340
-/+ buffers/cache:    2454656    9864216
Swap:      1052472          0    1052472

After some long time, everything working again like a charm!

After the CMS migration I commented about on the article MySQL Tuning Query Cache And Thread Cache, google had a couple of naps, the last one looked CRAZY on the stats! take a look yourself:

Does anybody need me to draw a circle around the difference? 🙂

Hello,

since we did the migration of a website from the php-nuke+phpbb to drupal+vbulletin, the cpu load of the server was getting around 20-30% more busy. I had to take a look to the mysql performance because it was using around the 20% of the CPU. First of all I activated the query cache on the /etc/my.cnf file:

query-cache-type = 1

And set the cache size to 20MB:

query-cache-size = 20M

After rebooting the MySQL service, the busy percentage decreased a bit. You can learn more about the query-cache on the MySQL Query Cache official website.

mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| have_query_cache | YES   |
+——————+——-+
1 row in set (0.00 sec)

The next step I took was to activate the thread cache:

mysql> SET GLOBAL thread_cache_size=40;
mysql> SELECT @@global.thread_cache_size;
+—————————-+
| @@global.thread_cache_size |
+—————————-+
| 40 |
+—————————-+
1 row in set (0.00 sec)

And include it on the /etc/my.cnf :

thread_cache_size=40

Since then, the MySQL server was caching every thread and every query and the cpu usage performance got reduced around 15%. While I was setting the new values I saw this one:

wait_timeout=600

And I reduce it to:

wait_timeout=10

Because there is no need for holding the connections 10 minutes, 10 seconds are more than enough. By now everything is working fine and you can see the load saving on the graph:

mysql cache optimization

Iconize is a cool CSS layout with a set of icons to iconize each kind of link inside your webpage. As I decided to use them in one of my personal projects I found out a little trouble with it because it didn’t work on the first try. Thinking on how http works I tried to find if the directory icons/ had some visibility from the internet and what i discovered? it worked but… wasn’t the icons folder of Iconize. WTF! was the icons folder of Apache!! so… working on Ubuntu:

grep -R icons /etc/apache2/*

And there it goes:

/etc/apache2/mods-enabled/alias.conf:#Alias /icons/ “/usr/share/apache2/icons/”

I added a comment on the line like:

#Alias /icons/ “/usr/share/apache2/icons/

And rebooted Apache:

/etc/init.d/apache2 restart

Problem solved, everything working.

Thank you pooliestudios.com people! Iconize is a great work!

Long time ago since last update… Busy time.

To improve my default db2 udb installation I switched form circular logging to disk logging, the steps:

#1#mkdir /db2backup

#2#mkdir /db2archive

#3#mkdir /db2archive/db2inst1

#4#chown db2inst1:db2admin /db2archive/db2inst1

#5#chown db2inst1:db2admin /db2backup

#6#db2 update db cfg for MYDB using logarchmeth1 disk:/db2archive

#7#db2 backup db MYDB to /db2backup

#8#db2 archive log for database MYDB

1,2,3: for storing backups and archive logs

4,5: setting owners

6: switching to db2 disk logging in /db2archive

7: after enabling log archiving, DB2 sets the database into the backup pending state to ensure that a full offline backup is taken before starting with log archiving so I backup. If the backup gives back an error like:

SQL1035N  The database is currently in use.  SQLSTATE=57019

You should:

#db2 list applications

#db2 force application all

8: to verify that the database is ready for log archiving

Now you can check your backups or the archive history

#db2 list history backup all for MYDB

#db2 list history archive log all for db MYDB

What a nice message code!

What does it mean? if we try the command:

db2 ? SQL0668N

It says:

QL0668N  Operation not allowed for reason code “<reason-code>” on table
“<table-name>”.

Explanation:

Access to table “<table-name>” is restricted. The cause is based on the
following reason codes “<reason-code>”:

1

The table is in the Set Integrity Pending No Access state. The
integrity of the table is not enforced and the content of the
table may be invalid. An operation on a parent table or an
underlying table that is not in the Set Integrity Pending No
Access state may also receive this error if a dependent table
is in the Set Integrity Pending No Access state.

Normally the same error message tell you what table has the problem, but, how can you know what is the table with that problem?

Try with the comand:

db2 “select TABSCHEMA, TABNAME from SYSCAT.TABLES where TYPE = ‘T’ and STATUS = ‘C'”

And you will get a list with all the tables with the SQL0668N rc=1 problem. How can you fix it? try with this comand:

db2 “SET INTEGRITY FOR TABSCHEMA.TABNAME IMMEDIATE CHECKED”

And everything is working again.

Hi again, I have to apologize because I’ve been in holidays and I didn’t write so often in last weeks.

On last week I had a problem with the codification in DB2 and the codification of the html layout.

The database is defined with this line:

db2 “CREATE DATABASE D2MU AUTOMATIC STORAGE YES ON ‘/home/db2inst1’ ALIAS D2MU USING CODESET ISO-8859-15 TERRITORY ES”

And the html layout includes this meta tag:

<meta http-equiv=”Content-Type” content=”text/html; charset=ISO-8859-15” />

So, if I disable the layout (with zend):

$this->_helper->viewRenderer->setNoRender();
$this->_helper->layout()->disableLayout();

The special chars in the text obtained from the database were not show in the proper codification, what I did for fixing it? I included a line in the HTTP headers like:

header (‘Content-type: text/html; charset=ISO-8859-15‘);

And the problem was solved!

What a surprise, just moved from MacOSX Leopard to Snow Leopard and what did I find?

Zend Development Studio doesn’t like the Java version already installed, the code is disappearing! the Java I had:

$ java -version
java version “1.6.0_17”
Java(TM) SE Runtime Environment (build 1.6.0_17-b04-248-10M3025)
Java HotSpot(TM) 64-Bit Server VM (build 14.3-b01-101, mixed mode)

I research a little and I found the fix: OS X 10.6 Snow Leopard Java problems (and fix)

At the end, everything working on 1.5:

$ java -version
java version “1.5.0_19”
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_19-b02-304)
Java HotSpot(TM) Client VM (build 1.5.0_19-137, mixed mode, sharing)

Enjoy it!

http://wiki.oneswarm.org/index.php/OS_X_10.6_Snow_Leopard