mysql, pc hardware, and an 11 million row database

[kashani]

As usual a simple server tuning contract job turned into so much more.

Mysql 4.0.x, Gentoo Linux, 2GB RAM, 3 disk raid5, on dual 1GB PIII. It just isn't cutting it though the data and index files can fit into RAM, 0.9GB total. I believe the main table has too many rows and any SQL quey against it is going to be slow, around 30 seconds in our case.

The db is a demographics type thingy with the main table beinrg 2 columns of intergers coming in at 11 million rows. Most queries end up with a left join to this table to get the demographics data and then figure out which email to send to and what not.

Here's a simplified look at big table. userid is going to a user, such as user 10, and demogid is each option userid 10 checked in the survey. 1-400 demogID's and about 200k users.

[iamarug]

Those solution both require only a single select statement to get the info you need so on that side it should be okay. However, it is hard to tell which one uses more storage because that would involve having to figure out how many entries each user would have in the first type of structure (on average). I would probably go for the 1st way you had it. If worst comes to worst, it should be fairly trivial to write a script to change the tables and retain all the data.

HTH

[kashani]

I believe it's more complicated than that. Doing a full table scan over 11 million rows is more work than doing it over 2 million even with the added weight of adding 1000 columns. The new method results in a bigger table space, but possibly better performing queries. Also I have better indexing options with the newer method... or so it appears.

That's my current theory which is pretty theoretical, I don't have the exp to say one way or another, which is what I'm looking for. Also the current db takes 30+ secs to return an answer and the client is looking for < 5 sec or better for scaling towards 1-2 million userID's or 50-100 million rows assuming we change nothing. With roughly the same performance with some added hardware. SBC's (the phone company) billing db is roughly 50 million rows and ranks in the top 10 Oracle installations, which might indicate that the current db design sucks and does not scale.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[myuser]

Alternatively you could use a 4 bit data value.

e.g.

[iamarug]

If one where to use 4 bit data types, could you then query based on one of the bits or would you have to do bit operaiton outside of mysql?

Kashani: I think you have a good point in theory I guess if you could do some tests on your data set (or something similar) you could come to a better decision.

Btw, if you do some tests, please let us know what results you get!

good luck

[kashani]

I don't think that's a workable idea either as they've got 300 odd demogID fields to start off. While my knowledge of indexing theory is incomplete I am however quite sure that indexing int(400) is a really bad idea.

Here's a better idea of something I've been considering. This is what the actual data might represent..

[iamarug]

have you actually timed any queries on 11 million rows? I think your design decisions may be unnecessarily burdensome if mysql can handle 11m rows "fast enough".
obviously, there is certain items (like sex) which may only have one entry (I hope ). Furthermore, it seems you would have the information on several items for everyone. With the previous two facts in mind, it may be a good decision to include such columns in the actual table while having the other demographic items in a seperate table.

btw, out of curiousty, where did you get your nick from?

[kashani]

In post #3 I mentioned it takes 30+ seconds to respond. We'd like < 1 sec, but would be happy with < 5 sec as long as it scaled to 1-2 million users.

This is on the new db server doing nothing other than Mysql. I tuned mysql with the my-huge.cnf and the usual bit of "you're got 2GB of memory, now use it" parameters. I'm feeling pretty confident that the server doesn't have any more performance in it without changing the database schema. I thought of pushing it to 4GB of ram, but the entire db with indexes is only 0.9GB and that's about what I use when I run the query.

Think about it this way. I give you a subject, author, and title library card catalog all schmushed together into one system. And then I tell you it's isn't in any particular order and make you do searches. You now have the equivalent of the 11 million row db where all your seaches have to go through the entire table. Instead we break the card catalogs into 3 "columns" author, subject, and title. Now you can do searches in a much smaller area. We can also add in Science card catalogs and Fiction card catalogs as well. Our searches have much fewer records to check by partitioning our data intelligently.

