1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Database problems haunting me again

Discussion in 'MySQL' started by Rian, May 25, 2010.

  1. #1
    Hi Guys

    I am back again with MySQL database problems. I previously had problems where my databases became so slow, that it was unusable. I then moved to VPS hosting and all searches were fast again. 6 months later and everything is slow again. I upgraded to s super fast and big VPS server, but everything is slow.
    SEMrush
    Here is what I have:

    My server: CentOS Linux 5.94Ghz Processor, 4435mb Ram

    I have one database with 76 tables. In size it totals 14gb. There is 140 million + records. All fields are different. Some tables have 2 fields while others have 121 fields. Fields are not the same throughout.

    I have only 110 users using the databases. One search takes around 12 - 30 minutes to display results. (Sometimes even longer).

    All required fields are indexed.

    Is there any way in which I can make things faster or is that the way it is?
     
    Rian, May 25, 2010 IP
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    Most likely something is requiring very large full-table scans, and not using any cache. Can you identify and post the actual query that is causing the problems, as well as the structure of the table that is being queried.
     
    jestep, May 25, 2010 IP
  3. kmap

    kmap Well-Known Member

    Messages:
    2,215
    Likes Received:
    29
    Best Answers:
    2
    Trophy Points:
    135
    #3
    try to reduce the mysql data

    other alternative shift to amazon (which is not simple ) but i have heard they are very fast

    other alternative buy a mysql data host seperate than vps

    you need to experiment thats the only solution

    In case you should have full backup

    Query optimization is the way by which you can reduce time of search

    atleast optimise some other queries where full search is not needed

    Some queries donot need whole database try to optimise them which will in a way reduce load on server.

    PM me if u think i can do something for you

    Regards

    Alex
     
    kmap, May 25, 2010 IP
    Rian likes this.
  4. cDc

    cDc Peon

    Messages:
    127
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I would first start by checking your fields have the best indexes, read about the EXPLAIN keyword in MySQL Documentation. Unused, or missing indexes are the number one cause of performance issues usually.

    Then check your field types are optimised - for example if you have 140 million keys and you are using bigint as the field type, you would only need int precision - this needs half the amount of memory required to index and search. (4 bytes instead of 8)

    You could also investigate table partitioning - again the MySQl DOcs have loads of info on this.
     
    cDc, May 26, 2010 IP
  5. Rian

    Rian Well-Known Member

    Messages:
    1,763
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    125
    #5
    Hi guys

    Thank you very, very much for your info and help.

    As suggested by kmap I started looking at my data. I deleted unused fields, and also changed the field character lengths (which all was 255) down to the required maximum characters. I also deleted some missing indexes.

    My database is now much, much faster and searches are conducted in less than a minute.

    Thanks again for your help!
     
    Rian, May 26, 2010 IP