large query settings

[matt]

I am having problems with mysql and running queries on very large datasets. The issues I am encountering generally only occur when I am doing a join between two or more tables.

for example:

in customer table i have 3 million customers

in charges table i have 18 million charges (each customer can have more than one charge)

Whenever i run update, select, or delete queries where i am using a join on the CustomerID fields (which is the link between the two tables) the queries will run for hours at 100% cpu. I know the queries are valid because I have successfully run them against much smaller (~50K records) datasets.

I have managed to carve out the files into small chunks of about 100K customers and 1-2 million charge records and run the queries in msaccess. it took about 45 min. I have proccessed the entire file as chunks in ms access in about 4 hours (and then use mysql to combine it back together).

The longest I have let it run using the whole file has been for ~30hours in mysql. After that point I have given up. The server is a p4-2.4gHz, 512mb ram, and plenty of disk space running win2k and the latest release of mysql

Does anyone have any ideas on tweaking mysql's my.ini settings so I can get this to run? I have tried setting it to not use the query cache but that didn't improve anything. Any suggestions?

[CalEvans]

4 suggestions:

1: get more RAM

2: Check out the sample cnf files. There is one for large systems. Read it over carefully, it may contain some insight.

3: Use EXPLAIN on your query to see if another index would help.

4: post on the mysql mailing lsit. There are guys there who have crossed this bridge before (Jeremy Zawoodny for one…and I know I misspelled his name)

=C=

[matt]

i am using the large system cnf file.

i was not aware of the EXPLAIN keyword. i will try to see if i need some more indexes or if it has any suggestions on arranging the queries. i will check out the mysql mailing list to see if i can get any further suggestions. thanks for your help.

[Shadow]

The table type may also affect which settings should be altered, but the following ones have the most influence on performance:

set-variable = read_buffer_size

set-variable = key_buffer_size

set-variable = max_allowed_packet

set-variable = table_cache

set-variable = sort_buffer

set-variable = net_buffer_length

set-variable = myisam_sort_buffer_size

4.5.6.4 SHOW VARIABLES section of MySql manual explains these settings in detail, but generally assigning higher values to these variables will increase the speed. But in case of such large tables, I would recommend to get additional memory (at least 512MB plus).

[Author]

Posts