Posts Tagged ‘mysql’

Dreamhost and MySQL

Posted November 29th, 2009 at 15:02 CST in Web Development | Tags: , | Leave a Comment »

At the company I work for, any amount of downtime on a website means we lose money, no exceptions. We run thousands of websites off just 6 or 7 severs and each of our popular sites get thousands of visits per day on average. This is easily accomplished by using dedicated servers and having a simple master-slave MySQL setup with writes only going to the master. Of course we went with a really cheap stupid company and absolutely no support, but we control our servers, so its all good.

For my personal sites, I do not have the luxury of having a dedicated sever; instead I use Dreamhost. Support at Dreamhost is phenomenal—I could not ask for anything better. But I feel ripped-off by their actual service. Using shared hosting is always a gamble, but Dreamhost goes one further. Web servers are physically separated from database servers.

From the perspective of a System Admin, separating web from DB is a great idea. Web servers need different setups than database servers, and management would be much easier with them separate. However, the web developer perspective looks at processing time more than anything else, and unless a network is setup to be super fast, latency is going to be killer. But since this is a respectable web hosting company I would not expect anything less.

So, assuming they know what they are doing, the only other explanation for why my page processing time is seconds instead of tenths of seconds is that server load is terrible, once again pointing to inadequacy of service.

Even so, I stay with them if nothing else because they are the cheapest web host to provide SSH access and the flexibility to use PHP, Python or whatever I want.

The Importance of EXPLAIN and SQL_NO_CACHE in Query Development

Posted October 7th, 2009 at 23:17 CST in SQL | Tags: , , | Leave a Comment »

On average, I guess 35% of my over time at work involves MySQL query building, optimizing and re-writing. I find it an enjoyable challenge, and recently I re-worked an existing framework for a new site which put my focus almost solely on SQL, queries, and the schema.

With a normalized database schema, SQL optimization tends to focus on aspects outside the query, like making sure indexes get put in the currect locations. But I happen to be stuck with large and slightly-non-normalized tables. So, in order to write queries against these tables for a site that averages 30k hits per month, I need to be able to see what MySQL is doing with each query. For this I would be lost without EXPLAIN and SQL_NO_CACHE.

I don’t like writing tutorials, and I don’t really have time. But I love linking to great documentation. Most of what I know about MySQL I learned from their documentation, and I encourage even experienced query writers to make sure they know the importance of testing query times with SQL_NO_CACHE or using a development environment with caching completely disabled. When it comes to web applications, even microseconds can hurt in the long run.

“New” MySQL features

Posted August 6th, 2009 at 01:34 CST in SQL | Tags: , | Leave a Comment »

The more I use MySQL, the more I love it. I recently reviewed documentation for triggers when I ran into a pickle that needed to be solved in a short amount of time: ordering a query by TIMESTAMP.

I had a table with approx. 70k rows which had data continually inserted into it via cron job. I needed to query this table for the most recently inserted data given certain values. After re-organizing indexes and converting the table to InnoDB from MyISAM I was about to give up since (surprisingly) none of it gave any real improvement. The fundamental part of this query (the subquery in the SELECT expression which was ran over and over) was taking on average 0.022 seconds when ordering by a TIMESTAMP field which was automatically inserted by MySQL. When I used ORDER BY UNIX_TIMESTAMP(`field`) DESC LIMIT 1 instead, the query took 0.001 seconds on average. Since I didn’t wan’t MySQL converting it every time (really I don’t know what it does with times internally), I looked up some documentation and saw triggers. It saved my query and the table. I was able to make an additional integer field in the table and set a trigger on insert that did the conversion and stored the unix timestamp in the new field, then I could query the new unix timestamp field all I want and speed up this query even more.

It was an epiphany. Suddenly I saw SQL problems under a different light. I was even able to solve another complex problem by creating an event which would build rebuild a VIEW every hour. It was awesome until I discovered the production MySQL server was running 5.0 which doesn’t have events and I suspect handles VIEWs much more poorly than 5.1 which I was running locally. Of course I have no control over this server, and it handles uber-important data, so there is absolutely nothing I can do about it. So unfortunately I had to go back to the drawing board with that one.

Using LIMIT

Posted April 10th, 2009 at 13:08 CST in SQL | Tags: , | Leave a Comment »

The last couple weeks I’ve had to focus on building and optimizing MySQL select statements. It’s been an interesting journey as I’ve learned about choosing optimal field types when building tables, when to use a subquery over a left join, and how to use the information procedure analyse() gives. But the one thing I did not expect was the extreme performance increase LIMIT gives when used appropriately. Looking back, I should have guessed it, since when you limit a query to only retrieving a specified amount of rows instead of an indefinite amount, MySQL has to do a lot less work.

Today’s challenge though is a little different; I’m working with an existing table which has text fields as indexes along with integer indexes and primary key. This quarter million row table needs to be checked against for data, but I am only able to check against indexes on varchar(80) fields and not unique keys, so those queries are running orders of magnitude slower than the others. Unfortunately, this data is used in many other locations, so I am not able to do anything with it right now.