The current US financial situation has claimed a victim in the Web 2.0 world — Uber. I’m not sure if this is the first significant name, but it will not be the last site running MySQL where investors will be quick to cut losses and move on.
Every website page content uses two basic elements, HyperText Markup Language (HTML) and Cascading Style Sheets (CSS). Each of these has various standards, HTML has versions such as 3.2, 4.0, 4.01, and the new XHTML 1.0,1.1, 2.0 along with various version flavors know as strict, transitional & frameset. CSS also has various versions including 1, 2 and 3.
Each browser renders your combined HTML & CSS differently. The look and feel can vary between FireFox, Safari, Chrome, Internet Explorer and the more less common browsers. Indeed each version of a product also renders different. With IE 8 just being released, it’s common versions now are 5.0, 5.5, 6.0 and 7.0. This product alone now has 5 versions that UI designers must test and verify.
To minimize presentation and rendering problems, adhering to the standards can only assist, and greatly benefit the majority of entrepreneurs, designers and developers that are not dedicated resources. There are two excellent online tools from the standards body, the World Wide Web Consortium (W3C) that an easily assist you.
You can also link directly to these sites, so it’s easier to validate your HTML and CSS directly from your relevant webpage. For example, HTML Validation and CSS.
It’s not always possible to meet the standards, and when you are not the full-time developer of your site, it can be time consuming if you don’t check early and regularly.
Indexes are a great way to improve performed in a MySQL database, when used appropriately.
When used in-appropriately the impact can be a degradation of performance.
The following example from Movable Type shows how when reviewing the slow query log I found numerous occurrences of Inserts take 3 or more seconds, with no reported lock contention time for this insert.
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET insert_id=6281; INSERT INTO mt_comment (comment_author, comment_blog_id, comment_commenter_id, comment_created_by, comment_created_on, comment_email, comment_entry_id, comment_ip, comment_junk_log, comment_junk_score, comment_junk_status, comment_last_moved_on, comment_modified_by, comment_modified_on, comment_parent_id, comment_text, comment_url, comment_visible) VALUES (...)
The impact here, is that SELECT statements to the mt_comment table are also blocked because this table is in MyISAM. It was reviewing slow running SELECT statements that the cause of the slow inserts was easily determined.
mysql> explain SELECT comment_id
-> FROM mt_comment
-> WHERE (comment_visible = '1') AND (comment_blog_id = '3') AND (comment_entry_id = '276')
-> ORDER BY comment_created_on DESC;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mt_comment
type: ref
possible_keys: mt_comment_visible,mt_comment_entry_id,mt_comment_blog_id,mt_comment_blog_stat,mt_comment_visible_date,mt_comment_entry_visible,mt_comment_blog_visible,mt_comment_blog_ip_date,mt_comment_blog_url
key: mt_comment_entry_visible
key_len: 6
ref: const,const
rows: 99
Extra: Using where
1 row in set (0.00 sec)
CREATE TABLE `mt_comment` (
`comment_id` int(11) NOT NULL auto_increment,
`comment_author` varchar(100) default NULL,
`comment_blog_id` int(11) NOT NULL default '0',
`comment_commenter_id` int(11) default NULL,
`comment_created_by` int(11) default NULL,
`comment_created_on` datetime default NULL,
`comment_email` varchar(75) default NULL,
`comment_entry_id` int(11) NOT NULL default '0',
`comment_ip` varchar(16) default NULL,
`comment_junk_log` mediumtext,
`comment_junk_score` float default NULL,
`comment_junk_status` smallint(6) default '0',
`comment_last_moved_on` datetime NOT NULL default '2000-01-01 00:00:00',
`comment_modified_by` int(11) default NULL,
`comment_modified_on` datetime default NULL,
`comment_parent_id` int(11) default NULL,
`comment_text` mediumtext,
`comment_url` varchar(255) default NULL,
`comment_visible` tinyint(4) default NULL,
PRIMARY KEY (`comment_id`),
KEY `mt_comment_commenter_id` (`comment_commenter_id`),
KEY `mt_comment_visible` (`comment_visible`),
KEY `mt_comment_junk_score` (`comment_junk_score`),
KEY `mt_comment_ip` (`comment_ip`),
KEY `mt_comment_parent_id` (`comment_parent_id`),
KEY `mt_comment_entry_id` (`comment_entry_id`),
KEY `mt_comment_email` (`comment_email`),
KEY `mt_comment_last_moved_on` (`comment_last_moved_on`),
KEY `mt_comment_created_on` (`comment_created_on`),
KEY `mt_comment_junk_status` (`comment_junk_status`),
KEY `mt_comment_blog_id` (`comment_blog_id`),
KEY `mt_comment_blog_stat` (`comment_blog_id`,`comment_junk_status`,`comment_created_on`),
KEY `mt_comment_visible_date` (`comment_visible`,`comment_created_on`),
KEY `mt_comment_entry_visible` (`comment_entry_id`,`comment_visible`,`comment_created_on`),
KEY `mt_comment_blog_visible` (`comment_blog_id`,`comment_visible`,`comment_created_on`,`comment_id`),
KEY `mt_comment_blog_ip_date` (`comment_blog_id`,`comment_ip`,`comment_created_on`),
KEY `mt_comment_junk_date` (`comment_junk_status`,`comment_created_on`),
KEY `mt_comment_blog_url` (`comment_blog_id`,`comment_visible`,`comment_url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
As you can see, the table has 18 indexes. This means that for every row inserted, 18 separate index inserts are required.
When adding an Index to a table, first determine the usage patterns that will use the index, consolidating indexes when possible and removing obvious duplicates (in the above example, the single column comment_blog_id is a classic duplicate index).
Adding an index will generally help SELECT performance, depending on cardinality, but will always impact INSERT,UPDATE and DELETE performance.
Another down side of too many indexes is the MySQL optimizer has much more work to do to eliminate beneficial indexes for every Query Execution Plan (QEP) that is undertaken.
Indeed I have seen worse, in one case a table with ~120 columns, move then 20 single column indexes AND a 3 part primary key summing 40 bytes in InnoDB. The impact was terrible for performance, with the Index size being 3x times the data size.
Ronald Bradford, Principal of 42SQL provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of specialized consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.
Choosing a domain name for your brand identity is the start. Protecting your domain name by registering for example .net, .org, and the many more extensions is one step in brand identity.
However a recent very unpleasant experience in New York, resulted in realizing some companies also register undesirable domain names. I was one of many unhappy people, mainly tourists as I was showing an Australian friend the sights of New York. We had chosen to use the City Sights NY bus line, but we caught with some 100+ people in a clear “screw the paying customers” experience.
I was really annoyed that my friend, only in New York for 2 days both had to experience this, and missed out on a night tour. I commented, I’m going to register citysightsnysucks.com, and share the full story of our experience, directing people to use Gray Line New York, which clearly by observation were providing the service we clearly did not get.
To my surprise, the domain name was already taken. To my utter surprise, the owner of the domain is the same as citysightsny.com. Did they do this by choice, or did another unhappy person (at least in 2006) register this, only to be perhaps threatened legally to give up the domain.
I would generally recommend in brand identity this approach, especially when select common misspellings and hyphenated versions if applicable can easily lead to a lot of domain names for your brand identity.
$ whois citysightsny.com Whois Server Version 2.0 Domain names in the .com and .net domains can now be registered with many different competing registrars. Go to http://www.internic.net for detailed information. Domain Name: CITYSIGHTSNY.COM Registrar: INTERCOSMOS MEDIA GROUP, INC. D/B/A DIRECTNIC.COM Whois Server: whois.directnic.com Referral URL: http://www.directnic.com Name Server: NS0.DIRECTNIC.COM Name Server: NS1.DIRECTNIC.COM Status: clientDeleteProhibited Status: clientTransferProhibited Status: clientUpdateProhibited Updated Date: 31-dec-2006 Creation Date: 28-nov-2004 Expiration Date: 28-nov-2011 Registrant: CitySights New York LLC 15 Second Ave Brooklyn, NY 11215 US 718-875-8200x103 Fax:718-875-7056 Domain Name: CITYSIGHTSNY.COM
$ whois citysightsnysucks.com Whois Server Version 2.0 Domain names in the .com and .net domains can now be registered with many different competing registrars. Go to http://www.internic.net for detailed information. Domain Name: CITYSIGHTSNYSUCKS.COM Registrar: INTERCOSMOS MEDIA GROUP, INC. D/B/A DIRECTNIC.COM Whois Server: whois.directnic.com Referral URL: http://www.directnic.com Name Server: NS.PUSHONLINE.NET Name Server: NS2.PUSHONLINE.NET Status: clientDeleteProhibited Status: clientTransferProhibited Status: clientUpdateProhibited Updated Date: 26-jun-2007 Creation Date: 11-aug-2006 Expiration Date: 11-aug- Registrant: CitySights New York LLC 15 Second Ave Brooklyn, NY 11215 US 718-875-8200x103 Fax:718-875-7056 Domain Name: CITYSIGHTSNYSUCKS.COM
Why you do not use GRANT ALL ON *.*?
I was with a client today, and after rebooting a MySQL 5.0.22 instance cleanly with /etc/init.d/mysqld service, I observed the following error, because you always check the log file after starting MySQL.
080923 16:16:24 InnoDB: Started; log sequence number 0 406173600 080923 16:16:24 [Note] /usr/libexec/mysqld: ready for connections. Version: ‘5.0.22-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution 080923 16:16:24 [ERROR] /usr/libexec/mysqld: Table ‘./schema_name/table_name’ is marked as crashed and should be repaired 080923 16:16:24 [Warning] Checking table: ‘./schema_name/table_name’
Now, I’d just added to the /etc/my.cnf a number of settings including:
myisam_recovery=FORCE,BACKUP
which explains the last line of the log file. When attempting to connect to the server via the mysql client I got the error:
“To many connections”
So now, I’m in a world of hurt, I can’t connect to the database as the ‘root’ user to observe what’s going on. I know that table it’s decided to repair is 1.4G in size, and the server is madly reading from disk. Shutting down the apache server that was connecting to the database is not expected to solve the problem, and does not, because connections must wait to timeout.
MySQL reserves a single super privileged connection, i.e. ‘root’ to the mysql server specifically for this reason, unless all the connections have this privilege. The problem, as often experienced with clients, is the permissions of the application user is simply unwarranted.
mysql> select host,user,password from mysql.user; +-----------+-------------+------------------+ | host | user | password | +-----------+-------------+------------------+ | localhost | root | 76bec9cc7dd32bc0 | | xxxxxx | root | | | xxxxxx | | | | localhost | | | | % | xxxxxxxxxxx | 0716d6776318d605 | | localhost | xxxxxxxxxxx | 0716d6776318d605 | | localhost | xxxxxxx | 6885269c4a550a03 | +-----------+-------------+------------------+ 7 rows in set (0.00 sec) mysql> show grants for xxxxxxx@localhost; +---------------------------------------------------------------------------------------+ | Grants for xxxxxxx@localhost | +---------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO xxxxxxx'@'localhost' IDENTIFIED BY PASSWORD '6885269c4a550a03' | | GRANT ALL PRIVILEGES ON `xxxxxxx`.* TO 'xxxxxxx'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
So the problem is ALL PRIVILEGES is granted to an application user. Never do this!
The solution is to remove all unused users, anonymous users, and create the application user with just the privileges needed.
DROP USER xxxxxxxxxxx@localhost; DROP USER xxxxxxxxxxx@'%'; DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES; DROP USER xxxxxxx@localhost; CREATE USER xxxxxxx@localhost IDENTIFIED BY 'xxxxxxx'; GRANT SELECT,INSERT,UPDATE,DELETE ON xxxxxxx.* TOxxxxxxx@localhost;
Domain names historically have been www.example.com, written also with the protocol prefix http://www.example.com, but in reality www. is optional, only example.com is actually needed.
www. is technically a sub-domain and sub-domains incur a small penalty in search engine optimization.
There is no right or wrong. What is important is that you choose one, and the other needs to be a 301 Permanent Redirect to the one you have chosen.
You also need to know that creating a server alias in your web server configuration, for example Apache or Tomcat is not a permanent redirect, in-fact it is technically duplicate content, with two web sites the same also incurring a penalty for search engine rating.
So what do the big players do. Here are a few.
Use www
Do not use www
Show duplicate content
Curiously youtube.com uses a 303 redirect, microsoft.com, stumbleupon.com and craigslist.org a 302 redirect.
How do you check? Use a CLI tool such as wget.
$ wget www.google.com --2008-09-22 19:56:48-- http://www.google.com/ Resolving www.google.com... 72.14.205.99, 72.14.205.103, 72.14.205.104, ... Connecting to www.google.com|72.14.205.99|:80... connected. HTTP request sent, awaiting response... 200 OK $ wget google.com --2008-09-22 19:57:56-- http://google.com/ Resolving google.com... 64.233.167.99, 64.233.187.99, 72.14.207.99 Connecting to google.com|64.233.167.99|:80... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: http://www.google.com/ [following] $ wget www.facebook.com –2008-09-22 20:07:59– http://www.facebook.com/ Resolving www.facebook.com… 69.63.178.12 Connecting to www.facebook.com|69.63.178.12|:80… connected. HTTP request sent, awaiting response… 200 OK Length: unspecified [text/html] $ wget facebook.com –2008-09-22 19:59:43– http://facebook.com/ Resolving facebook.com… 69.63.176.140, 69.63.178.11 Connecting to facebook.com|69.63.176.140|:80… connected. HTTP request sent, awaiting response… 301 Moved Permanently Location: http://www.facebook.com/ [following] $ wget digg.com –2008-09-22 20:10:47– http://digg.com/ Resolving digg.com… 64.191.203.30 Connecting to digg.com|64.191.203.30|:80… connected. HTTP request sent, awaiting response… 200 OK Length: 15322 (15K) [text/html] $ wget www.digg.com –2008-09-22 20:14:06– http://www.digg.com/ Resolving www.digg.com… 64.191.203.30 Connecting to www.digg.com|64.191.203.30|:80… connected. HTTP request sent, awaiting response… 301 Moved Permanently Location: http://digg.com/ [following] $ wget twitter.com –2008-09-22 20:26:18– http://twitter.com/ Resolving twitter.com… 128.121.146.100 Connecting to twitter.com|128.121.146.100|:80… connected. HTTP request sent, awaiting response… 200 OK Length: 2655 (2.6K) [text/html] $ wget www.twitter.com –2008-09-22 20:26:41– http://www.twitter.com/ Resolving www.twitter.com… 128.121.146.100 Connecting to www.twitter.com|128.121.146.100|:80… connected. HTTP request sent, awaiting response… 301 Moved Permanently Location: http://twitter.com/ [following]
Professionally, I prefer shorter and simpler without www.
References:
Gary Vaynerchuk spoke next at Web 2.0 NY on Building Personal Brand Within the Social Media Landscape.
He was hilarious. His video presentation is available online, to share with others. He is inspirational for new young entrepreneur and I’d love to see him talk at Ultra Light Startups
His talk was simply “Patience and Passion”.
Words of wisdom included.
In closing, you have to do what you love.
Next on the Web 2.0 keynote speaker list was Maria Thomas of www.etsy.com with her talk The DIY Guide to Growing a Company.
If never heard of Etsy before - Your place to buy & sell all things handmade, interesting site. Most companies start small, and stay small. Only 0.1 of 1% grow to any size (e.g. > $250 Million) Esty, this year has 100M in revenue, Amazon is about $2 billion.
The opening lines included the message “Don’t loose the essence of who you are, and what you want to achieve.” and the term Filotimo.
It was an interesting point about qualifications “I got my Internet Degree at Amazon.com, then building digital media business at NPR - National Public Radio.”
Some more quotes from this discussion.
Some points of reference from the next Web 2.0 keynote by Jason Fried of 37 Signals
The DNA of your company has to able to say no.
I needed a rest from my opening keynote review NY Tech 1995-2008. Opening Web 2.0 Expo NY Keynote but a few siginificant points from The Death of the Grand Gesture by Deb Schultz.
Web 2.0 Expo NY keynotes are happening today. Technology in use included CrowdVine which I’d not heard of, and plenty of Twitter feeds such as w2e_NY08.
The opening keynote was Fred Wilson from Union Square Ventures with his presentation New York’s Web Industry From 1995 to 2008: From Nascent to Ascendent .
Some stats, Seed and early stage deals.
Fred first asked “New York is not an alley. Call it Broadway, or just New York.”
Here is a summary of his history of New York Web Industry.
New York is now 1/3 of Silicon valley, compared to 1/8 of funded Internet companies.
One thing mentioned is a documentary called “We live in Public”. Some of the footage from 1999, is so early Big Brother.
I will be attending next week’s Web 2.0 Expo 2008 in New York.
Garys Guide has a schedule of the key events and off site associated event parties.
It will be a bit of a change from the typical MySQL Conferences and recent OSCON Conference I have attended this year.
The Keynote titles gives you an indication of the variety of talks expected.
While working for a client, I had need to produce canned results of certain different criteria, recording the result in a table for later usage, and keep the position within each result.
Knowing no way to do this via a single INSERT INTO … SELECT statement, I reverted to using a MySQL Stored Procedure. For example, using a sample I_S query and the following snippet:
...
DECLARE list CURSOR FOR SELECT select table_name from information_schema.tables where table_schema='INFORMATION_SCHEMA';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;
OPEN list;
SET result_position = 1;
SET done = FALSE;
lab: LOOP
FETCH list INTO table_name;
IF done THEN
CLOSE list;
LEAVE lab;
END IF;
INSERT INTO summary_table(val,pos) VALUES (table_name,result_position);
SET result_position = result_position + 1;
END LOOP;
However, in reviewing with another colleague after writing some 10+ different queries and SP loops, I realized that it is possible to record the position of each row in a result set using session variables, negating the need for all that code.
SET @rowcount = 0; SELECT table_name, @rowcount := @rowcount + 1 FROM information_schema.tables WHERE table_schema = 'INFORMATION_SCHEMA'; +---------------------------------------+----------------------------+ | table_name | @rowcount := @rowcount + 1 | +---------------------------------------+----------------------------+ | CHARACTER_SETS | 1 | | COLLATIONS | 2 | | COLLATION_CHARACTER_SET_APPLICABILITY | 3 | | COLUMNS | 4 | | COLUMN_PRIVILEGES | 5 | | ENGINES | 6 | | EVENTS | 7 | | FILES | 8 | | GLOBAL_STATUS | 9 | | GLOBAL_VARIABLES | 10 | | KEY_COLUMN_USAGE | 11 | | PARTITIONS | 12 | | PLUGINS | 13 | | PROCESSLIST | 14 | | PROFILING | 15 | | REFERENTIAL_CONSTRAINTS | 16 | | ROUTINES | 17 | | SCHEMATA | 18 | | SCHEMA_PRIVILEGES | 19 | | SESSION_STATUS | 20 | | SESSION_VARIABLES | 21 | | STATISTICS | 22 | | TABLES | 23 | | TABLE_CONSTRAINTS | 24 | | TABLE_PRIVILEGES | 25 | | TRIGGERS | 26 | | USER_PRIVILEGES | 27 | | VIEWS | 28 | +---------------------------------------+----------------------------+ 28 rows in set (0.01 sec)
Of course you need the all important SET before each query, if not specified however, the subsequent query does not result in an error, just NULL.
So all I needed was:
INSERT INTO summary_table(val,pos) SELECT table_name, @rowcount := @rowcount + 1 FROM information_schema.tables WHERE table_schema = 'INFORMATION_SCHEMA';
A simple and trivial solution.
DISCLAIMER:
How this performs under load, and how it is supported in different and future versions of MySQL is not determined.
Do you have a full time System Administrator? Do you have only a part-time SA, or none at all?
Packet General’s Data Security and PCI Compliance solutions run on a dedicated appliance, based on a “Just Enough Operating System” (JeOS) to minimize exposure.
This appliance actually improves not just the security of your data, but ensures your Operating System is secure and up to date. With only 4 services and a footprint < 600MB this is an ideal solution for running even a normal MySQL installation. Security upgrades can also be provided as an automated feature, eliminating the need for this management internally.
Tomorrow in the MySQL Webinar How to secure MySQL data and achieve PCI compliance which is being held Thursday, September 11, 2008, 10:00 am PST, 1:00 pm EST, 18:00 GMT we will be discussing this in more detail.
It started with del.icio.us/ (which now ironically redirects to http://delicious.com), and now it’s becoming more the trend to create a domain name with the extension included for effect.
With unique .com domains harder to come by, and dropping vowels like flickr.com so last generation, some countries must be trying to cash in on the success such as Tuvalu which has something like 10% of GDP from domain name sales of .tv.
Some recent names I’ve noticed are http://identi.ca, http://chi.mp and http://cyclo.ps.
I have even considered some recent projects using this new trend, but the combination of either the 2 letter extension not existing (For example .ld) or it’s not possible to get domains from a registrar (For example .er) it will take some time.
| Home |  |
 |
|
Professional Blog  |
 |
| Consulting | |
| Presentations | |
|
|
| About Ronald | |
| Related Links | |
| Contact Ronald | |
|
|
|
|
