Install MySQL plugin for Newrelic in a Minute

To use this plugin, you must have:

  • a Java Runtime Environment (JRE) of 1.6 or higher
  • at least one database to monitor (MySQL 5.0 or higher)
  • a New Relic account

New Relic Platform Installer (NPI) is a simple, lightweight command line tool that helps you easily download, configure and manage New Relic Platform Plugins

Plugin for Generic Linux OS (OS = Opensuse)

LICENSE_KEY=4eeeeeeeeeeeeeeeeeeeeeeee2e bash -c "$(curl -sSL https://download.newrelic.com/npi/release/install-npi-linux-x64.sh)"
npi install com.newrelic.plugins.mysql.instance

Configuration File

#vim ~/newrelic-npi/plugins/com.newrelic.plugins.mysql.instance/newrelic_mysql_plugin-2.0.0/config/plugin.json

 

{
"agents": [
{
"name" : "Host Name on Newrelic UI",
"host" : "localhost/RDS ENpoint",
"metrics" : "status,newrelic",
"user" : "DB_USER_NAME",
"passwd" : "DB_PASSWORD"
}
]
}

Start Plugin:

#cd /root/newrelic-npi/plugins/com.newrelic.plugins.mysql.instance/newrelic_mysql_plugin-2.0.0
#java -Xmx128m -jar plugin.jar

 

 

GitHub = https://github.com/newrelic-platform/newrelic_mysql_java_plugin
Plugin Home Page = https://rpm.newrelic.com/accounts/748441/plugins/directory/52

MySQL Optimization

There is one specific MySQL configuration parameter above all others that will produce significant performance improvements and takes very little understanding to implement.

“query_cache_size”

This configuration directive tells the MySQL server to store the result of the query in memory—much faster than accessing from disk.

In a hosting environment with 1 GB RAM, this allows for a query cache size of 64 MB or larger depending on other memory use factors, such as other services running on the server.

This allows 64 MB of queries to be stored in the very fast RAM, or memory of your hosting environment, as opposed to having to access the disk each time this query is executed or each time the page is loaded. This is an extremely large amount of data.

Invaldating a subset of a sufficiently large query cache can take a long time. During this time the server is efficiently frozen and no other operation can be executed. This patch addresses this problem by moving the locks which cause the freezing and also by temporarily disable the query cache while the invalidation takes place.

Extremely large query cache sizes should not be used. The larger the query cache size, the greater the impact of the data validation MySQL performs on the cache when a table has changed, and the resulting set of data in the cache is no longer valid. The bottom line is not to use a query cache size larger than what you need.

On a Magento installation with over 80,000 products, we have found 64 MB to provide 10x the performance improvement over disabled query cache. The actual use or capacity of the cache is less than 50 percent, which provides room for continued cache growth up to 64 MB.

The performance increase you should see from this single configuration enhancement will be in the order of 100-1000% depending on the query executed and the disk being accessed.

To check if your host has query caching enabled in the MySQL server, you can issue the following command from your MySQL command prompt:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| have_query_cache | YES      |
+------------------+----------+

 

Here’s the important items in the list and what they mean:

query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.
query_cache_type – This value must be ON or 1 for query caching to be enabled by default.
query_cache_limit – This is the maximum size query (in bytes) that will be cached.

If the query_cache_size value is set to 0 or you just want to change it, you’ll need to run the following command, keeping in mind that the value is in bytes. For instance, if you wanted to allocate 8MB to the cache we’d use 1024 * 1024 * 8 = 8388608 as the value.

SET GLOBAL query_cache_size = 8388608;

Similarly, the other options can be set with the same syntax:

SET GLOBAL query_cache_limit = 1048576;
SET GLOBAL query_cache_type = 1;

Now how do we tell if it’s actually working? You can use the SHOW STATUS command to pull all the variables that start with “Qc” to take a look at what is going on under the hood.

mysql> SHOW STATUS LIKE ‘Qc%’;
+————————-+——–+
| Variable_name           | Value  |
+————————-+——–+
| Qcache_free_blocks      | 65     |
| Qcache_free_memory      | 201440 |
| Qcache_hits             | 18868  |
| Qcache_inserts          | 2940   |
| Qcache_lowmem_prunes    | 665    |
| Qcache_not_cached       | 246    |
| Qcache_queries_in_cache | 492    |
| Qcache_total_blocks     | 1430   |
+————————-+——–+
8 rows in set (0.00 sec)

Learn more about MySQL query cache from = http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

Database Sharding

 

 

“share nonthing” : Key Law on database sharding Architecture.
Small Databases are Fast, Big Databases are Slow !!!
DB Sharding – Breaking a Bigger DB into a Smaller DB.

Key Points on DB Sharding —

  • Partition Data across master
  • Writes and read are distributed
  • Application needs modification
  • Needs choice partitioning strategy for uniform data distribution.

Issues –

  • Joins cannot be performed across shards
  • Application modification can be expensive.
  • Example : Evernote uses database sharding – localized failures , no need for joins. Each shards handles all  data &  traffic for about 100,000 users.

Sharding is another way to resolve MySQL scalability issues. It usually means splitting up the data by some logic derived from the application. This can be done by selecting a key in the data and splitting the data by hashing that key and having some distribution logic. It can also be done by identifying the application needs and setting different tables or different data sets in different databases (splitting the North-America sales data from the EMEA sales data, etc.)

This approach is simple from the database standpoint, but is very complex from the application standpoint since the application needs to be modified to deal with the data being scattered into the different shards. Moreover, combining data from different shards can be very complex and involves development in the application (you can’t just run a simple JOIN.)

Advantages of scaling out and in using sharding:

* Scales beyond the limitations of a single machine
* Scales both read and write operations (but makes some operations impossible to achieve in the database)
* Scales both throughput and capacity

Disadvantages of scaling out and in using sharding:

* Complex and requires application changes
* Scaling is usually offline and requires a re-partitioning event – and may require application changes.

Today, there are some solutions that introduce auto-sharding (Scalebase, Dbshards). This approach makes sharding more similar to shared-nothing partitioning, thus taking the sting out of some sharding complexities. However, it still requires application awareness and could prove to be a limiting factor if you needed to update your app or migrate to a different database solution.

How to Reset Mysql ROOT Password


1) Stop the mysql demon

/etc/init.d/mysql stop

2) Start the mysqld demon process using the –skip-grant-tables option with this command

/usr/sbin/mysqld --skip-grant-tables --skip-networking &

3) start the mysql client process using this command

mysql -u root

4) from the mysql prompt execute this command to be able to change any password

FLUSH PRIVILEGES;

5) Then reset/update your password

SET PASSWORD FOR root@'localhost' = PASSWORD('P@SsW0rD');
FLUSH PRIVILEGES;

6) Then stop the mysqld process and relaunch it with the classical way:

/etc/init.d/mysql stop
/etc/init.d/mysql start