|
Improve the performance of your MySQL Server
MySQL is a rock solid, lighting fast database server which
has been designed for two factors speed and performance. It is the Ferrari
of databases: Light weight, fast and Built for the high speed tracks!
I still hear an awful lot of stories from owners whose databases are
running two slow. In my experience, the three main places to look for
problems are:
1. Faulty Database Design 2. Bad Queries 3. Server factors
Faulty Database Design
Proper database design is the single most important factor for the ensuring
performance and maintainability of the database. Here is what you need
to answer when designing a table: Can I reduce the size of data that
each row will have? Here is what you can do:
1. Use unsigned numeric values when the application will not store negative
numbers. Like the “quantity ordered” of an item in an ecommerce
application is never going to be -$125.
2. Use Variable length values instead of fixed length value i.e. used
varchar instead of char.
3. Do not use unnecessarily large field sizes. For most ecommerce application “unsigned
smallint” is more than enough to store inventory count. A field
described as “unsigned smallint” can store a max value of
65535.
4. Don’t ignore normalization; its helps prevent unnecessary repetition
of data. The part B of this is, don’t overuse normalization. If
the table will not grow in size significantly, there is no point in normalization.
For example, if the user table has just 20 rows (i.e. 20 employees in
a company), all attempts of normalization are wasted.
5. Use Keys. Don’t decide keys by “The customer id has to
be indexed in the order table”. If the order table is being searched
90% of the times by “order date”, it makes more sense to
index “order date”.
Remember, how a table will be used should determine how it is designed.
Spending time here will save years of frustration.
Bad Queries
It sounds too good to be true but you wont believe the number of developers
out there who completely suck at writing queries. There are two types
of bad queries:
a) Unnecessary Queries: These are the queries that shouldn’t have
been made in the first place. The only way to avoid this is asking, “Do
I really need this data?”
b) Inefficient Queries: These are the queries that do not use the underlying
table structure or MySQL functions in the correct way.
Here is a starting point to start looking at problem areas:
1. Unnecessary usage of “Select * “statements when the entire
processing is being done on a single column. The more data is fetched
from the server the more work MySQL has to do and more bandwidth it takes.
2. Using sub-query instead of a join. On a properly designed database,
joins are incredibly fast. Using sub-queries just shows a lack of knowledge.
3. Improper use of Keys. This is especially valid for range checks.
Remember to use the “Explain” statement to check the usage
of keys and then use the “use key” statement in your “where” clauses
to force key usage.
Server Factors
Everything done correctly, there still may be some server factors that
may be causing the system to be slow. These are:
1. Hardware related 2. Server configuration related
Here is what you can do about the hardware:
1. The more RAM is on the system the better it is. MySQL frequently
fetches data from the RAM and more the RAM is on the system, the better
it is.
2. Buy the fastest possible RAM! A slower RAM is just irony.
3. Once you are settled with the RAM size and speed, look for processing
speed. MySQL can use multiple processors.
Once you are satisfied with the hardware, there are a set of variables
in “my.cnf” that you must look at:
a) key_buffer_size: This describes the memory available to store the
index keys. The default is 8 MB but you can set it to 25% of the RAM.
b) query_cache_size: This value is by default 0. if you have a lot of
repeating queries like in reporting applications etc, make sure you set
this value high.
c) table_open_cache: This determines the number of table descriptors
that MySQL will keep in the cache. The default value is 64. But, if you
have 100 users accessing a table concurrently then this value should
atleast be 100. You also have to take into considerations joins etc.
Thus, this value should also be kept high.
I hope this article will take one step further in unlocking the mystery
of slow servers and help solve some of the problems.
About the author
Mukul Gupta is the CMO of Indus Net Technologies, an India based
Internet Consulting firm which specializes in Opensource solutions.
You can reach him at script@script2please.com or visit http://www.script2please.com |
| » Change
your mind about an eBay bid?
We have all made choices in life that two seconds later
we know we should take back. Especially when there is money involved
this can become a problem.
» A
simple way to create 7 effective autoresponder messages
Email is the Net's most powerful marketing tool. And autoresponders
are the best idea yet for marketing with email.
» 7
ways to drive laser-targeted traffic to your website
Getting people who matter to see one’s website
is a difficult undertaking if he tries to consider the fact that
there are rivals everywhere waiting to pin him down.
» Website
valuation: Why standard website pricing methods will emerge
The market of buying and selling developed websites is
becoming more and more liquid each day.
» One
way links are better than reciprocal links
You probably know by now that where your website ranks in
the search engine rankings dramatically affects how many visitors you
have to your site. Did you also know that you can change where your
site is ranked by being proactive and getting as many one way links
to your site as possible?
» How
to make visitors stay at your website
The very first thing which you should provide the visitors
with is some free interesting reading material.
» How
to make your visitors click your ads
Here is a simple solution; Convert your banner advertisement
to look like a text advertisement!
» Offline
advertising should be a part of your online strategy
Day by day, online business has become more & more
complicated and competitive.
» How
to sell traffic
Selling the traffic arriving at your site is a good method
to increase profits from your portal.
» Make
money from online auctions
Online auctions have the best benefit of a vast platform.
Your product is viewed by loads of people & hence there is
more possibility of finding a suitable bidder.
» Groupware
explained in easy terms
Groupware is a term used frequently to describe collaborative
software. Groupware is application software that integrates work
on a single project by several concurrent users at separated random
workstations.
» Timely
back up can save you from disasters
Few things which people often back up are e-mail addresses,
bank records, photographs, personal records, software’s,
music etc.
» Why
should one go for autoresponders
Autoresponders are programs which get automatically
executed in particular situations.
» Become
your own boss - Start your own online business today
A survey conducted by SBA states that two third of new
business survives at least two year and about forty four percent
survives at least four year.
» Express
your thoughts - Creating your own blog!
What exactly is a blog? Technically speaking it is a
journal or a newsletter which is regularly updated and can be used
by any one.
» Pop-up
ads - To be or not to be?
According to a study conducted by the Bunnyfoot University, “The
Efficacy of Pop-ups and the Resulting Effect on Brands” Internet
users feel harangues and harassed by pop-up ads.
» Why
content is king on the Internet
The advantages that Internet holds over the rest of
the other communication mediums should not wasted because of
the inability to find a comprehensive plan that will bind all
these faculties together.
» 10
niche marketing tips
In our increasingly driven consumer economies, the average
customer is bombarded by choices. With increased saturation of the
market, companies look towards niche marketing to search new, ever-evolving
and sophisticated consumers.
» Using
free traffic exchange
These days internet has emerged as both, a market and
hub for marketing. Unlike the ‘brick and mortar’ world
where large manufacturers manage to squeeze out the market bases
of smaller companies, the internet provides haven like the free
traffic exchange.
» Ten
ways to drive traffic to your website
Developing a web site and then letting it grow is
like planting a tree and then nurturing it.
» Marketing
through keyword articles
One of the most effective tools of Internet marketing
is the use of keyword articles.
» Want
to make money online? Market a service to businesses
Don walked across the street from his house to mine to
announce he had finally retired. "But I'm not ready for the
golf course," he said. "I want to make a living on the
Internet. What can I sell?"
» Web
site design mistakes - Database parameters in URLs
Creating a web site takes thought, planning and execution.
Unfortunately, many designs are dead in the water before they are
even published as far as search engine optimization is concerned.
Whatever you do, avoid these critical mistakes.
» Alexa
Toolbar - The ultimate internet tool
There are numerous tools available on the Internet to
assist online businesses. A valuable tool that you should use is
the Alexa Toolbar. Even better, this tool is free.
» Web
site design mistakes
Some wise human once said "Learn from the mistakes
of others. There isn't nearly enough time to make them all yourself."
Hence this article. Here are five of the most annoying and common
web design mistakes.
» Abandonment
- Why visitors don't turn into customers
Every good Internet business understands the value of
conversions versus hits received. Far too often, businesses become
fixated on the hits they are receiving instead of monitoring their
hit to sale conversion rate.
» Creative
search engine optimization - A case study
Search engine optimization this and search engine optimization
that. You read and hear about it all day, but what about your site?
|
|
| Tools & services to enhance your online
business |
|
» Site
Build It!
Over 100,000 small businesses of all kinds outperform
larger, well-financed competitors. Read about this all-in-one site-building-hosting-marketing
system of tools that delivers results.
» Secrets
To Their Success
Take a private tour of two "Mom & Pop"
web sites every month that earn $100,000+ a year... and discover
the exact step-by-step strategies they have personally used to generate
these massive profits. |
|
|
