Showing entries 1 to 10 of 21
10 Older Entries »
Displaying posts with tag: cache (reset)
RDS Aurora MySQL and Service Interruptions

In Amazon space, any EC2 or Service instance can “disappear” at any time.  Depending on which service is affected, the service will be automatically restarted.  In EC2 you can choose whether an interrupted instance will be restarted, or left shutdown.

For an Aurora instance, an interrupted instance is always restarted. Makes sense.

The restart timing, and other consequences during the process, are noted in our post on Aurora Failovers.

Aurora Testing Limitations

As mentioned earlier, we love testing “uncontrolled” failovers.  That is, we want to be able to pull any plug on any service, and see that the environment as a whole continues to do its job.  We can’t do that with Aurora, because we can’t control the essentials:

  • power button;
  • reset switch;
[Read more]
RDS Aurora MySQL Failover

Right now Aurora only allows a single master, with up to 15 read-only replicas.

Master/Replica Failover

We love testing failure scenarios, however our options for such tests with Aurora are limited (we might get back to that later).  Anyhow, we told the system, through the RDS Aurora dashboard, to do a failover. These were our observations:

Role Change Method

Both master and replica instances are actually restarted (the MySQL uptime resets to 0).

This is quite unusual these days, we can do a fully controlled role change in classic asynchronous replication without a restart (CHANGE MASTER TO …), and Galera doesn’t have read/write roles as such (all instances are technically writers) so it doesn’t need role changes at all.

Failover Timing

Failover between running instances takes about 30 seconds.  This is in line with information provided in the …

[Read more]
Exploring Amazon RDS Aurora: replica writes and cache chilling

Our clients operate on a variety of platforms, and RDS (Amazon Relational Database Service) Aurora has received quite a bit of attention in recent times. On behalf of our clients, we look beyond the marketing, and see what the technical architecture actually delivers.  We will address specific topics in individual posts, this time checking out what the Aurora architecture means for write and caching behaviour (and thus performance).

What is RDS Aurora?

First of all, let’s declare the baseline.  MySQL Aurora is not a completely new RDBMS. It comprises a set of Amazon modifications on top of stock Oracle MySQL 5.6 and 5.7, implementing a different replication mechanism and some other changes/additions.  While we have some information (for instance from the “deep dive” by AWS VP Anurag Gupta), the source code of the Aurora modifications …

[Read more]
MySQL inline query versus stored procedure comparison

Simple query using group clause for 1 million records resulting in final list of 27 records.

First time takes 0.43053775 secs.
Same query through Stored procedure: First time takes 0.43341600 secs.

So in terms of time, first time they are very close.
Profiling comparison for both can be seen in below figure no_cache_comparison.png where left one is simple inline query and right one is stored procedure query.




There are some actions which are extra in the inline query:

1. freeing items
2. logging slow query
3. cleaning up

Running both second time retrieve data from cache taking
0.00048025 secs for simple query and 0.00036625 for stored procedure.

Profiling comparison for …

[Read more]
Importance of MySQL cache

My test environment is:
Ubuntu 14.04 Trusty Tahr
MySQL Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)

MySQL uses sql cache to store results of queries that have been executed so that when the same query is executed again it retrieves the result data set from the cache instead of getting it again from db. So it is faster data access.

It is by default enabled in MySQL.

This is interesting since there is one question we ought to ask here whether we should use it or disable it or just leave it as it is who cares :).

Ok, moving forward today's session goals are:

  1.     How useful is MySQL cache?
  2.     When to use it and when not to use it?
  3.     What to do if you do not want to use it?


There are some catchy areas here too like not all your queries will be stored in cache. …

[Read more]
Cache pre-loading on mysqld startup

The following quirky dynamic SQL will scan each index of each table so that they’re loaded into the key_buffer (MyISAM) or innodb_buffer_pool (InnoDB). If you also use the PBXT engine which does have a row cache but no clustered primary key, you could also incorporate some full table scans.

To make mysqld execute this on startup, create /var/lib/mysql/initfile.sql and make it be owned by mysql:mysql

SET SESSION group_concat_max_len=100*1024*1024;
SELECT GROUP_CONCAT(CONCAT('SELECT COUNT(`',column_name,'`) FROM `',table_schema,'`.`',table_name,'` FORCE INDEX (`',index_name,'`)') SEPARATOR ' UNION ALL ') INTO @sql FROM information_schema.statistics WHERE table_schema NOT IN ('information_schema','mysql') AND seq_in_index = 1;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET SESSION group_concat_max_len=@@group_concat_max_len;

and in my.cnf add a line in the [mysqld] block

init-file = …
[Read more]
Caching could be the last thing you want to do

I recently had a run-in with a very popular PHP ecommerce package which makes me want to voice a recurring mistake I see in how many web applications are architected.

What is that mistake?

The ecommerce package I was working with depended on caching.  Out of the box it couldn't serve 10 pages/second unless I enabled some features which were designed to be "optional" (but clearly they weren't).

I think with great tools like memcached it is easy to get carried away and use it as the mallet for every performance problem, but in many cases it should not be your first choice.  Here is why:

  • Caching might not work for all visitors - You look at a page, it loads fast.  But is this the same for every user?  Caching can sometimes be an optimization that makes the average user have a faster experience, but in reality you should be caring more that …
[Read more]
PBXT 1.5.02 Beta adds 2nd Level Cache

As many probably already know, PBXT is the first MySQL Storage Engine to use a log-based architecture. Log-based means that data that would normally first be written to the transaction log, and then to the database tables, is just written to the log, and the log becomes part of the database.

This result is that data is only written once, and is always written sequentially. The advantage when writing is obvious, but there is a down side (as always). The data is written to the disk in write order, which is seldom the order in which the data is retrieved. So this results in a lot of random reads to the disk when accessing the data later.

Placing the data logs on a Solid State Drive would solve this problem, because SSDs have no seek time. But the problem with this solution is that SSDs are still way to expense to base all your storage needs on such hardware.

The solution: an SSD-based 2nd Level Cache.

[Read more]
change accelerator cache ratio

I was given the task of checking the array accelerator cache ratio and see if it was set to optimal levels. Our ideal preference was a read/write ratio of 0/100.

The machine configuration is HP DL180 G5, 2 x Xeon L5420 2.50GHz, 15.7GB / 16GB 667MHz DDR2, 6 x 300GB-15K SAS.This machine was running mysql 5.1.36 using the innodb plugin.

The command line utility to check the controller configuration is “hpacucli”. Navigating using hpacucli is very straight forward.

“ctrl all show config detail” Will give you the entire controller configuration.

=> ctrl all show config detail

Smart Array P400 in Slot 5
Bus Interface: PCI
Slot: 5
Serial Number: P61630K9SW31NL
Cache Serial Number: PA82C0J9SW02H1
RAID 6 (ADG) Status: Enabled
Controller Status: OK
Chassis Slot:
Hardware Revision: Rev D
Firmware Version: 4.12
Rebuild …

[Read more]
Get a load of your database - paginated caching

Your site is getting awfully slow? There's just to much reads to your database and you have already tweaked the performance of every query? In most cases data caching is the solution to your problem!

The idea is to cache all processed data you heave retrieved from the database. Let us look on a example. It uses a mockup class that basically can handle any caching system like memcached or xcache

Showing entries 1 to 10 of 21
10 Older Entries »