I'm fairly certain the above describes what's going on, but I'm not sure what's the best way to go about fixing it. If you're interested you should read O'Reilly's Oracle Tuning Guide. It has a number of interesting case studies, but wasn't general enoguh to help me when I read it last night. I'm taking DB Design for Mere Mortals home tonight which looks like it might be the right book.

Kashani is the name of my Grandmother's family in Iran. Yes those Kashani's if you're familiar with 20th century Iranian politics. And it's a type of carpet which I bet is why you're asking.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[iamarug]

Actually, I did not ask for the carpet thing. I am from Iran so it was interesting to see you nick. Anyways, sorry I didnt catch the 30 second thing.
What kind of query is it? For all of the records in the table? Maybe if you give a general idea of your query some others could help you out.

[kashani]

Dude, yer killing me over here. Any query against a two column table has to do a full table scan. A full table scan of 11 million records will take a long ass time. You can index your table, but this setup is so bad that the index gets you almost nothing.

This is striclty a design problem brought on by having too many rows, no columns for all practical purposes, and lame indexing, thus spake the 2 DBA's I went drinking with tonight. I think the kindest thing anyone said was, "well I guess it does work in a completely lobotomized way. What are you moving them to next? A flat file."

iamarug, are you still in Iran or somewhere else? I'm in Los Angeles.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[To]

You will only have one line for each user if you use, the 2nd way you have shown. Course like you hav sayd any query to more than 1 clum will be slower, yes, and for each more colum even more and so on. I beleave that you should try to use posgresql. MySQL is faster on smaller db's but postgesql is faster on huge db's. It used to be this way, can't be sure but you should try.

Tó

[iamarug]

Damn. I cant see why you would need a full table scan. Why wouldnt a user ID index help? I dont think any database can do an 11 million row full table scan in less than 5 seconds on your hardware so this is a design problem. But seriously, why doesnt an index on the userid help?

ps: I live in chicago

[screwloose]

[kashani]

Screwloose,
After getting expert advice last night and being somewhat introduced to normalization the mutiple columns method is the logical solution. I'm still a bit unsure how to deal with data where the user can check mutiple fields. Probably by creating a separate table for each set of interests. The car table, the sports tables, etc and have a column for each option they can choose. I don't think any of them have more that 10-20 separate choices. Mostly it's a matter of learning the basic techniques your average DBA already has for this stuff the firther I get into it.

Having bounced this question off several people the past few days I learned a number of things. One of them is that the average Linux/BSD admin/user/developer does not understand databases. Not criticizing anyone who posted here, you all really helped me get my head around the problem. But database theory/design/whatever is a real blind spot for many, many usually smart people.

Kashani's top 8 things to know about databases
1. Postgres = Mysql If anyone can prove that there is more than 5% of difference between these db's with reproducible lab results, I'll buy you sushi.

2. Swapping out your db in a production system is hard. Don't even bring up switching to your favorite database unless there is concrete data to support the decision. See also #1.

3. "Design is the #1 influence on performance in your database." - DB Design for Mere Mortals

4. One set of data per column. Integers are easier to sort than ascii. Use that "many to many" thing to avoid ascii.

5. The word relation in relational databases refers to relation, part of set theory, the mathematical basis of relational databases. Not becuase of any relationship implied between data.

6. Flat files breakdown at 2-3k rows, Mysql/Postgres breakdown at 5-10 mil rows. Everything has a limit, understand your database's.

7. "Blindly upgrading hardware in the face of performance problems will usually make your database slower because of increased contention" - O'Reilly's Oracle Tuning Guide

8. Remember Amdahl's law

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[screwloose]

I'm one of those bizarre jack of all trades where I have been trained to be an Oracle DBA, Linux/Novell/Windows Network Admin, and programmer(too many languages) but have somehow found myself mostly building web apps with php using pretty much any database server my clients have. Your top 8 list is pretty much bang on.

The one thing I would comment on having used both MySQL and Postgres is that for speed most users it won't matter but sometimes if a person is doing a more advanced query Mysql won't be able to run it do to lack of support for some forms of joins. Again there are ways around that problem but I'm not going to get into that. Most of the time I do work with MySQL so I've learned how to get around its quirks.

Anyway back to your problem, the best way of handling multiple choices is using a seperate table for each choice, possibly with an additional lookup table to later determine what the choices are.

Here are some really basic tables that hopefully give you the right idea

[screwloose]

[iamarug]

I think you guys seem to have found your solution. One other thing that concerned me (dont know if it is really important) was that if you were to break up all of these things into different tables, then in order to get your information, you would have to do a join across a ton of tables. Your case is a many to one normalization. If I had been in your situation, I would have created the following way:
recordid userid demographicid | demographicid demographicdata
0 1 2 | 0 male
1 3 3 | 1 female
2 3 12 | 2 unemployed

I would include demographic data that all people have (such as sex etc...) in the userid table.

Then I would have constructed the index on the userid field. This would seem to make alot of sense to me and I would expect my results from queries in a short period of time (much much much less than 30 seconds).

I still dont know why this solution would not work!

I am glad you have found a solution that suites you

[screwloose]

I'm not sure but I think you didn't quite read my example right or maybe I wasn't clear enough about the relationship I described above but it is actually a many to many relationship. (ex. there are many users each of which may have 0 to many sports associated with them)

The most efficient way of handling that situation as far as I know is with multiple tables. I do agree any field that only has one value at a time (ex. the user's age) the field should remain in the user table.

Table joins aren't an overly expensive operation as long as you use proper indexes and they can add alot of flexibility to an application.
_________________
If something can go wrong it probably already has. You just don't know it yet. ~Henry's Modified version of Murphy's Law

[kashani]

[kashani]

Screwloose,
same boat as you though I came from a Cisco/ISP/NSP background. These forums have it all, db design just being the latest. First time for everything I suppose. But this is exactly what I'm looking for, the voice of experience... "no you don't want to do that way kid, you'll put your eye out."

I agree with your points on the Mysql/Postgres debate. The current developer is actually more familiar with Postgres than Mysql, but there is too much code and business logic to start fresh. We both talked about it and decided to stick with Mysql for the time being. Though if we do a complete rewrite he'd prefer Postgres and I see no problems with changing the database at that point.

The new plan is roughly

master_table: user, age, sex, state, zip, income, status, etc
Basically anything that has a single data field. I can break this up into more tables, but I don't want to get carried away like you mentioned. I'm still a bit confused on how much is too much. Is it better to have all these in a single table assuming that we'll be looking for 0-4 of them in any particular query? Or should I have have 10 tables? I am worried about going back to the user id appearing mutiple times in any table getting us back to this problem as we grow. Ideally none of the tables would ever be larger than the number of user ID's which should keep everything under 1-2 million for the foreseeable future.

Then we'll have the assorted special interest tables such as sport_table, car_table, computer_table, and so forth. My working assumption is that most queries are going to pull something from the master table and then go looking for 1 or more matches in the special interest tables. 99% of those will only hit a single special interest table or at least that's what I expect to find.

example
query for males, 20's, 30k+, buying a new car, owns a Jeep
query for anyone, owns a computer, has DSL

Luckily there is a developer who is writing all this code and even better has to go through and figure out what the application is actually doing. We both sort of inherited this mess and it gets worst every time we turn our backs.

According to the books the next step is write all the fields down, look at the queries, and design a schema according to some parameters I'm still learning that will make all these problems go away.

Thanks for the input, it's nice to know I'm not preparing to walk off a cliff.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

[kashani]

[screwloose]

Too much is when your queries start to become slower due to the fragmented data(yes I know thats still kind of vague) An easy way to tell how efficient a particular query is running is to use the EXPLAIN command.