performance – MySQL suddenly taking 2000 ms per request, instead …

by Lanh on July 25, 2014

UPDATE: finally remembered to look at munin, and it seems that one of the HDDs is the root of the problem after all, unless I’m totally reading this wrong. Here’s the munin output: . Am I right in thinking that one of my HDDs probably needs replacing, or could something else account for those I/O spikes?

I’ve got a pretty powerful server (quad core i7, 32GB RAM) running a Drupal 7 website ( on a LAMP stack, utilizing Varnish, Memcached, APC, CloudFlare. We get about 20,000 pageviews a day, and typically the site has been loading in 3-4 seconds for authenticated users, with total server time accounting for about .5 seconds of that, split fairly evenly between php and mysql (so about .2 seconds in mysql per request)… all as reported via New Relic (lite, so I can’t see db traces).

The 3-4 second page load time, with .2 seconds in mysql has been going like this for several months, consistently.

Then a few days ago the site started taking a lot longer to load, and looking at New Relic, I notice that mysql is now taking about 2 seconds per request, instead of .2 seconds. This is without any major changes to the site at all… just regular site usage.

So my question is, what might suddenly cause mysql to be spending 2 seconds per request instead of .2 seconds, with no changes deployed to the site, and no major changes in traffic?

The best instinct I have is that this might be caused by:

Some table growing too large, or the total db growing too large,
based on current memory allocations in my.cnf, so what used to be
in memory is now thrashing the HDDs.
Some hardware issue, like a
problem with one of the HDDs causing disk access to be slow (I have two HDDs in RAID).
The thing is, based on looking at mysqltuner output, the memory allocations seem to be okay, and based on an initial drive test, the HDDs don’t seem to be broken… so…

Any thoughts on what might cause something like this to happen, how I should go about troubleshooting this?

Here’s mysqltuner output (mysql running for 98 days):

——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28-0ubuntu0.12.04.2-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 88B (Tables: 10)
[--] Data in InnoDB tables: 1G (Tables: 582)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 148

——– Performance Metrics ————————————————-
[--] Up for: 98d 2h 0m 34s (1B q [156.130 qps], 14M conn, TX: 3596B, RX: 225B)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 5.2G global + 3.1M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.1G (19% of installed RAM)
[OK] Slow queries: 0% (32K/1B)
[OK] Highest usage of available connections: 37% (113/300)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.4M
[OK] Key buffer hit rate: 100.0% (35M cached / 545 reads)
[OK] Query cache efficiency: 71.4% (820M cached / 1B selects)
[!!] Query cache prunes per day: 43794
[OK] Sorts requiring temporary tables: 0% (15K temp sorts / 63M sorts)
[!!] Joins performed without indexes: 85938
[OK] Temporary tables created on disk: 1% (302K on disk / 17M total)
[OK] Thread cache hit rate: 99% (113K created / 14M connections)
[!!] Table cache hit rate: 13% (2K open / 19K opened)
[OK] Open file limit used: 0% (69/33K)
[OK] Table locks acquired immediately: 99% (529M immediate / 529M locks)
[OK] InnoDB data size / buffer pool: 1.5G/4.0G

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 1G) [see warning above]
join_buffer_size (> 512.0K, or always use indexes with joins)
table_cache (> 16384)

Here’s my.cnf:

# The MySQL database server configuration file.
# You can copy this to one of:
# – “/etc/mysql/my.cnf” to set global options,
# – “~/.my.cnf” to set user-specific options.
# One can use all long options that the program supports.
# Run program with –help to get a list of available options and with
# –print-defaults to see which it would actually understand and use.
# For explanations see

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain “#” chars…
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
socket = /var/run/mysqld/mysqld.sock
nice = 0

# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address =
# * Fine Tuning
key_buffer = 128M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 300
table_cache = 16384
max_heap_table_size = 256M
join_buffer_size = 512K

#thread_concurrency = 10
# * Query Cache Configuration
query_cache_limit = 4M
query_cache_size = 1024M
# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
# * InnoDB
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
innodb_buffer_pool_size = 4G
innodb_flush_method = O_DIRECT

# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M

# Lock wait timeout. See
innodb_lock_wait_timeout = 120

# * Security Features
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
# For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

max_allowed_packet = 16M

#no-auto-rehash # faster start of mysql but no tab completition

key_buffer = 16M

# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with ‘.cnf’, otherwise they’ll be ignored.
!includedir /etc/mysql/conf.d/

