homepage Welcome to WebmasterWorld Guest from 54.161.192.61
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL Optimization
Question about how to improve memory performance on MySQL DB
Trav

5+ Year Member



 
Msg#: 4037141 posted 6:38 pm on Dec 4, 2009 (gmt 0)

Hello,

I'm not a MySQL guru by any stretch, but I am trying to determine if there are some fairly straightforward ways to optimize DB calls and generally reduce the RAM requirements. Our server has been crashing fairly often of late (12+ times in a 24 hour period), in no small part due to the increase of holiday traffic. My sense is that the increased traffic is teasing out the more subtle issues with the code/queries being used. At any rate, hoping someone can point me to a 'primer' of sorts on this. I've perused the mysql.com site, which is great for specific, known questions- but it's not so great tutorial-wise. Any help appreciated- happy Friday!

Trav

 

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4037141 posted 9:00 pm on Dec 4, 2009 (gmt 0)

You might not like this . . . but the first place to look is your programming. This is usually where mysql begins to tank, in the way it's accessed.

I'll give you one example: one of the most common things I see in PHP is

execute a query to meet a where condition, getting the data
execute a SECOND query for mysql_num_rows

Loop through num rows, make repetitive selects for each record id to actually get at the data that was in hand already in the first query. I don't know where this logic comes from, but I see it a lot.

Another example is select * from when all you need is one or two fields.

I almost never see mysql_free_result() after queries, which frees up what? memory. :-)

You can add query benchmarks throughout your code, compare against command line queries, this will tighten up the programming and reveal possible areas of improvement.

Explain on your queries can tell you a lot too.

Inadequate error checking often leads one to think "it's working fine" when it's getting dangerously close to crashing your server.

For the DB itself, question a few items:

Are there any areas that I could be using integer lookups instead? Example, instead of

select * from products where category like '%some_cat%'

select * from category where category=5

where even though "category" is in a separate table, queries on integer fields will always run faster. Use this to your advantage. At every chance in programming, try to eliminate text searches, even if it means creating tables or complicated selects with joins. Save text searches for when you need them.

Are there any columns that have redundant data that doesn't need to be?

id¦first¦lst¦email¦site_ownership
1¦John¦Smith¦blah@example.com¦1
2¦John¦Smith¦blah@example.com¦2
3¦John¦Smith¦blah@example.com¦3
4¦John¦Smith¦blah@example.com¦4

John owns 4 sites. Each site is id'ed by a unique ID. So we have to have row after row of redundant data . . . NOT!

users
1¦John¦Smith¦blah@example.com

sites
id¦user_id¦site_id
123¦1¦1
124¦1¦2
125¦1¦3
126¦1¦4

Again, eliminating 4 rows in the users table, and allowing integers to rule the sites table, this is going to be light years faster, even with a join.

Are my columns wider than they need to be?

create table . . . zipcode varchar(255) <------

Are my columns indexed? Be careful with indexing, use it only on fields that will be searched in a query. Indexing all fields can sometimes have the opposite effect.

create table . . . zipcode varchar(15), index (zipcode(3))

On that topic, is my column the appropriate type? If your site commits to a five digit zip code, U.S. only (bad idea, but follow along) why would you want anything but this?

create table . . . zipcode int(5), index (zipcode(3))

Google for mysql normalization which will lead to many topics on tightening up the ship.

lammert

WebmasterWorld Senior Member lammert us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4037141 posted 3:08 pm on Dec 7, 2009 (gmt 0)

I almost never see mysql_free_result() after queries, which frees up what? memory. :-)

It frees up memory for large queries, but my experience is that when you have a large number of very small queries in your script it is better not to use this function because total execution time of the script is significantly increased with only slight reduction of memory usage. YMMV

Trav

5+ Year Member



 
Msg#: 4037141 posted 1:31 am on Dec 11, 2009 (gmt 0)

Awesome, thanks for the detailed responses. I haven't actually written most of the DB code- it's locked away in PHPbb2 and Expression Engine. I would have assumed that these two products would be fairly well-written, at least in the manner described by roknbil, and they probably are. It will be interesting taking a look. Thanks again!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved