Mysql database advice

Discussion in 'OT Technology' started by jlangdale, May 22, 2006.

  1. jlangdale

    jlangdale New Member

    Joined:
    May 21, 2006
    Messages:
    6
    Likes Received:
    0
    Location:
    Montreal, Quebec
    I am looking to setup a large mysql database on a RAID. Does anyone know a good schmea and technique for store a lot of text and indexing it? Maybe using compression or something like a search engine might do?
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    compression will slow you down.

    define "large"

    what will you be storing? Yea it's text, but what is it going to be off? What are you indexing? How will you search it?
     
  3. jlangdale

    jlangdale New Member

    Joined:
    May 21, 2006
    Messages:
    6
    Likes Received:
    0
    Location:
    Montreal, Quebec
    kind of text

    I'll be storing html snippets, not full pages just snippets. Similar to what a CMS would store as a page/post/blog.

    I'm just thinking optimization, what kind of schemes might there be? Substitute all the tags for keys/ids? build a taglist index? wordlist?

    I want to find the most efficient storage scheme I can to get the most performance out of my database running on my raid. Maybe mysql is not a good database, maybe something flat file proprietary. I worked with some indexing source a while ago, I can't remember too well.

    Basically, I'm woundering if anyone knows any good sample source code or sample benchmarking mysql schemas/queries I can use to get a handle on what my through put might be as far as queries/time/size. Then I want to do some tests and maybe make a J2EE app server deal, scale up to another app server at home and see what kind of performance I can get.

    I'm going to for a homegrown deal using free open source stuff. I've used MSSQL server and oracle 10g, but they're too big for me. Seems like too much overhead. I want to keep it as simple as possible while getting awesome performance.
     
  4. jlangdale

    jlangdale New Member

    Joined:
    May 21, 2006
    Messages:
    6
    Likes Received:
    0
    Location:
    Montreal, Quebec
    the text will be conversational text with links and images
     
  5. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    what about XML?
     
  6. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    MySQL comes with great benchmarking tools. Check their site for documentation about load testing. Your application doesn't sound too demanding. With a proper database design, MySQL can certainly handle it. Without knowing what you're doing, its hard to tell you more.

    If you want to create some data and run specific queries for a load test, then I would use the Perl DBI library with the DBD::mysql driver. The CPAN and the web in general have thorough documentation, and the application would likely be under a page of code.

    How much data are you storing?

    MySQL limits chars and varchars to 255 characters. MS SQL Server has a much higher limit. Is 255 enough for your data? If not, you'd have to use text.
     
  7. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Mysql has the ability to do Full Text Search Indexes. This might be something that you might want to look into. You'll have to use MyISAM in order to use Full Text Indexes, but that shouldn't be that big of a an issue.

    The amount of data that you are storing will play a big part. If you don't see this project getting really large (gigs or better of data) then I wouldn't worry too much about the performance since it will still be high.

    Also, start to think about the type of data that you will be searching for. Is the data only at the end of the set or the beginning dead center. This might help you determine the best method in which to organize your data.

    Just some more things to think about.

    Oh as for raid, Mysql recommends RAID 10.
     

Share This Page