Faulty Physical Ram

Some times it's very difficult to find out exact issue. Specially When it's related with hardware. Similar scenario, i faced with a client. I have been provided with a new box to setup mysql server. After setup mysql along with other application, mysql frequently goes down without any comment in mysql error log file. Spending few days verifying os, logs, mysql and later i found the culprit using memtester tool. Thanks to memtester tool.

[root@voice ~]# memtester 5 1
memtester version 4.1.2 (32-bit)
Copyright (C) 2009 Charles Cazabon.
Licensed under the GNU General Public License version 2 (only).

pagesize is 4096
pagesizemask is 0xfffff000
want 5MB (5242880 bytes)
got  5MB (5242880 bytes), trying mlock ...locked.
Loop 1/1:
Stuck Address       : ok      
Random Value        : ok
Compare XOR         : ok
Compare SUB         : ok
FAILURE: 0x7888cfc4 != 0xe088cfc4 at offset 0x00039fba.
Compare MUL         : FAILURE: 0x00000001 != 0x00000002 at offset 0x00039fba.
Compare DIV         : FAILURE: 0x7fff9d53 != 0x7fff9d52 at offset 0x00039fba.
Compare OR          : FAILURE: 0x77fb0403 != 0x77fb0402 at offset 0x00039fba.
Compare AND         :   Sequential Increment: ok
Solid Bits          : ok      
Block Sequential    : ok      
Checkerboard        : ok      
Bit Spread          : ok      
Bit Flip            : ok      
Walking Ones        : ok      
Walking Zeroes      : ok      

Done.
[root@voice ~]#

After replacing faulty ram with a new one. Things looks good and working fine.

[root@voice ~]# memtester 5 1
memtester version 4.1.2 (32-bit)
Copyright (C) 2009 Charles Cazabon.
Licensed under the GNU General Public License version 2 (only).

pagesize is 4096
pagesizemask is 0xfffff000
want 5MB (5242880 bytes)
got  5MB (5242880 bytes), trying mlock ...locked.
Loop 1/1:
Stuck Address       : ok      
Random Value        : ok
Compare XOR         : ok
Compare SUB         : ok
Compare MUL         : ok
Compare DIV         : ok
Compare OR          : ok
Compare AND         : ok
Sequential Increment: ok
Solid Bits          : ok      
Block Sequential    : ok      
Checkerboard        : ok      
Bit Spread          : ok      
Bit Flip            : ok      
Walking Ones        : ok      
Walking Zeroes      : ok      

Done.
[root@voice ~]#

Quick Table Query

Working with clients, sometime I have to deal wih more than 300
tables within a schema. In order to identify tables, views,
table size, alphabetical ordering of tables, engines etc.
Information Schema is the best place to look for detailed information.

Quick Alternatives:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.2.2-MariaDB-gamma Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use sakila;
Database changed
MariaDB [sakila]> show full tables;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| jk                         | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
24 rows in set (0.00 sec)

MariaDB [sakila]> show full tables where table_type='base table';
+------------------+------------+
| Tables_in_sakila | Table_type |
+------------------+------------+
| actor            | BASE TABLE |
| address          | BASE TABLE |
| category         | BASE TABLE |
| city             | BASE TABLE |
| country          | BASE TABLE |
| customer         | BASE TABLE |
| film             | BASE TABLE |
| film_actor       | BASE TABLE |
| film_category    | BASE TABLE |
| film_text        | BASE TABLE |
| inventory        | BASE TABLE |
| jk               | BASE TABLE |
| language         | BASE TABLE |
| payment          | BASE TABLE |
| rental           | BASE TABLE |
| staff            | BASE TABLE |
| store            | BASE TABLE |
+------------------+------------+
17 rows in set (0.01 sec)


MariaDB [sakila]> show full tables where table_type='view';
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor_info                 | VIEW       |
| customer_list              | VIEW       |
| film_list                  | VIEW       |
| nicer_but_slower_film_list | VIEW       |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff_list                 | VIEW       |
+----------------------------+------------+
7 rows in set (0.01 sec)

MariaDB [sakila]> show full tables where tables_in_sakila like 'film%';
+------------------+------------+
| Tables_in_sakila | Table_type |
+------------------+------------+
| film             | BASE TABLE |
| film_actor       | BASE TABLE |
| film_category    | BASE TABLE |
| film_list        | VIEW       |
| film_text        | BASE TABLE |
+------------------+------------+
5 rows in set (0.00 sec)

MariaDB [sakila]> show table status where name='actor' and engine='innodb' \G
*************************** 1. row ***************************
         Name: actor
       Engine: InnoDB
      Version: 10
   Row_format: Compact
         Rows: 200
Avg_row_length: 81
  Data_length: 16384
Max_data_length: 0
 Index_length: 16384
    Data_free: 5242880
Auto_increment: 201
  Create_time: 2010-09-29 19:55:46
  Update_time: NULL
   Check_time: NULL
    Collation: utf8_general_ci
     Checksum: NULL
Create_options:
      Comment:
1 row in set (0.00 sec)



This is my first post.

Hope you all will enjoy!!!