Showing entries 1 to 10 of 168
10 Older Entries »
Displaying posts with tag: primary (reset)
Finding out the MySQL performance regression due to kernel mitigation for Meltdown CPU vulnerability

Update: I included the results for when PCID is disabled, for comparison, as a worse case scenario.

After learning about Meltdown and Spectre, I waited patiently to get a fix from my OS vendor. However, there were several reports of performance impact due to the kernel mitigation- for example on the PostgresQL developers mailing list there was reports of up to 23% throughput loss; Red Hat engineers report a regression range of 1-20%, but setting OLTP systems as the worse type of workload. As it will be highly dependent on the hardware and workload, I decided of doing some test myself for the …

[Read more]
A DBA Analyses 'The Phoenix Project'

Last year, I read 'The Phoenix Project'. I liked it and as an IT manager in the past, I did experience high blood pressure during the SEV1 scenarios in the book.



I also liked the way DevOps methodology helped solve issues with IT as well as help the company succeed overall.
As a DBA, however, I did have some things that didn't make sense to me about this story.  Bare in mind that the two major incidents in the book were database related. So in this post, I would like to jot down some things I have noticed and how they could have been solved looking at them from a different lens.
Caution, Spoiler Alert

Incident No.1 - Tokenisation
In the first incident, a 3rd party supplier ran a script …

[Read more]
Top 4 Reasons Companies Won't Fix Their Database Issues

When I consult at a company, I aim to identify issues with their database and give options on how to solve them.
However, sometimes implementing those solutions may be a more lengthy process than it needs to be and sometimes they may not be implemented at all. During my career, I have observed some reasons as to why that might happen within organizations.

Obviously, the following observations will never happen at your company. I am just writing about them so that you might notice them in other places.

1. Legacy code 
People don't like to have anything to do with legacy code. It’s painful. It’s difficult. It’s risky to change. It runs business critical functions. Worse of all, they didn’t write it. This can be a problem as often, the most cripling database issues require changes to legacy code.

2. New Technologies or Methods
People don’t like you to introduce any …

[Read more]
Setting Up Databases in your Development Environment

Setting up databases in development environments can be challenging.

Normally, what I usually see is some automated process for setting up empty databases with up-to-date data structures. This is helpful for integration testing, but is tricky for actual development as well as performance testing. 
For example:

  • It is difficult to conceptually get your head around writing a query when you cannot see any data in your tables
  • You cannot possibly know if your query is slow before you deploying it to production without running it against 'some' data.

Relevant Post: How to Not be the One that Deploys that Slow Query to Production
In addition, there can be a strict requirement to not let sensitive customer data be available outside certain secure environments and …

[Read more]
Data Modelling: Counter Table

A counter table is a table that keeps counts of particular items or for certain keys. This can range from page count on your blog to keep track of a limit the user is allowed to have from a particular item or service.


Usually, a counter table would be better kept in something like Memcached or Redis as frequent increment updates would be better suited to those in-memory systems.

MySQL and Innodb in particular has many stringent systems to make sure that your data has been reliably written to disk. Just going through those systems alone, can make having a counter table, not suitable, not even considering the speed it takes to update the actual table.

However, sometimes there is a need for certain assurances from failure scenarios where in-memory …

[Read more]
Archiving for a Leaner Database

There is an idea that data is sacred and needs to be stored forever. However, if you keep your data forever, you will, sooner or later, have a very large database.

In order to keep operations running smoothly, it would be useful to allocated data that is used more frequently in certain tables and keep data that is used less frequently in archive tables.


Some examples
You have a large table that stores transactions and it's size is 200Gb. It is that way, because your company has been around for 5 years, but in the last year, your company has been successful acquiring new users and your data has doubled.

Congratulations.

In your database, you now have a table that has 5 years worth of data, but your application usually only needs about the last 1-3 months. There may be a use case where someone might require data about a customer for a period starting a year ago and there may …

[Read more]
How to Not be the One that Deploys that Slow Query to Production

Have you ever deployed code to production that slowed the database and the entire site down?
Did you get a feeling of anxiety when you just read that?

Well, it could be to do with your test environment.

Most places nowadays have test suites that will check for integration issues. But in very few will check for performance.

The issue lies with how developers are set up to develop code. They have their code on their local computer with a local copy of an empty database where they develop against. That setup will not give you any useful feedback about how your code will performs once its run against the production database.

How do you get Performance Feedback for your Queries?
Whenever you log into your database, lets say MySQL, you get 3 types of feedback:

[Read more]
Top Slowest Queries and their Associated Tables in MySQL

The following query gets data from performance_schema in MySQL and attempts to regex the digest to the list of tables in the same schema.

 SELECT d.*,  
(SELECT group_concat(distinct TABLE_NAME) FROM information_schema.TABLES
WHERE table_schema = d.schema_name and d.digest_text regexp table_name) table_name
FROM performance_schema.events_statements_summary_by_digest d
WHERE d.DIGEST_TEXT regexp "^(SELECT|UPDATE|DELETE|REPLACE|INSERT|CREATE)"
and d.LAST_SEEN >= curdate() - interval 7 day
ORDER BY d.SUM_TIMER_WAIT DESC limit 10\G


My top author list for Planet MySQL

Who are the top individual authors of influential recent posts to planet MySQL?  The planet MySQL page includes a list of the top 20 authors as well as a list of the top 10 vendor blogs.  However, since posts to the vendor blogs make up at least 1/4 of all of the posts, and the authors of vendor blog posts are not included in the top author list, I decided to compute my own top author list.  I include the hidden authors from the vendor blogs when computing my top author list.

The first problem is to identify the hidden authors for posts from each vendor blog.  This requires that the author information be extracted from the individual posts, and this requires a specialized parser for each vendor blog to extract the author name from the document.
The second problem is to rank the authors using some criteria such as the number of posts in a given recent time range.   I could run a page rank algorithm if I …

[Read more]
New Query Analysis Features in MONyog

While looking to optimize your MySQL, you are needing to spot slow/bad queries at a glance and get deep insights about them with ease.

The latest update of MONyog brings new easier ways to find problem SQL in Real-Time, Wayback Machine and sniffer based Query Analyser using Performance Schema. Here is what’s new:

Query Execution Status and Full Table Scan Count
View success/failure status of every query executed and also the number of ‘full table scans’ for queries in a single glance.

The latest update also has an option to switch between Performance Schema and Processlist in Real-Time making it easy for you to enable/disable Performance Schema or Processlist.

Get the latest update now to enjoy these features.Existing customers can download MONyog 6.52 from Customer Area. To evaluate MONyog 6.52, please …

[Read more]
Showing entries 1 to 10 of 168
10 Older Entries »