14

How To Fix Intermittent MySQL Errcode 13 Errors On Windows


Posted by Artem Russakovskii on January 5th, 2010 in Databases, MySQL, PHP, Programming

Updated: September 16th, 2012

13

The Problem

I've had MySQL on my Windows 7 laptop for a bit (as part of wampserver), mostly for local offline WordPress development.

However, even though MySQL is relatively stable, I've been observing a vast quantity of intermittent MySQL errors, as reported by WordPress in the PHP error log (C:\wamp\logs\php_error.log). Here are some examples:

[05-Jan-2010 09:47:51] WordPress database error Error on delete of
'C:\Windows\TEMP\#sql17e0_1a2_6.MYD' (Errcode: 13) for query SELECT t.*, tt.*
FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id =
tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (3)
ORDER BY t.name ASC made by require, require_once, include, get_footer,
locate_template, load_template, require_once, dynamic_sidebar,
call_user_func_array, widget_rrm_recent_posts, RecentPosts->execute,
ppl_expand_template, 

Share

Updated: September 16th, 2012

In the past few weeks I've been implementing advanced search at Plaxo, working quite closely with Solr enterprise search server. Today, I saw this relatively detailed comparison between Solr and its main competitor Sphinx (full credit goes to StackOverflow user mausch who had been using Solr for the past 2 years). For those still confused, Solr and Sphinx are similar to MySQL FULLTEXT search, or for those even more confused, think Google (yeah, this is a bit of a stretch, I know).

