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

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?

Here I collected some useful scripts for helping you tuning MySQL:

• MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. MySQLTuner
• MySQL Performance Tuning Primer Script. This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
  to produce sane recomendations for tuning server variables. MySQL Performance Tuning Primer Script
• A Defragmentation Script to Optimize Fragmentated Tables in MySQL, finds your fragmented tables…and defragments them. A Defragmentation Script to Optimize Fragmentated Tables in MySQL

The first two will help you checking the MySQL most important performance values and giving you advice on how to modify them. The last one is really useful for finding every fragmented table and defragment it automatically.

Thanks to the authors!

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

Everything was working properly until one of the users add a summary artwork greater than the cast size in the xquery select.
Here the select i am using:

select A.ARCHIVO_ID,
F.ARCHIVO_FORMATO_DESCRIPCION_ES,
X.NOMBRE#,
X.RESUMEN#,
X.AUTOR#
from ARCHIVO A,
ARCHIVO_FORMATO F,
ARCHIVO_LOCALIZACION L,
ARCHIVO_TECNICA T,
ARCHIVO_CATEGORIA C,
ASIGNATURA_ARCHIVO AA,
ARCHIVO_BIBLIOGRAFIA B ,
xmltable(
‘$c/obra’ passing A.ARCHIVO_CARACTERISTICAS as “c” columns
NOMBRE# VARCHAR(100) path ‘nombre’,AUTOR# VARCHAR(100) path ‘autor’,
RESUMEN# VARCHAR(1000) path ‘resumen’
) as X
where A.ARCHIVO_ESTADO BETWEEN 1 AND 2 AND
A.ARCHIVO_PRIVADO = ‘N’ AND
…

The describe table:

db2 => describe table archivo

Data type Column
Column name schema Data type name Length Scale Nulls
——————————- ——— ——————- ———- —– ——
ARCHIVO_ID SYSIBM INTEGER 4 0 No
ARCHIVO_FORMATO SYSIBM INTEGER 4 0 No
ARCHIVO_TIPOLOGIA SYSIBM INTEGER 4 0 No
ARCHIVO_LOCALIZACION SYSIBM INTEGER 4 0 No
ARCHIVO_ESTADO SYSIBM INTEGER 4 0 No
ARCHIVO_VISITAS SYSIBM INTEGER 4 0 No
ARCHIVO_USUARIO_CREACION SYSIBM INTEGER 4 0 No
ARCHIVO_IP_CREACION SYSIBM VARCHAR 16 0 No
ARCHIVO_FECHA_CREACION SYSIBM TIMESTAMP 10 6 No
ARCHIVO_USUARIO_MODIFICACION SYSIBM INTEGER 4 0 Yes
ARCHIVO_IP_MODIFICACION SYSIBM VARCHAR 15 0 Yes
ARCHIVO_FECHA_MODIFICACION SYSIBM TIMESTAMP 10 6 Yes
ARCHIVO_PRIVADO SYSIBM VARCHAR 1 0 No
ARCHIVO_NOTA SYSIBM DOUBLE 8 0 Yes
ARCHIVO_CARACTERISTICAS SYSIBM XML 0 0 No
ARCHIVO_SOURCE SYSIBM BLOB 262144000 0 Yes

A XML file example:

<?xml version=”1.0″ encoding=”iso-8859-15″?>
<obra tipo=”Imagen”>
<nombre>Sin t&amp;iacute;tulo</nombre>
<autor>Vicente Ort&amp;iacute; Mateu</autor>
<periodo>Siglo XXI</periodo>
<archivo>42.jpg</archivo>
<resumen>escultura en m&amp;amp;aacute;rmol crema Valencia y madera, colecci&amp;amp;oacute;n particular</resumen>
<mime_type></mime_type>
<alto>2352</alto>
<ancho>2208</ancho>
<descriptores>
<descriptor></descriptor>
</descriptores>
</obra>

And here the error:

SQL16061N The value “El presente artículo constituye un inv…” cannot
be constructed as, or cast (using an implicit or explicit cast) to the data
type “VARCHAR_1000″. Error QName=err:FORG0001. SQLSTATE=10608

I had to change the cast from RESUMEN# VARCHAR(1000) to RESUMEN# VARCHAR(2000) because the select was failing and I was getting 0 results…

I just update the php of one of the servers I’m handling on and I got a surprise… every f*cking ereg() or eregi() function was displaying a pretty sentence:

Function ereg() is deprecated in…

How to disable this annoying warnings?
Fast solution:go to the php.ini file and add an ~E_DEPRECATED on the error_reporting var like:

error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED

Best solution:use preg_match() instead

Enjoy!

Something is going on DB2…

db2 “list tablespaces”

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0×0020
Detailed explanation:
Backup pending

Solving it:

db2 backup database YOURDB online to /theBackupDir/

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.