Tag: mysql

GrabPERF – Database Failure

2009-09-04 / spierzchala / 0 Comments

The GrabPERF database server failed sometime early this morning. The hosting facility is working to install a new machine, and then will begin the long process of restoring from backups and memory.
Updates will be posted here.

UPDATE – Sep 4 2009 22:00 GMT: The database listener is up and data is flowing into the database and can be viewed in the GrabPERF interface. However, I have lost all of the management scripts that aggregate and drop data. These will be critical as the new database server has a substantially smaller drive. There is a larger attached drive, and I will try and mount the data there.

It will likely take more time than I have at the moment to maintain and restore GrabPERF to its pre-existing state. You can expect serious outages and changes to the system in the next few weeks.

[Whining removed. Self-inflicted injuries are always the hardest to bear.]

UPDATE – Sep 5 2009 03:30 GMT: The Database is back up, and absorbing data. Attempts to move it to the larger drive on the system failed, so the entire database is running on an 11GB partition. <GULP>.

The two most vital maintenance scripts are also running the way they should be. I had to rewrite those from very old archives.

Status: Good, but not where I would like it. I will work with Technorati to see if there is something that I’m missing in trying to use the larger partition. Likely it comes down to my own lame-o linux admin skillz.

I want to thank the ops team from Technorati for spending time on this today. They did an amazing job of finding a machine for this database to live on in record time.
I have also learned the hard lesson of backups. May I not have to learn it again.

UPDATE – Sep 5 2009 04:00 GMT: Thanks again to Jerry Huff at Technorati. He pointed out that if I use a symbolic link, I can move the db files over to the large partition with no problem. Storage is no longer an issue.

[And, why you ask, is Tara Hunt (@missrogue) on this post. Hey, when I asked Tagaroo for Technorati images, this is what it gave me. It was a bit of a shock after 8 hours of mind-stretching recovery work, but hey, ask and ye shall receive.]

UPDATE – Sep 7 2009 01:00 GMT: Seems that I got myself into trouble by using the default MySQL configuration that came with the CentOS distro. As a result, I ran out of database connections! Something that I have chided others for, I did myself.
The symptom appeared when I reactivated my logging database, which runs against the same MySQL installation, just in a separate database. It started to use up the default pool of connections (100) and the agents couldn’t report in.

This has been resolved and everything is back to normal.

GrabPERF: What and Why

2008-12-01 / spierzchala / 0 Comments

Why GrabPERF?

About four years ago, I had a bright idea that I would like to learn more about how to build and scale a small Web performance measurement platform. I’ve worked in the Web performance industry for nearly a decade now, and this was an experimental platform for me to examine and encounter many of the challenges that I see on a daily basis.

The effort was so successful and garnered enough attention during the initial blogging boom that I was able to sell the whole platform for a tiny (that is not a typo) sum to Technorati.

The name is taken from another experimental tool I wrote called GrabIT2 which uses the PHP cURL libraries to capture timings and HTML data for HTTP requests. It is an extension of my articles and writings on Web performance that started at Webperformance.org, and that have since moved to this blog.

What is GrabERF?

GrabPERF is a multi-location measurement platform, based on PERL, cURL, PHP, and MySQL that is designed to

Measure the base HTML or a single-object target using HTTP or HTTPS
Report the data to a central database (located in the San Francisco Area)
Report the data using a GUI or through text based download

Why not Full Pages with all Objects?

Reason 1: I work for a company that already does that. Lawyers and MBAs among you, do the math.

Reason 2: I am an analyst, not a programmer. The best I can say about my measurement script is hack job.

Why is the GrabPERF interface so clunky?

See reason 2 above.

If you want to write your own interface to the data, let me know.

Why has the interface not changed in nearly three years?

The current interface works. It’s simple, clean, and delivers the data that I and the regular users need to analyze performance issues. If there is something more that you would like to see, let me know!

I like what I see. How can I host a measurement location?

Just contact me, and I can provide you with a list of PERL modules you will need to install on your linux server. In return, I need a static IP address of the machine hosting the measurement agent.

How stable is GrabPERF?

Most of the time, I forget it’s even running. I have logged onto the servers and typed in uptime and discovered that it’s been 6 months or more since the servers have been re-booted.

It was designed to be simple, because that’s all I know how to do. The lack of complexity makes it effectively self-managing.

Shouldn’t all systems be that way?

What if my question isn’t asked / answered here?

Your should know the answer to this by now: contact me.

GrabPERF: Why EXPLAIN is such a useful SQL tool

2005-09-14 / spierzchala / 0 Comments

Now, EXPLAIN seems like such an old school SQL tool. Well, I am here to explain why it’s not something you should ignore.

The Index Chart queries were all doing table scans. This can be pretty painful; ok, this can be stupid and EXTREMELY painful. The cause: I was setting sub-optimal date ranges on my queries and making all kinds of crazy date_format calls to format the dates IN THE QUERIES.

Once I changed the code, even when the query cache is cleared, the Index Charts now all run at less than 1 second.

Colour me stupid.

Making PHP-to-MySQL Connections Persistent

2005-06-29 / spierzchala / 0 Comments

I have been seeing these bursts of traffic, mainly from spambot morons, that have suddenly been crushing my server. The main cause: excessive database connections.

This was quickly remedied today when I changed all of the mysql_connect statements to mysql_pconnect statements. This allows PHP to use an existing connection to the MySQL database to serve requests from the same Apache child process.

Now the truly geeky among you are going “DOH! Wadda ya mean you were opening a new connection for every request?”. Well, believe it or not, I will bet you dollars to doughnuts that your blog app doesn’t persist database connections. Not a big deal if your database is on the same machine, and you are using local named pipes to make requests. However, if that database is located on another machine, if you do a netstat, you will see a large number of connection on port 3306.

Persisting database connections is particularly important for large hosted services. A great deal of TCP overhead, and kernel space memory can be saved by simply not letting the Web server saturate the database with individual database connections for every page request.

Without persistent database connections, eventually the TCP queue will be full of database connections and no one will be able to connect to the server, or they will get a lovely “can’t connect to database error”.

MySQL: I was NOT losing my mind…ok, not this time

2005-05-17 / spierzchala / 0 Comments

I noticed that a new version (4.1.12) of MySQL was up on their site last night. I grabbed the RPMs (Yes, I am a binary-loving weenie, not a hardcore source jockey) and installed them.

For those of you who install MySQL from RPMs know that it takes 4 packages to get all of the components up and running correctly. I got 3 of the 4 running no problem.

The one that bombed is the one that contains the main server binaries. All sorts of backtrace and coredump type errors, and then no response from the DB. So I re-installed the previous Server RPM, and I am up and running. I just figured I am an idiot and moved on with my evening.

This morning, I went to the MySQL site. Lo and behold, all of the 4.1.12 downloads have been pulled.

I don’t feel like such an idiot anymore. And I am not alone: here are the Severity 1 bugs for MySQL 4.1.12. Two of them are identical to what I was seeing.