Here’s my slow query log:

Read more from the original source: performance – MySQL suddenly taking 2000 ms per request, instead …

MySQL Cluster 101 | Skillachie

by Lanh on July 25, 2014

What is MySQL Cluster
MySQL Cluster is a technology providing shared-nothing clustering and auto-sharding for the MySQL database management system. It is designed to provide high availability and high throughput with low latency, while allowing for near linear scalability.[2] MySQL Cluster is implemented through the NDB or NDBCLUSTER storage engine for MySQL (“NDB” stands for Network Database).Wikipedia

For simplicity MySQL Cluster can be defined as   a shared-nothing distributed “database” that is designed for fault tolerance, high availability and high performance.
MySQL Cluster Architecture
MySQL Cluster is made of  three main kind of nodes:
1. Management Node(s) [ndb_mgmd]  -  Are responsible for performing mainly administrative  functions related to the cluster. Management nodes are used to check the status,start & stop nodes that are apart of the cluster. They are also responsible for distributing information about the makeup of the cluster.

2.Data/Storage Node(s)[ndbd] -  These are the nodes that store the data in the cluster. Data nodes do the low level work  of storing or retrieving  the data from either disk or ram.
3.SQL Node(s)[mysqld] - These nodes are responsible for extracting the data from the server. SQL nodes interface between the application and the cluster issuing SQL queries and returning the data.
So How Does It All Work ?(Short Version)
A application(client) talks to the MySQL Cluster by communicating  with one(1) or more SQL nodes(mysqld).  When a SQL node receives a query to update or modify some data,that query is then passed  to the data/storage nodes to process the request . A two-phase commit is carried out to ensure that the data is saved locally  on more than one  data/storage nodes for redundancy in the case of a failure (depends on the NoOfReplicas). If any one write fails the update fails and the transaction is not declared as complete. MySQL Cluster only marks/declares the transaction  as complete  to the client only when  the data/storage nodes have received the request and committed all the changes.
Architecture Diagram

To design a cluster that is highly available. You would require at minium in my opinion three(3) data nodes, two(2) SQL nodes & two( 2) management nodes
See  MySQL Cluster Getting Started  tutorial I wrote to complement this article.

Link: MySQL Cluster 101 | Skillachie

MySQL: mysqld_safe works but unable to start mysql service, why …

by Lanh on July 24, 2014

I’m using MySQL 5.6.19 on a Centos 5 machine.

I removed the data dir and created a new one with:

mysql_install_db –user=mysql –datadir=/var/lib/mysql

When I run:

mysqld_safe &

all works fine, but when I try to start the service I get the following error:

