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


MySQL mysqldump tunning
31/12/2012, 12:32
Filed under: GNU/Linux,MySQL,Performance

Hello since April!
I am a little bit busy in the last months with some personal projects, bla bla bla…
Anyway, I had to improve the mysqldump command because the admin was getting some mails saying:
MySQL Error : MySQL server has gone away

The old command was:
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
The new command is:
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS --single-transaction --quick --lock-tables=false $db | $GZIP -9 > $FILE

The source of the tip is http://stackoverflow.com of course…



Linux RAID1 faulty spare and a dead hard disk
21/04/2012, 9:09
Filed under: General,GNU/Linux,Hardware | Tags: , , ,

Yesterday we had a surprise, something happen, take a look:Linux faulty spare raid1

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!



Google’s nap lets our server breath for a while
18/04/2012, 10:12
Filed under: Apache,General,GNU/Linux,MySQL,Performance | Tags: , , , ,

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:

Graph where you can see the difference between google indexing our site and google having a nap

Grap where you can see the difference on the CPU usage between google indexing yourself and google having a nap

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



MySQL tunner and MySQL Performance Tuning and MySQL fragmentation finder
14/04/2012, 11:13
Filed under: GNU/Linux,MySQL | Tags: , , , ,

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!



MySQL tuning query cache and thread cache
06/04/2012, 15:43
Filed under: General,GNU/Linux,MySQL | Tags: , , , , ,

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

mysql cache optimization



Function ereg() is deprecated in…
23/05/2011, 20:16
Filed under: Apache,GNU/Linux,PHP

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!



iconize and apache mod_alias
01/03/2011, 21:01
Filed under: Apache,General,GNU/Linux | Tags: , , , ,

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!



Protecting .svn folders with htaccess
09/04/2010, 21:01
Filed under: Apache,GNU/Linux,Security | Tags: , , ,

After configuring a subversion server for web projects you should take care about the .svn directories and files, ¿how may I secure them from the Internet savage? I found an easy way to do it with the .htaccess file:

RewriteEngine on
RewriteRule .*\.svn/.* – [F]

Don’t forget to enable mod_rewrite, and if you are already using some rule like

RewriteRule ^.*$

be sure of writing first the new rule:

RewriteRule .*\.svn/.* – [F]

RewriteRule ^.*$

Source: http://forum.webfaction.com/viewtopic.php?id=1069





phpMyAdmin and mod_security
15/03/2010, 21:08
Filed under: Apache,GNU/Linux,Security | Tags: , , , , ,

Sometimes a high level of security in a website can be painful. In my case, I lost some phpMyAdmin functionalities because the use of mod_security. The /var/log/httpd/modsec_debug.log said:

[15/Mar/2010:20:31:42 +0100] [thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][2] Warning. Pattern match “(?:\b(?:(?:s(?:elect\b(?:.{1,100}?\b(?:(?:length|count|top)\b.{1,100}?\bfrom|from\b.{1,100}?\bwhere)|.*?\b(?:d(?:ump\b.*\bfrom|ata_type)|(?:to_(?:numbe|cha)|inst)r))|p_(?:(?:addextendedpro|sqlexe)c|(?:oacreat|prepar)e|execute(?:sql)?|makewebtask)|ql_(? …” at ARGS:prev_sql_query. [file “/etc/httpd/modsecurity.d/modsecurity_crs_40_generic_attacks.conf”] [line “66”] [id “950001“] [msg “SQL Injection Attack“] [data “select * from `users`“] [severity “CRITICAL”] [tag “WEB_ATTACK/SQL_INJECTION”]


[15/Mar/2010:20:31:42 +0100] [
thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][2] Warning. Pattern match “(?:\b(?:(?:s(?:elect\b(?:.{1,100}?\b(?:(?:length|count|top)\b.{1,100}?\bfrom|from\b.{1,100}?\bwhere)|.*?\b(?:d(?:ump\b.*\bfrom|ata_type)|(?:to_(?:numbe|cha)|inst)r))|p_(?:(?:addextendedpro|sqlexe)c|(?:oacreat|prepar)e|execute(?:sql)?|makewebtask)|ql_(? …” at ARGS:sql_query. [file “/etc/httpd/modsecurity.d/modsecurity_crs_40_generic_attacks.conf”] [line “66”] [id “950001“] [msg “SQL Injection Attack“] [data “select * from `users` where“] [severity “CRITICAL”] [tag “WEB_ATTACK/SQL_INJECTION”]


[15/Mar/2010:20:31:42 +0100] [
thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][1] Access denied with code 400 (phase 2). Pattern match “\%(?!$|\W|[0-9a-fA-F]{2}|u[0-9a-fA-F]{4})” at ARGS:sql_query. [file “/etc/httpd/conf/modsecurity/modsecurity_crs_20_protocol_violations.conf”] [line “64”] [id “950107“] [msg “URL Encoding Abuse Attack Attempt”] [severity “WARNING”]

So, I had to add some local rules to the file /etc/httpd/modsecurity.d/modsecurity_localrules.conf

<LocationMatch “/phpMyAdmin/import.php”>
SecRuleRemoveById 950107
SecRuleRemoveById 950001
</LocationMatch>

I restarted the apache and everything is working!

Updated (16/03/2010):

I still had some troubles, so, I decided to disable mod_security totally on phpMyAdmin directory with the line:

SecRule REQUEST_URI ^/phpMyAdmin phase:1,allow,ctl:ruleEngine=off

In the file /etc/httpd/modsecurity.d/modsecurity_localrules.conf

Cheers!



DB2 export UTF8
25/02/2010, 18:54
Filed under: DB2,GNU/Linux,macosx | Tags: , , , , ,

Some tips and tricks.

Exporting data from DB2 to files in linux (I like EXPORT but you also can use UNLOAD or a simple SELECT and the redirection >):

$ db2 EXPORT TO “/tmp/unload.txt” OF del MODIFIED BY COLDEL, CHARDEL* SELECT * FROM MYTABLE

This command will export the table MYTABLE to the file /tmp/unload.txt formatting the fields like *COL1*,*COL    2*,*COL NUM 3*,…,*COL N*

In MacOSX formatting the files from UTF-8 format to ISO-8859-1 format (because the special spanish chars áéíóúñ…):

$ iconv -f UTF-8 -t ISO-8859-1 unload.txt > unload_8859.txt