Announcing Sun MySQL!
January 16, 2008 | Leave a Comment
Sun has officially acquired MySQL! As a huge fan and supporter of MySQL, I have personal commitment and obvious concerns about this. In my experience most things Sun does are bloated and slow to develop (Solaris, Java, Open Office). Yet, I remain hopeful because they do seem to respect and work positively with open source initiatives.
The most positive things can come out of this are improved marketing, better name recognition, and funds injected into a project. Maybe they can compete with Oracle now? Hey, maybe Sun will stop using Berkeley DB to back most it’s existing products. I’ll admit that upon first reading this my heart skipped a few beats, but as the pains subsided I think its all going to be OK.
Tags: database, java, lamp, mysql, openoffice, oracle, solaris, Sun, sun microsystems
Dotster VPS Upgrade to PHP 5 and MySQL 5
May 7, 2007 | 8 Comments
I recently purchased a Dotster VPS and needed to get it upgraded to have PHP 5 and MySQL 5, rather than version 4 of both. These machines run CentOS, so the best plan was to use yum.
Unfortunately, yum is not installed initially, so the first step was to get and install all the prerequisites plus yum:
export MIRROR=http://mirror.centos.org/centos/4/os/i386/CentOS/RPMS/
wget ${MIRROR}python-sqlite-1.1.7-1.2.i386.rpm
wget ${MIRROR}sqlite-3.3.3-1.2.i386.rpm
wget ${MIRROR}python-urlgrabber-2.9.8-2.noarch.rpm
wget ${MIRROR}python-elementtree-1.2.6-4.2.1.i386.rpm
wget ${MIRROR}yum-2.4.3-1.c4.noarch.rpm
rpm -Uhv python-elementtree-1.2.6-4.2.1.i386.rpm
rpm -Uhv python-urlgrabber-2.9.8-2.noarch.rpm
rpm -Uhv sqlite-3.3.3-1.2.i386.rpm
rpm -Uhv python-sqlite-1.1.7-1.2.i386.rpm
rpm -Uvh yum-2.4.3-1.c4.noarch.rpm
Once that is in place, you need to enable CentOS Plus. Go into /etc/yum.repos.d/CentOS-Base.repo and under [centosplus] change “enabled=0″ to “enabled=1″.
Now you can simply execute ‘yum update’ and it should install both MySQL 5 and PHP 5 with all the necessary prerequisites (plus a number of other things…)
Update: A few other things to do:
- Remove /etc/php.d/ioncube-loader.ini This file will cause weirdness with PHP and throw “Failed loading /usr/lib/php4/php_ioncube_loader_lin_4.3.so: /usr/lib/php4/php_ioncube_loader_lin_4.3.so: undefined symbol: zend_hash_add_or_update” in /var/log/httpd/error_log
- Change php.ini memory_limit to be increased from the default of 8M, I recommend at least 32MB
- Tune your MaxClients setting in /etc/httpd/conf/httpd.conf I couldn’t begin to make a recommendation here…
Do this at your own risk, this could mess up your entire system. Be sure to have a good backup before starting this process.
Tags: cent os, centos, centos plus, centosplus, dotster, dotster vps, mysql, mysql 5, php, php 5, unix, vps, yum
MySQL Performance Blogs
March 7, 2007 | 2 Comments
I’ve recently been doing a lot of work with performance tuning in MySQL. Here are a few blogs that I have found highly useful by people who are involved in some of the most serious MySQL based projects on the internet.
Peter Zaitsev and Vadim Tkachenko: MySQL Performance Blog
Zaitsev used to work as manager of High Performance Group at MySQL. Now he and Tkachenko run a consultant business specializing in high performance MySQL. He is common on the presentation circuit and has made his presentations all available. Tkachenko worked as a performance and scalability engineer under Zaitsev at MySQL. This blog contains great benchmarking and deep understanding of the MySQL infrastructure.
Domas Mituzas: Vaporware, Inc
Mituzas currently works for MySQL and is responsible for much of the scaling and performance tuning for Wikipedia. He also worked to make WordPress much more efficient in the latest versions. His blog has some examples and tips for scaling and performance tuning. Since he has such solid real world experience with Wikipedia, these examples and explanations are truly invaluable to understanding how to work with MySQL in amazingly efficient ways.
Jeremy Zawodny: Jeremy Zawodny’s blog
Zawodny works at Yahoo and is responsible for the amazing MySQL administration tool mytop. When it comes to MySQL, Zawodny is likely the best recognized name. He also wrote the O’Reilly book, High Performance MySQL. He also has some informative presentations available, but they are definitely starting to become dated.
If anyone else knows of great MySQL blogs, specifically that apply to performance tuning, scaling, and optimization, please let me know.
Tags: blog, blogs, Domas Mituzas, jeremy zawodny, mituzas, mysql, mytop, O'Reilly, performance, performance blog, Peter Zaitsev, presentations, scalability, scaling, tuning, Vadim Tkachenko, vaporware, wikipedia, yahoo, zawodny
Armenian Junior Web Developer Falls Off A Mountain
June 22, 2006 | 1 Comment
Tim Haroutunian is working with us this summer in ITS at Plymouth State University as our Jr. Web Developer. Tim is an interesting character. He is quite proud of his Armenian heritage and at lunch explained in excruciating length about how while visiting Armenia he fell off a mountain. This story took a good 30 minutes to tell, but is summarized in it’s entirely above in the diagram. The diagram was brilliantly composed by Cliff.
Over the course of the summer we have all conspired to throw insane amounts of harrassment in hopes of thickening Tim’s skin. So far he is taking it well, we’ll find his breaking point though I’m sure.
(In quick defense of Tim, he is shaping up to be a pretty kick-ass PHP/Ajax/MySQL programmer)
Tags: AJAX, armenia, armenian, cliff, mysql, php, time haroutunian, web developer
A Couple MySQL Administrative Queries
March 27, 2006 | 1 Comment
There are a few tasks in MySQL which I do rarely and usually graphically. This in turn leads to sifting through lots of documentation to refresh my memory. Not anymore… here are those commands so I’ll always know exactly where to find them.
First is a basic command for granting privileges to a user, the example shows all privs from a specific host to a user from a specific host including the ability to grant further privs to others:
GRANT ALL ON * TO 'username'@'hostname' IDENTIFIED BY 'password' WITH GRANT OPTION;
The above is not a good thing to do willy nilly. The command creates a superuser, though sometimes you need these…
The next thing i do more regularly these days is slip tuning configuration parameters in during runtime. This is useful if you’ve experienced some significant growth and want to tune you db without a restart. Keep in mind these settings will be lost if the database is restarted:
SET GLOBAL wait_timeout=60;
Tags: code, grant, mysql, set global, sql
PHP strtotime Limitation
September 20, 2005 | 5 Comments
I have been relying very heavily on strtotime() in PHP in just about everything I write. For those not entirely familiar with this function, strtotime() will "parse about any English textual
Why create Unix date() date('m/d/Y'); will generate a reasonably nice US representation of today's date. However, if you did date('m/d/Y',strtotime('3 weeks ago')); you can get the nice readable format provided by date, but for some arbitrary date you are deciding upon with strtotime. Also, knowing strtotime happily accepts the date('m/d/Y',strtotime($row['date_field']));. For a complete list of the available date formats, visit
Ok, so the title of this makes reference to a limitation... on to that. On some
In the meantime, safestrtotime() function and shared it on PHP.net. I'm reproducing it here for my convenience, altered to match the code formatting I prefer.
-
<?php
-
-
function safestrtotime($strInput)
-
{
-
$iVal = -1;
-
for ($i=1900; $i<=1969; $i++)
-
{
-
// Check for this year string in date
-
$strYear = (string)$i;
-
{
-
$replYear = $strYear;
-
$yearSkew = 1970 - $i;
-
}
-
}
-
if ($yearSkew> 0)
-
{
-
$numSecs = (60 * 60 * 24 * 365 * $yearSkew);
-
$iVal = $iVal - $numSecs;
-
$numLeapYears = 0; // determine number of leap years in period
-
for ($j=$replYear; $j<=1969; $j++)
-
{
-
$thisYear = $j;
-
$isLeapYear = false;
-
// Is div by 4?
-
if (($thisYear % 4) == 0)
-
{
-
$isLeapYear = true;
-
}
-
// Is div by 100?
-
if (($thisYear % 100) == 0)
-
{
-
$isLeapYear = false;
-
}
-
// Is div by 1000?
-
if (($thisYear % 1000) == 0)
-
{
-
$isLeapYear = true;
-
}
-
if ($isLeapYear == true)
-
{
-
$numLeapYears++;
-
}
-
}
-
$iVal = $iVal - (60 * 60 * 24 * $numLeapYears);
-
}
-
return $iVal;
-
}
-
?>
Tags: 1970-01-01, date format, datetime, Ed Lecky-Thompson, epoch, function, mysql, operating systems, PHP.net, timestamp, timestamps, Unix Epoch, Unix timestamp
ADOdb - PHP Database Abstraction
August 27, 2005 | 1 Comment
Anyone who has used more than one database (MySQL, PostgreSQL, SQL Server, Oracle, etc) with PHP realizes it is cumbersome. In fact the developers of PHP have recently realized this as well. In 5.1 they are planning to include PHP Data Objects (PDO). However, in the meantime I recommend ADOdb.
Unfortunately, PHP's database access functions are not standardised. Every database extension uses a different and incompatibile API. This creates a need for a database class library to hide the differences between the different databases (encapsulate the differences) so we can easily switch databases.
Example connection to mySQL:
-
$db = NewADOConnection('mysql'); // type of connection
-
$db->SetFetchMode(ADODB_FETCH_ASSOC); // sets the query results to come back as assoc arrays
-
$db->PConnect('mysql_db_host', 'some_user', 'some_pass', 'the_db'); // make the connection
Example connection to Oracle:
-
<?php
-
$db = NewADOConnection('oci8'); // type of connection
-
$db->SetFetchMode(ADODB_FETCH_ASSOC); // sets the query results to come back as assoc arrays
-
$db->PConnect('oracle_db_host', 'jpseudo', 'his_password', 'oracle_sid'); // make the connection
-
?>
Comparing the two examples it's clear how similar connecting to either database becomes. Also notice we're setting the fetch mode to associative so we get back friendly associative arrays. The following is an example of how to query and get back results:
-
<?php
-
$query = "SELECT * FROM tablename";
-
$res = $db->Execute($query);
-
while($row = $res->FetchRow())
-
{
-
// $row is an associative array, indexed by fieldname from the table
-
}
-
?>
This will get back a bunch of rows of data, You might want less, like a single row or single field of a single row.
-
<?php
-
$row = $db->GetRow("SELECT * FROM tablename WHERE id_field='single_value'"); // this returns an associative array of the single row
-
$a_field = $db->GetRow("SELECT a_field FROM tablename WHERE id_field='single_value'"); // this returns the value of single field of the single row
-
?>
More Resources:
ADOdb Database Abstraction Library for PHP Main Site
ADOdb Documentation
Alternate Abstraction Library: PEAR DB
Tags: adodb, database, database abstraction, databases, mysql, oracle, pdo, php, programming