Starting MySQL. ERROR! The server quit without updating PID
file (/var/lib/mysql/

and the error log shows:

140720 07:30:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140720 07:30:19 mysqld_safe mysqld from pid file /var/lib/mysql/ ended

All files in /var/lib/mysql/ are owned by mysql:mysql. I tried putting my.cnf in different locations, namely /etc/ /usr/ /var/lib/mysql/.

Here is my.cnf:


innodb_strict_mode = ON
innodb_buffer_pool_size = 2G
innodb_file_per_table = ON
innodb_file_format = Barracuda
innodb_compression_level = 9
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 2

max_allowed_packet = 1G

tmp_table_size = 1G
max_heap_table_size = 1G

query_cache_type = 0
query_cache_size = 0

log_output = TABLE
slow_query_log = ON
log_error = mysql-error.log


Any ideas? Thanks!

Go here to read the rest: MySQL: mysqld_safe works but unable to start mysql service, why …

snabb viktminskning tips

Simple task manager application using AngularJS, PHP, MySQL …

by Lanh on July 23, 2014

This tutorial explains how to create a simple Task Manager application using AngularJS. Here I used PHP for server side communication and MySQL for database.

  Live Demo   Download
Our task manager app will have the following features
Create a new task
Strikeout the completed tasks
Option to delete any task
User will be able to search for tasks
1. Creating MySQL Database
We will create the only table required for this project tasks

`task` varchar(200) NOT NULL,
`status` int(11) NOT NULL,
`created_at` int(11) NOT NULL,

INSERT INTO `tasks` (`id`, `task`, `status`, `created_at`) VALUES
(1, ‘My first task’, 0, 1390815970),
(2, ‘Perform unit testing’, 2, 1390815993),
(3, ‘Find bugs’, 2, 1390817659),
(4, ‘Test in small devices’, 2, 1390818389);

2. The project structure
We will arrange the project files of our MVC framework into 5 different folders for better organisation.
js/ – Javascript library files. e.g. angular.jsapp/ – Our custom javascript controller files for our projectpartials/ – Small pagelets that we wish to reuseajax/ – The .php files to communicate to server (Connect, Create, Read, Update, Delete)css/ – Stylesheet files
3. The Stylesheets
We will add some css for styling purpose

4. Import the required javascript library

5. The Controller Code (app.js)

//Define an angular module for our app
var app = angular.module(‘myApp’, []);

app.controller(‘tasksController’, function($scope, $http) {
getTask(); // Load all available tasks
function getTask(){
$scope.tasks = data;
$scope.addTask = function (task) {
$scope.taskInput = “”;
$scope.deleteTask = function (task) {
if(confirm(“Are you sure to delete this line?”)){

$scope.toggleStatus = function(item, status, task) {


6. Our pagelet file (task.html)


7. CRUD Files


$result = $mysqli->affected_rows;

echo $json_response = json_encode($result);



$arr = array();
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$arr[] = $row;

# JSON-encode the response
echo $json_response = json_encode($arr);



$result = $mysqli->affected_rows;

$json_response = json_encode($result);



$result = $mysqli->affected_rows;

echo $json_response = json_encode($result);

Original post: Simple task manager application using AngularJS, PHP, MySQL …

How To Install Nginx With PHP And MySQL (LEMP Stack) On …

by Lanh on July 23, 2014

Version 1.0 Authors: Till Brehm, Falko TimmeLast edited 21/July/2014
Nginx (pronounced “engine x”) is a free, open-source, high-performance HTTP server. Nginx is known for its stability, rich feature set, simple configuration, and low resource consumption. This tutorial shows how you can install Nginx on a CentOS 6.5 server with PHP support (through PHP-FPM) and MySQL (Mariadb) support.

1 Preliminary Note
In this tutorial I use the hostname with the IP address These settings might differ for you, so you have to replace them where appropriate.

2 Enabling Additional Repositories
Nginx is not available from the official CentOS repositories, so we include the repository of the Nginx project to install it:
vi /etc/yum.repos.d/nginx.repo [nginx]
name=nginx repo

3 Installing MySQL
First we install Mariadb. Mariadb is a free fork of MySQL. Run this command on the shell:
yum install mariadb mariadb-server net-tools
Then we create the system startup links for MySQL (so that MySQL starts automatically whenever the system boots) and start the MySQL server:
systemctl enable mariadb.servicesystemctl start mariadb.service
Now check that networking is enabled. Run
netstat -tap | grep mysql
It should show something like this:
[root@example ~]# netstat -tap | grep mysqltcp 0 0* LISTEN 10623/mysqld Run
to set a password for the user root (otherwise anybody can access your MySQL database!):
[root@example ~]# mysql_secure_installation/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
In order to log into MariaDB to secure it, we’ll need the currentpassword for the root user. If you’ve just installed MariaDB, andyou haven’t set the root password yet, the password will be blank,so you should just press enter here.
Enter current password for root (enter for none):OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.
Set root password? [Y/n] < -- ENTERNew password: <-- yourrootsqlpasswordRe-enter new password: <-- yourrootsqlpasswordPassword updated successfully!Reloading privilege tables..... Success!
By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.
Remove anonymous users? [Y/n] <-- ENTER... Success!
Normally, root should only be allowed to connect from 'localhost'. Thisensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] <-- ENTER... Success!
By default, MariaDB comes with a database named 'test' that anyone canaccess. This is also intended only for testing, and should be removedbefore moving into a production environment.
Remove test database and access to it? [Y/n] <-- ENTER- Dropping test database...... Success!- Removing privileges on test database...... Success!
Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.
Reload privilege tables now? [Y/n] <-- ENTER... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDBinstallation should now be secure.
Thanks for using MariaDB![root@example ~]#
[root@server1 ~]# mysql_secure_installation
4 Installing Nginx
Nginx is available as a package from which we can install as follows:
yum install nginx
Then we create the system startup links for nginx and start it:
systemctl enable nginx.servicesystemctl start nginx.service
There are chances that you get an error like port 80 already in use, error message will be like this
[root@server1 ~]# service nginx startStarting nginx: nginx: [emerg] bind() to failed (98: Address already in use)nginx: [emerg] bind() to failed (98: Address already in use)nginx: [emerg] bind() to failed (98: Address already in use)nginx: [emerg] bind() to failed (98: Address already in use)nginx: [emerg] bind() to failed (98: Address already in use)nginx: [emerg] still could not bind()                                                           [FAILED][root@server1 ~]#
Then it means that there are chances of apache service running there. Stop the service & further start the service for NGINX as follows
systemctl stop httpd.serviceyum remove httpdsystemctl disable httpd.service
systemctl enable nginx.servicesystemctl start nginx.service
And open the http and https ports in the firewall
firewall-cmd --permanent --zone=public --add-service=httpfirewall-cmd --permanent --zone=public --add-service=httpsfirewall-cmd --reload
The resulting output on the shell will look like this:
[root@example ~]# firewall-cmd --permanent --zone=public --add-service=httpsuccess[root@example ~]# firewall-cmd --permanent --zone=public --add-service=httpssuccess[root@example ~]# firewall-cmd --reloadsuccess[root@example ~]#
Type in your web server's IP address or hostname into a browser (e.g., and you should see the nginx welcome page:
(JavaScript must be enabled in your browser to view the large image as an image overlay.)
How To Install Nginx With PHP And MySQL (LEMP Stack) On CentOS 7 - Page 2

Read more: How To Install Nginx With PHP And MySQL (LEMP Stack) On …

tips f

LeakedIn » Blog Archive » Potential leak of data: MySQL URI

by Lanh on July 22, 2014

Detected 2 occurrence(s) of ‘mysql://’:

log_dir = /var/log/trove
trove_auth_url = http://controller:5000/v2.0
nova_compute_url = http://controller:8774/v2
cinder_url = http://controller:8776/v1
swift_url = http://controller:8080/v1/AUTH_
sql_connection = mysql://trove:be415545686c868ddae7bc331cd17b@controller/trove
notifier_queue_hostname = controller
rpc_backend = qpid
qpid_hostname = controller

nova_proxy_admin_user = nova
nova_proxy_admin_pass = 4ad04e075b5bd5353037723e31ef4e

If you find the content of this pastie suspicious or inappropriate, highlight the relevant piece of text and press Shift + E or click here to notify us.
Tags: MySQL URI,

This entry was posted
on Tuesday, July 22nd, 2014 at 12:06 and is filed under PasteMon.
You can follow any responses to this entry through the RSS 2.0 feed.

Both comments and pings are currently closed.

Comments are closed.

See original here: LeakedIn » Blog Archive » Potential leak of data: MySQL URI

Android recipes app

Update on MySQL on POWER8 | Ramblings – The FlamingSpork

by Lanh on July 22, 2014

About 1.5 months ago I blogged on MySQL 5.6 on POWER andtalked about what I had to poke at to make modern MySQL versions run and run well on shiny POWER8 systems.
One of those bugs, MySQL bug 47213 (InnoDB mutex/rw_lock should be conscious of memory ordering other than Intel) was recently marked as CLOSED by the Oracle MySQL team and the upcoming 5.6.20 and 5.7.5 releases should have the fix!
This is excellent news for those wanting to run MySQL on SMP systems that don’t have an Intel-like memory model (e.g. POWER and MIPS64).
This was the most major and invasive patch in the patchset for MySQL on POWER. It’s absolutely fantastic that this has made it into 5.6.20 and 5.7.5 and may mean that these new versions will work out-of-the-box on POWER (I haven’t checked… but from glancing back at my patchset there was only one other patch that could be related to correctness rather than performance).

Here is the original post: Update on MySQL on POWER8 | Ramblings – The FlamingSpork

Cheapest Car Insurance

Inaugural Meet-up Oklahoma City MySQL Meetup | Open Source …

by Lanh on July 22, 2014

The inaugural Meet-up Oklahoma City MySQL Meetup is Wednesday, July 23, 2014!As a special guest speaker, Peter Zaitsev (CEO of Percona and co-author of High Performance MySQL) will be giving a presentation on “Best Indexing Practices”, followed by a Q/A session.
Please RSVP if you plan to attend so we can get a good headcount for food and beverages that will be provided and as always, please spread the word to friends or colleagues in the industry.
It should be a great night and great first event for the MySQL user group in OKC!

About these ads

div { margin-top: 1em; } #google_ads_div_wpcom_below_post_adsafe_ad_container { display: block !important; }

See the original post here: Inaugural Meet-up Oklahoma City MySQL Meetup | Open Source …