A quick note, I am speaking at Percona live in London next week… its should be a rip roaring time. I have two topics I am speaking on.
The first is on building a MySQL Data Access Layer with Ruby and Sinatra. While this may seem a bit odd, its actually very cool and useful. With
As a followup to my previous post on installing HandlerSocket on CentOS 5 + Percona-Server 5.1, I’ve been asked to provide an updated HOWTO for RHEL 6 + Percona-Server 5.5. Although very similar to the original steps, there are some new traps (RHEL 6 comes with SELinux enabled) and there are some new ec2 gotchas! [...]
In this tutorial I will describe, how to use nmon (Nigel’s performance Monitor) to monitor performance data in the interactive mode or in the capture mode.
nmon can display / capture the following performance data
A special extended edition of Tech Messages for 2011-08-31 through 2011-09-21:
If I were to ask you 'what are the (database) metrics?', what do
you think I'm asking? Thats an interesting question and something
that requires some thought so lets handle this by actually
looking at the following two alternative questions. 'What am I
trying to archive [with database metrics]?' and 'how will I know
when I have achieved it [by having database metrics]'?
Define the problem
What are we really doing
What are we trying to achieve?
How do we know when we have achieved it?
So, what is capacity
Capacity is knowing how much extra we can do without making
things 'slow'. Its a …
We have seen a few instances where upgrades of MySQL 5.1 to 5.5 have resulted in poor performance when using default values. After investigation it would appear that the main culprit appears to be the innodb_thread_concurrency setting.
In MySQL 5.1 the default was 8, while in MySQL 5.5, the default was changed to 0 (unlimited).
So what does innodb_thread_concurrency do and why would it cause performance issues?
The value given to innodb_thread_concurrency sets the number of simultaneous threads the InnoDB engine will create. If there are more jobs waiting to be processed than there are available thread slots, these jobs will be made to wait until a thread slot becomes free. So on the face of it, making this unlimited sounds like a good idea - so you won't have any jobs waiting on thread slots.
In an ideal world, unlimited is good. In the real world, unlimited doesn't exist. CPUs only have …
[Read more]MySQL is needlessly slow at accepting new connections. People usually work around that by having various sorts of connection pools, but there’s always a scale at which connection pools are not feasible. Sometimes connection avalanches come unexpected, and even if MySQL would have no trouble dealing with queries, it will have problems letting clients in. Something has to be done about it.
Lots of these problems have been low hanging fruits for years – it ‘was not detected’ by benchmarks because everyone who benchmarks MySQL would know that persistent connections are much faster and therefore wouldn’t look at connection speeds anymore.
Usually people attribute most of slowness to the LOCK_thread_count mutex – they are only partially right. This mutex does not just handle the counter of active running connections, but pretty much every operation that deals with increase or decrease of threads (thread cache, active thread …
[Read more]Scalability is about application, architecture and infrastructure design, and careful management of server components.
1. Object Relational Mappers
ORMs are popular among developers but not among performance experts. Why is that? Primarily these two engineers experience a web application from entirely different perspectives. One is building functionality, delivering features, and results are measured on fitting business requirements. Performance and scalability are often low priorities at this stage. ORMs allow developers to be much more productive, abstracting away the SQL difficulties of interacting with the backend datastore, and allowing them to concentrate on building the features and functionality.
On the performance side the picture is a bit different. By leaving SQL query writing to an ORM, you are faced with complex queries that the database cannot optimize …
[Read more]Hanging out in #mysql on freenode the other day, I overheard someone saying that the reason to use connection pools with MySQL is because JDBC connections are expensive to create. That is true out of the box, but mostly because the out of the box behavior of MySQL's JDBC driver is to be standards-compliant. If you know that your DBA and your developers aren't doing crazy things with the database (changing configurations without letting the developers know, going around the "standard" API calls to start/end transactions, etc), then you can get to the point where connection setup is no slower than any other API. Does this mean you shouldn't use a connection pool? NO! (more on this next week).
Here's an iterative overview of the changes made in configuration, and how they affect what queries the driver does on initialization.
First, asking the driver to connect with default configuration results in the following statements being …
[Read more]I use snmptrapd to catch SNMP traps and then put them into a MySQL database using a pretty generic trap handler. This gives me the opportunity to generate such useful information as: > SELECT HOUR(time) AS "hour", COUNT(*) AS "bounces" FROM snmptraps WHERE hostname = 'XXX' AND time > '2011-07-25' AND trap_oid = 'SNMPv2-MIB::snmpTrapOID.0 IF-MIB::linkDown' […]