Matt Probert
msg:1580694 | 7:14 am on Sep 19, 2005 (gmt 0) |
I'm not an expert on MySQL, but with regards to any server side performance you could try talking to your host about the possibilities of "load balancing". That is operating a number of identical parallel servers that effectively share the processing effort. You'll want to talk to your host as they need to understand how to do it, and to be able to quote you a cost. Matt
|
Eltiti
msg:1580695 | 8:35 am on Sep 19, 2005 (gmt 0) |
If you are talking about 20000 simultaneous users, that may actually be a serious load... I'm no MySQL expert either (SQL Server here), but my #1 suggestion would be: create the right index(es) to support the query! There is a performance hit when you create/modify an index; however, you say that your app is "select-only"; and an index can make a huge difference when retrieving data. If your select statement looks like this: SELECT a, b, c FROM t
WHERE a = 123 and e < 234 you'll probably want an index on the columns a and e. -- In addition, you may want to look into using "stored procedures" (not sure if MySQL supports these, but if it does, they're probably faster than "ad-hoc queries"). HTH
|
RonPK
msg:1580696 | 9:58 am on Sep 19, 2005 (gmt 0) |
Correct me if I'm wrong, but I think that the use of pconnect will cause thousands of mysql processes hanging around idle. In other words, you may hit the limit of max. allowed connections.
|
grandpa
msg:1580697 | 10:27 am on Sep 19, 2005 (gmt 0) |
I believe you are correct RonPK. The MySQL documentation notes suggest using mysql_connect() instead of mysql_pconnect(), unless you have a good reason. These are developer notes, but they've been allowed to fly. The first thing I thought of was max_questions. I think it's set to 50000 (it was on my host) and with 20000 daily visitors it could potentially be exceeded. I was able to get the limit raised on one account, but on another I was told they can't raise the limit (confused look). The problems of too many connections or too many questions doesn't exactly address the issue of improving search performance, but they should not be overlooked. It's advisable to have a 'plan B' in case either becomes an unexpected issue.
|
R e b r a n d t
msg:1580698 | 10:23 pm on Sep 24, 2005 (gmt 0) |
How about you show us your table schema, few rows of data and a query?
|
jmccormac
msg:1580699 | 1:41 pm on Sep 25, 2005 (gmt 0) |
This could be very useful if the same queries keep getting executed. [dev.mysql.com...] Regards...jmcc
|
sergeytch
msg:1580700 | 6:17 pm on Oct 2, 2005 (gmt 0) |
First, you don't have to use persistant connection;
Second - creating of index (on the row by which you're searching) may help you (to do it right it would be better if you show the table schema). InnoDB works well with large tables, but I think that if you don't use transactions MyISAM will work faster... I don't really remember, but I think that the query optimization in such case doesn't depend on type of table. Best regards,
Sergey
|
arran
msg:1580701 | 2:54 pm on Oct 3, 2005 (gmt 0) |
| Second - creating of index (on the row by which you're searching) may help you |
|
It will be essential - 1000+ users table scanning a 0.5 million row table is a non-starter. Personally, i think you'd be better off looking at other options (e.g. SQL Server). arran.
|
sergeytch
msg:1580702 | 4:48 pm on Oct 3, 2005 (gmt 0) |
hm... let's imagine that you try to search on a varchar field. if you make an index on first 8-12 characters such index table can extremely speed up select queries (as i tried). I didn't have 500000 records in one table, the largest amount was 200.000 or so, but i think that mysql can process much more if you build your queries right.
and when it's possible, try to make well-structured databases.
|
sellchain
msg:1580703 | 8:12 pm on Oct 16, 2005 (gmt 0) |
Eltiti's INDEXING theory helped for my problem. I was waiting 9 hours so far for 23,760 lines of data to use the UPDATE statement from my PHP, to get it into MYSQL. I Indexed the UPC code, and instantly all of the data went in. I didn't wait more than 5 seconds. Eltiti you rule ;) - I been trying to do this for years, but no one ever posted this information.
|
|
