Archive

Archive for the ‘Uncategorized’ Category

Import Blob data To Mysql

January 13, 2015 Leave a comment

If you have large blob data in your tables most likely mysql will die on you while trying to import.

Solution is add

max_allowed_packet=32M (or whatever value you require)

to [mysqld] section in your my.cnf file.

Categories: Uncategorized Tags:

Nginx Uniq Visitors

November 6, 2014 Leave a comment

Total hits per day:

[root@marko nginx]# awk '/06.Nov.2014/ {print $1}' < www.xxx.com.access.log | sort | uniq |wc -l
182

Hits Per IP

[root@marko nginx]# awk '/06.Nov.2014/ {print $1}' < www.xxx.com.access.log | sort | uniq -c
2 xx.30.xx.62
1 xx.9.xx.191
1 xx.131.xx.112
4 xx.21.xx.193
2 xx.27.xx.127

Mysql Query Cache Optimization Review

November 5, 2014 Leave a comment

Query Cache:

my.cnf:
query_cache_type = 1
query_cache_size = 20M
query_cache_limit = 1M

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 537      |
| Qcache_free_memory      | 14197176 |
| Qcache_hits             | 24170    |
| Qcache_inserts          | 4697     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 68       |
| Qcache_queries_in_cache | 2895     |
| Qcache_total_blocks     | 6481     |
+-------------------------+----------+
8 rows in set (0.00 sec)

Query Cache Formulas:

Query cache hit rate = ((Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached))*100)

Query cache use percentage = ((query_cache_size – Qcache_free_memory) / query_cache_size )*100

Average size of the queries = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

Threads:
my.cnf:
thread_cache_size = 30

mysql> SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 91082 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 12    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 12    |
+----------------------+-------+
1 row in set (0.00 sec)

Attention:
Threads_created / Connections should be < 0.01 (if needed increase thread_cache_size)
thread_cache_size should be > Max_used_connections

Thread Formulas:
Thread cache hit rate = 100 – ((Threads_created / Connections) * 100)

Connection Formulas:

Percentage of used connections: processlist / MAX_CONNECTIONS

mysql> select (( pl.connections / gv.max_connections ) * 100) as percentage_used_connections from ( select count(*) as connections from information_schema.processlist ) as pl, ( select VARIABLE_VALUE as max_connections from information_schema.global_variables where variable_name = 'MAX_CONNECTIONS' ) as gv;

+-----------------------------+
| percentage_used_connections |
+-----------------------------+
|          0.6622516556291391 |
+-----------------------------+
1 row in set (0.00 sec)

Maintenance

mysql> RESET QUERY CACHE; 

will clear out the query cache

if Qcache_free_blocks approaches Qcache_total_blocks / 2, your query cache is severely fragmented.

mysql> FLUSH QUERY CACHE; 

does NOT clear out the query cache but rather defrags the cache and leaving the cached query results in place

Problem:
Qcache_lowmem_prunes value is increasing and you have a lot of Qcache_free_blocks
Reason 1:
Fragmentation is causing queries to be deleted from the cache.
In this case, increase query_cache_limit or query_cache_size, or both of them.
Reason 2:
The query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable (default value 4096 bytes). When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit:

If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit (minimum value 512 bytes). The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

To change in runtime

mysql> Set global query_cache_min_res_unit = 1024;

To change in my.cnf

query_cache_min_res_unit = 1024

mysql> show variables like "query%";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_alloc_block_size       | 8192     |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 1024     |
| query_cache_size             | 20971520 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
| query_prealloc_size          | 8192     |
+------------------------------+----------+
7 rows in set (0.00 sec)

OpenSSL Certificate Generation

September 24, 2014 Leave a comment

openssl req -new -newkey rsa:2048 -nodes -keyout example.key -out example.csr

Categories: Uncategorized

Nginx Bundle SSL Certicate Configuration

September 23, 2014 Leave a comment
cat www.example.com.crt bundle.crt > www.example.com.chained.crt

server {
       listen         80;
       server_name   www.example.com;
       rewrite        ^ https://$server_name$request_uri? permanent;
}


server {
        listen 443;
        server_name www.example.com;
        access_log /var/log/nginx/www.example.com.access.log main;
        error_log /var/log/nginx/www.example.com.error.log error;
        ssl on;
        ssl_certificate /etc/nginx/ssl/www.example.com.chained.crt;
        ssl_certificate_key /etc/nginx/ssl/www.example.com.key;

        include /etc/nginx/conf.d/includes.inc;

        location / {
                root   /var/www/html/www.example.com;
                index index.php;
        }

        location /phpmyadmin {
                alias /usr/share/phpMyAdmin;
                index index.php;
                allow  x.x.x.x;
                deny all;
        }

        location ~ /phpmyadmin/.*\.php$ {
                if ($fastcgi_script_name ~ /phpmyadmin(/.*\.php)$) {
                        set $valid_fastcgi_script_name $1;
                }
                fastcgi_pass 127.0.0.1:9000;
                fastcgi_index index.php;
                fastcgi_param SCRIPT_FILENAME /usr/share/phpMyAdmin$valid_fastcgi_script_name;
                include /etc/nginx/fastcgi_params;
        }

        location ~ \.php$ {
                fastcgi_pass 127.0.0.1:9000;
                fastcgi_index index.php;
                fastcgi_param SCRIPT_FILENAME /var/www/html/www.example.com$fastcgi_script_name;
                fastcgi_param PATH_INFO $fastcgi_script_name;
                include /etc/nginx/fastcgi_params;
        }
}



Categories: Uncategorized

Mysql Monitoring

July 17, 2014 Leave a comment

Monitor the queries being run by MySQL:

watch -n 1 mysqladmin –user=<user> –password=<password> processlist

Monitor MySQL threads per user:

mysql -BNe “SELECT user,COUNT(user) AS count FROM processlist GROUP BY user ORDER BY count;” information_schema

 

my.cnf

[mysqld]
….
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

 

Categories: Uncategorized Tags:

Thinkpad x120e mouse issue

February 5, 2014 Leave a comment

Seems like after recent kernel and/or driver updates Thinkpad x120e has mouse issues on linux. Issues include malfunctioning mouse buttons, continuous button clicks etc… Here is solution disable touchpad and/or trackpoint.

$ xinput
⎡ Virtual core pointer                          id=2    [master pointer  (3)]
⎜   ↳ Virtual core XTEST pointer                id=4    [slave  pointer  (2)]
⎜   ↳ TPPS/2 IBM TrackPoint                     id=11   [slave  pointer  (2)]
⎜   ↳ SynPS/2 Synaptics TouchPad                id=10   [slave  pointer  (2)]
⎣ Virtual core keyboard                         id=3    [master keyboard (2)]
    ↳ Virtual core XTEST keyboard               id=5    [slave  keyboard (3)]
    ↳ Power Button                              id=6    [slave  keyboard (3)]
    ↳ Video Bus                                 id=7    [slave  keyboard (3)]
    ↳ Sleep Button                              id=8    [slave  keyboard (3)]
    ↳ AT Translated Set 2 keyboard              id=9    [slave  keyboard (3)]
    ↳ ThinkPad Extra Buttons                    id=12   [slave  keyboard (3)]

Disable whichever you prefer

$ xinput --disable 10