Showing entries 121 to 130 of 142
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: tips (reset)
The perils of InnoDB with Debian and startup scripts

Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance.

It can happen on a server with MyISAM tables, if there are enough tables, but it is far worse on InnoDB. There are a few reasons why this happens -- access to open an InnoDB table is serialized by a mutex, for one thing, and the mysqlcheck script opens all tables. One at a time.

It's pretty easy to get into a "perfect storm" scenario. For example, I'm working with one client right now who has a hosted multi-tenanting application that keeps each customer in its own database. So they have a lot of databases and a lot of tables. And they're running on Amazon EC2 with 8G of RAM and EBS storage, which is slower than typical directly-attached server-grade RAID storage. Since they have a lot of tables, InnoDB uses …

[Read more]
Optimizing repeated subexpressions in MySQL

How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they're all the same and not calculate the result for each of them?

I had a specific case where I needed to find out for sure, so I made a little benchmark. The query looks something like this:

PLAIN TEXT SQL:

  1. SELECT sql_no_cache
  2.    pow(sum(rental_id), 1),
  3.    pow(sum(rental_id), 2),
  4.    pow(sum(rental_id), 3),
  5.    pow(sum(rental_id), 4),
  6.    pow(sum(rental_id), 5),
  7.    pow(sum(rental_id), 6),
  8.    pow(sum(rental_id), 7),
  9.    pow(sum(rental_id), 8),
  10.    pow(sum(rental_id), 10),
  11.   …
[Read more]
ActiveMQ Tips: Flow Control and Stalled Producers Problem

It’s been a few months since we‘ve started actively using ActiveMQ queue server in our project. For some time we had pretty weird problems with it and even started thinking about switching to something else or even writing our own queue server which would comply with our requirements. The most annoying problem was the following: some time after activemq restart everything worked really well and then activemq started lagging, queue started growing and all producer processes were stalling on push() operations. We rewrote our producers from Ruby to JRuby, then to Java and still – after some time everything was in a bad shape until we restarted the queue server.

So, long story short, after a lots of docs and source code reading we’ve found really interesting thing. There is a “feature” added in the recent ActiveMQ release …

[Read more]
Profiling MySQL stored routines

These days I'm working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven't worked much with stored procedures, I though it's going to be a piece of cake. In the end - it was, but there's a catch.

My initial idea was - I'll just analyze queries in the slow query log generated by our mysql build running with long_query_time=0, get the slowest ones and work on them. It wasn't really all the way I expected..

For a showcase I have created a function "whatstheweatherlike". Let's call it and see what shows up in the slow query log:

PLAIN TEXT SQL:

  1. mysql> SELECT whatstheweatherlike(5);
  2. +----------------------------------------------------------+
  3. | …
[Read more]
Dropping unused indexes

Vadim wrote some time ago about how to find unused indexes with single query.

I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So I extended Vadim's query to generate ALTER TABLE statements automatically. I also made it to look only at tables which were accessed:

PLAIN TEXT SQL:

  1. mysql> SELECT concat('alter table ',d.table_schema,'.',d.table_name,' drop index ',group_concat(index_name separator ',drop index '),';') stmt FROM (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND …
[Read more]
High-Performance Click Analysis with MySQL

We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.  The first thing these have in common is that they're generally some kind of loggable event.

The next characteristic of a lot of these systems (real or planned) is the desire for "real-time" analysis.  Our customers often want their systems to provide the freshest data to their own clients, with no delays.

Finally, the analysis is usually multi-dimensional.  The typical user wants to be able to generate summaries and reports in many different ways on demand, often to support the functionality of the application as well as to provide reports to their clients.  Clicks by day, by customer, top ads by clicks, top ads by click-through ratio, and so on for dozens of different types of slicing and dicing.

And as a result, one of the most common …

[Read more]
A quick way to get memcached status

There are all sorts of different interfaces to memcached, but you don't need any of them to make requests from the command line, because its protocol is so simple. Try this, assuming it's running on the usual port on the local machine:

PLAIN TEXT CODE:

  1. echo stats | nc 127.0.0.1 11211
  2. STAT pid 22020
  3. STAT uptime 3689364
  4. STAT time 1227753109
  5. STAT version 1.2.5
  6. STAT pointer_size 64
  7. STAT rusage_user 4543.071348
  8. STAT rusage_system 8568.293421
  9. STAT curr_items 139897
  10. STAT total_items 51710845
  11. STAT bytes 360147055
  12. STAT curr_connections 40
  13. STAT total_connections 66762
  14. STAT connection_structures 327
  15. STAT cmd_get 319992973
  16. STAT cmd_set …
[Read more]
Poor man’s query logging

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with --log (or --general-log in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by --log-slow-queries parameter (MySQL 5.1 requires also --slow-query-log), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.

The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and rebuilding the entire database from sources.

I know that many …

[Read more]
Living with backups

Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem by my last sentence, but it is not important right now. Either way the data is out and ready to save someone’s life (or job at least). Unfortunately taking backup does not come free of any cost. On the contrary, it’s more like doing very heavy queries against each table in the database when mysqldump is used or reading a lot of data when copying physical files, so the price may actually be rather high. And the more effectively the server resources are utilized, the more that becomes a problem.

What happens when you try to get all the data?

The most obvious answer is that it needs to be read, through I/O requests, from a storage that it resides on. The storage is …

[Read more]
MySQL, AIX5L and malloc()

Some time ago I get brand new IBM POWER6 server as the replacement for "old" P5 used to host Oracle database. Because we planed to use advanced virtualization with VIOS + LPAR/DLPAR I conceived the idea to use one spare partition for MySQL tests. Because I had no past experience with it and there is not much documentation all around the web, I tried to set-up system and database traditional way. The first problem I hit was memory allocation and I think it is the best place share my remarks. Let's start from the beginning..

For any reason, you decided to run MySQL database on AIX 5L operating system. You compiled it successfully, configured and.. unluckily database didn't start due to memory allocation problem?

Basically, for some historical reasons AIX OS will allow your application to allocate maximum 256MB of memory per process by default. To use more, you have to use "Large Address Space" memory model so AIX will split …

[Read more]
Showing entries 121 to 130 of 142
« 10 Newer Entries | 10 Older Entries »