SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL database maintenece?

    Hi,

    I am currently building a MySql database which I maintain via PHP.

    I am concerned over how I can maintain the performance of the database. Ie, do I need to run regular processes to compress the data given that I will be constantly inserting and deleting.

    Does anyone know or know of a resource/guide which can help me learn what I need to do to the database (besides backups) on a regular basis?

    thankyou,
    Darren.

  2. #2
    SitePoint Enthusiast xor's Avatar
    Join Date
    Jul 2001
    Location
    New Zealand
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Optimization

    I like the DevShed tutorials (and they have nicely formatted pdfs for printing ), here is one on optimizing MySQL. Many of the optimization techniques are not really something you would do in php (like compiling and configuring), but once you have done them you shouldn't really have to continue doing them. Also good database design to begin with, and optimizing your PHP code wrt the database queries will help too.

    Have a read...
    Optimizing MySQL

    Hope that helps

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help but I was more looking at how do I keep the performance of the data optimized? Ie, will the data over time become defragmented and therefore need to be optimized via (for example) an unload process, then a sort then a reload? I have read something about a command called FLUSH but am not exactly sure when to you it?

    thankyou.

  4. #4
    SitePoint Enthusiast xor's Avatar
    Join Date
    Jul 2001
    Location
    New Zealand
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah I see what you mean now. I don't know enough about the inner workings of MySQL and how it actually stores the data to say. Here is more about the Flush command:
    http://www.mysql.com/doc/en/FLUSH.html

    Looks like it clears the cache, but this flush_option might be what you have heard about:


    QUERY CACHE
    Defragment the query cache to better utilise its memory. This command will not remove any queries from the cache, unlike RESET QUERY CACHE.

    But I think you would have to have very high traffic to you site to get to the point where you would need to do it. For example if you had a very popular forum like this one. I'd be interested to know other's experiences with this. Who administers the SitePointForums DB? Is it MySQL?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •