MySql and 615 million records

[AlbertAguirre]

OK my record count is up to 615 million. In 3 months it will be over 1.2 billion.

Problem: My webpage takes over an hour to display records.
(Using php)

1) I know my table design is very good.

2) Using the admin tool I see that my query takes 17 seconds

Then it reports that MySql is still doing something after the query is complete.

Does it buffer the data? (lots of records)
Does it write to a temp table?
Why is it taking so long?
(My query is super fast in spite of the large number of records.)

Can anyonme provide insight on what mysql does post query?

How about tips on increasing performance for large databases?

 

[elsenorjose]

Sounds like an application problem. Perhaps also posting in the PHP forum?

 

[AlbertAguirre]

No I know its not. The application is extremely simple. I have been doing this for many years.
I also have the GUI mysql admin tool and it is reporting the query times and this "buffering" thing as well.
If it were PHP it the mysql tool would not report what PHP is doing, at least I would think.

 

[KarveR]

615million records shouldn't give mysql a problem provided your indexes and cardinality are good, and youe query is specific enough to reduce the resultset to something meaningful.


so whats your table design, what indexes are present, what are the index cardinalities, what query are you running, and how many results would you expect returned.

How big does your desired resultset make the intended page, and how much work post processing are you making apache(or IIS) do after the resultset is returned)?


Do you really need that many records?
honestly?



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[AlbertAguirre]

Im at home now, cant send you the table structure but I assure you its ok. So is the query. my users are forced into providing the indexed values.

615 mill recs.. yes, I have no choice. I have done everything in my power to reduce the count but there are just too many transactions per day.

The result set averages 300 records.

Very little post processing is done. It is just set into table rows. Thats it. No column queries for sub values, cjild record tables. Nothing.

The problem here is this process that happens right after the records are retrieved.

What does mysql do with the recordset once the query is completed??

is there a setting, configuration in mysql that will help speed?

 

[jpadie]

what is the format for the display? are you rendering the records in an (x)html table?

If so, most browsers are going to barf if you are sending a true table as they need the whole table before they can parse any of it - and 615mn rows is a lot for the average browser. the exception is if you style your table using css and the fixed table layout, which should allow an earlier rendering.

if you have genuinely ruled out your sql table design and query design, and your php is dead simple then i'd be looking at the output to browser issues.

you have not cross-posted to the php forum, but if you had I'd ask what your script timeouts were set at, as that may help diagnose the bottleneck.

 

[Sleidia]

I don't get it.

Why would someone want to output millions of records into a browser? It's not even human readable!

Why not just make an output into a text file and see if the problem persists?

 

[KarveR]

There are many settings / config options, however without anything technical to work with its a bit hard to offer any solid advice.





______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[AlbertAguirre]

Sleidia, read again. I am not outputting millions of records. 200 max.
The database contains millions of records.

Heres the table structure.

MASTERTABLE (general layout)
----------------------
-> masterID (primary key)
- col1 (indexed)(used for searches)
- col2 (indexed)(used for searches)
- col3 (indexed)(used for searches)
- col4 (indexed)(used for searches)
- data1
- data2
- data3
----------------------
approx 5000 recs


DETAILTABLE (general layout)
----------------------
-> detailID (primary key)
- masterID (indexed)(used for joining master table)
- textdata
----------------------
approx 615 million recs

Its not a table rendering problem. I have already checked that. The queries take forever.

Query example:
SELECT masterID, data1, textdata
FROM MASTERTABLE
LEFT JOIN MASTERTABLE ON MASTERTABLE.masterID = DETAILTABLE.detailID
WHERE col1 = 'someinteger' AND col2 = 'someinteger' AND col3 = 'someinteger' AND col4 = 'someinteger'

 

[AlbertAguirre]

Query example CORRECTION

SELECT masterID, data1, textdata

FROM MASTERTABLE

LEFT JOIN MASTERTABLE ON MASTERTABLE.masterID = DETAILTABLE.masterID

WHERE col1 = 'someinteger' AND col2 = 'someinteger' AND col3 = 'someinteger' AND col4 = 'someinteger'

 

[0ddball]

Wotcha. Ok - I'm not sure if you've fixed this but I'm doing my bit to search for the answer to my problem before I post and I wondered in.

Have you tried making PHP output timings after **every** line in your code. Just spuck em to the output with titles like 'Before query begin', 'Query callback', yaddah.

May help you track down exactly where things go tets up.


Yet another unchecked rambling brought to you by:
Oddball

 

[guelphdad]

when you set up mysql which my.ini file did you end up using?

 

[AlbertAguirre]

You know what guys. I found the solution.
Mysql database partitioning is the answer.

I am trying that now. 615 mill recs in one table is just too much for mysql to handle.

Anyone agree? Disagree?

 

[guelphdad]

no probably it isn't.

but yes partitioning may be the option.

you still didn't answer the my.ini question above though. If you went with the default set-up then your caching an other settings may be too low as a result.