Similarities

  • Both Solr and Sphinx satisfy all of your requirements. They're fast and designed to index and search large bodies of data efficiently.
  • Both have a long list of high-traffic sites using them (Solr, Sphinx
Share

Updated: September 16th, 2012

Introduction

StackOverflow is an amazing site for coding questions. It was created by Joel Spolsky of joelonsoftware.com, Jeff Atwood of codinghorror.com, and some other incredibly smart guys who truly care about user experience. I have been a total fan of SO since it went mainstream and it's now a borderline addiction (you can see my StackOverflow badge on the right sidebar).

The Story

Update 6/21/09: This server is currently under very heavy load (10-200), even with caching plugins enabled. Please bear with me as I try to resolve the situation.

Feel free to bookmark this page and return to it later when the fires have been put out.

Update 06/21/09: I think I've got the situation …

Share

Introduction

A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

Solutions Suggested By Others

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
DELETE a FROM a INNER JOIN b on a.id=b.id;
DELETE FROM a WHERE id IN (SELECT id FROM 

Share

Updated: June 9th, 2009

Introduction

If you, like me, are building or thinking of implementing a MySQL-powered application that has any need for prioritizing selecting certain data over other data, this article is for you.

Example

As a real world example, consider a queue-like video processing system. Your application receives new videos and processes them. The volume of incoming videos can at times be higher than the processing rate because the process is CPU bound, so occasionally a pretty long queue may form. You will try to process them as fast as you can but…

Note that I am using a queue here, so the the next item to be processed is a result of sorting by some sort of field in a ascending

Share
30

Swapping Column Values in MySQL


Posted by Artem Russakovskii on February 17th, 2009 in Databases, Linux, MySQL, Programming

Updated: September 16th, 2012

Today I had to swap 2 columns in one of my MySQL tables. The task, which seems easily accomplishable by a temp variable, proved to be a bit harder to complete. But only just a bit.

Here are my findings:

  1. The

    UPDATE swap_test SET x=y, y=x;

    approach doesn't work, as it'll just set both values to y.

    PostgreSQL seems to handle this query differently, as it apparently uses the old values throughout the whole query. [Reference]
  2. Here's a method that uses a temporary variable. Thanks to Antony from the comments for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values

Share

imageEveryone and their mother are throwing out their predictions for 2009 nowadays, it’s a new fad. It’s like you’re not cool anymore if you don’t have twitter, a Mac, and a set of random predictions for the next 12 joyous months.

So I decided to throw in a few ideas of my own to be part of the cool crowd again (how much cooler can I be already, you might think, and I wouldn’t blame you).

 

Disclaimer (read it, tough guy)

What this post is:

  • about the future of technology and the Internet, 2009 and beyond.
  • my ideas on what is going to happen or should happen. If they happen to match someone else’s ideas – it doesn’t mean
Share
1

Hadoop Primer – Yet Another Hadoop Introduction


Posted by Artem Russakovskii on October 20th, 2008 in Databases, Programming

image I just came upon a pretty good Hadoop introduction paper posted on Sun’s wiki. Apache Hadoop is a free Java software framework that supports data intensive distributed applications. It enables applications to work with thousands of nodes and petabytes of data. Hadoop was inspired by Google's MapReduce and Google File System (GFS) (wikipedia). I wouldn’t call it an alternative to mysql – they’re in completely different weight categories. I like to think of Hadoop as a complement – I think it’s closer to memcached in its functions than to mysql. Perhaps a hybrid of both but a unique beast nonetheless. If you’re serious about scaling, you owe it to yourself to start exploring Hadoop yesterday.

A couple of …

Share
13

MySQL Slave Lag (Delay) Explained And 7 Ways To Battle It


Posted by Artem Russakovskii on September 5th, 2008 in Databases, Programming

Updated: September 16th, 2012

http://forge.mysql.com/w/images/1/1e/Dolphin_Laptop_cropped-386x222.jpgSlave delay can be a nightmare. I battle it every day and know plenty of people who curse the serialization problem of replication. For those who are not familiar with it, replication on MySQL slaves runs commands in series – one by one, while the master may run them in parallel. This fact usually causes bottlenecks. Consider these 2 examples:

  • Between 1 and 100 UPDATE queries are constantly running on the master in parallel. If the slave IO is only fast enough to handle 50 of them without lagging, as soon as 51 start running, the slaves starts to lag.
  • A more common problem is when one query takes an hour to run (let's say, it's an UPDATE with a
Share
21

Best MySQL Server Under $10K?


Posted by Artem Russakovskii on June 11th, 2008 in Databases

Updated: January 4th, 2009

Server picture I want to get opinions from outside of my daily circle of people on the best server hardware to use for MySQL. I remember from the conference somebody (Pipes?) mentioning a particular Dell server with multiple disk RAID10 that could supposedly be had for about $6k but I completely misplaced the model number (Frank, did you get my email?).

I know that a multi-disk RAID array with a bunch of fast disks (15k RPM?) is probably the most important method of improving performance, followed by the amount of RAM, so I'm trying to find the best combination/balance of the two. However, server prices on the Internet range so much that I don't even know where to begin to tell a …

Share

Updated: June 1st, 2008

Recently I ran into major problems using GNU diff. It would crash with "diff: memory exhausted" after only a few minutes trying to process the differences between a couple 4.5GB files. Even a beefy box with 9GB of RAM would run out of it in minutes.

There is a different solution, however, that is not dependent on file sizes. Enter rdiff – rsync's backbone. You can read about it here: http://en.wikipedia.org/wiki/Rsync (search for rdiff).

The upsides of rdiff are:

  • with the same 4.5GB files, rdiff only ate about 66MB of RAM and scaled very well. It never crashed to date.
  • it is also MUCH faster than diff.
  • rdiff itself combines both diff and patch capabilities, so you can create deltas

Share
6

Sun Definitely Developing A Phone This Year


Posted by Artem Russakovskii on April 21st, 2008 in Beer Planet, Databases, Technology

JavaFX_Mobile One thing that still springs to mind when I think of the MySQL User Conference last week is Sun's opening keynote. While talking about Sun's market penetration with open source software, Jonathan Schwartz, Sun's CEO, slipped in a short mention of the mobile market saying something along the lines of "Sun is going to be entering the mobile market later on this year". He didn't spend more than 5 seconds talking about it, moving on to the acquisition of MySQL.

Last year, Sun already made an announcement of JavaFX, a Java-based mobile platform but didn't provide any concrete timelines, so I was excited to hear the more on the subject. With Apple iPhone's advent last year and …

Share

  • the database
  • CPU
  • memory
  • IO
    • disk latency
    • network latency
  • slow queries
  • media size deployment example
    • 300 platforms (300 remote agents collecting data)
    • 2,100 servers
    • 21,000 services (10 services per server), sounds feasible
    • 468,000 metrics (20 metrics per service)
    • 28,800,000 metric data rows per day
    • larger deployments have a lot more of these (sounds crazy)
  • data
    • measurement_id
    • timestamp
    • value
    • primary key (timestamp, measurement_id)
  • data flow
  • Share
    0

    MySQL Conference Liveblogging: MySQL Hidden Treasures (Thursday 11:55PM)


    Posted by Artem Russakovskii on April 17th, 2008 in Databases

    • Damien Seguy of Nexen Services presents
    • easiest session of all (phew, that's a relief)
    • clever SQL recipes
    • tweaking SQL queries
    • shows an example where SELECT is ORDERED by a column that is actually an enum.
    • an enum is both a string and a number
    • sorted by number
    • displayed as string
    • can be sorted by string if it's cast as string
  • compact column
    • compacts storage
    • faster to search
    • if (var)char is turned into enum, some space can be saved, shows example
  • random order
    • order by rand(1) – obviously
    • the integer parameter is actually a seed
    • really slow, also obviously, especially for larger tables because it has to order first, then apply rand() to the list
    • another solution is to add an
    Share
    6

    MySQL Conference Liveblogging: Monitoring Tools (Wednesday 5:15PM)


    Posted by Artem Russakovskii on April 16th, 2008 in Databases

    Updated: April 18th, 2008

    • Tom Hanlon of MySQL presents
    • monitoring tool basics
      • SHOW FULL PROCESSLIST
      • SHOW GLOBAL STATUS
      • SHOW GLOBAL VARIABLES
    • basic tools
      • mysqladmin is provided with the server
        • mysqladmin -i 10 extended status: will repeat the same command every 10 seconds. Pipe through grep "and smoke it" (bad pun, hah hah)
        • -r: show only changed values
      • MySQL Administrator
    • cacti
      • rrdtool based network graphing tool
      • uses snmp
      • PHP apache and MySQL based solution
      • MySQL plugins, download and install
      • "poller" gathers data and populates the graphs
      • someone offers munin as an alternative
        • not snmp based, its own agent is used
      • pros
        • cacti is fairly easy to configure
      • cons
        • could be CPU intensive with lots of machines (Perl polling seems to be the
    Share