Chat on PHP: the bottleneck is the database — how to solve?

0 like 0 dislike
34 views
There is a task to organize a simple chat with a web interface, and full history at the current site on samopisnom engine (PHP5.3.3/MySQL5.1). The gugleniya on existing decisions nothing good has given, or is redundant, or produces a sense of "lap crafts" and often have not supported, and I would like to have a single architecture and coding style. In General, the decision to implement independently. With coding no special problems, the prototype implemented, but load testing with different versions of indexes and tables showed that ~20 hosts "readers" and one "writer" per second MySQL shuts up (c VDS 1Gb RAM, muscle half cast, and 2GHz percent, nginx+php-frpm under Debian), even for denormalized table, because caching means DB queries can not be (filters, each "reader" its because the private, filtering in the server application is unlikely to be more effective than in the database I think, and the client is invalid). And I would like on this hardware at least 40-50 to keep in addition to the basic load. What can help? Experience "hayload" no, any such idea:

— write daemon to chat on a subdomain, to read in the main thread from the database only at the start (of the last N messages) or in rare specific queries, stored them in the memory of a process (killing the old), and wrote to the database only "logs" for next start (then filtering will be effective, IMHO, plus it will be possible to implement an incremental advance and by storing the messages in a single pool and for every reader to add to the list of links to "his" message when a message arrives from "writer" to him personally or to public, and delete them from there when reading)

— similarly use mimkes (although it is hard to imagine how to ensure the integrity, to just file caches worked, who do not "evaporate") for normal PHP handler (that is, that a lot of workerb had access to a shared pool of messages, and incremental personal lists of references to them between requests)

— translate chat on NoSQL database (what? the main task of an effective filtration for a couple of fields the last record, like WHERE timestamp > {last_time} (or id>{last_id}) AND (recipient_id IS NULL OR recipient_id={user_id}) ORDER BY timestamp (or id) DESC LIMIT {max_records} )

It's worth a try or what are the options? Demon not want to write, as it will complicate administration and servers, and the actual chat (similar to IRC commands do?) experience with caching and NoSQL are almost there.
by | 34 views

7 Answers

0 like 0 dislike
First, it is necessary to clarify that for VDS. If masterkhost, immediately warned, because it is a separate song.
\r
Secondly, you need to understand what it mySQL. Above correctly wrote about the lock on recording time.
The same may not be able to drive (due to limitations of the VDS).
\r
I would try to translate in dB and innodb commit to 0 to set (reset once per second to disk).
\r
Further, there is such a thing as views. And they have mode with in-memory cache. And SQL queries have him to do. Moreover, they can spawn on different occasions.
\r
It would be good not to forget about memory tables. For example, to write more and not memory, but only read mass.
\r
Along the way, it is worth to remember about the indexes. Their absence makes the select a long, and the excessive presence of a long insert. And indices beginners usually do wrong.
\r
Still, I went back to the VDS settings, because it cuts the average load in percent, memory, and disk operations. You stupid maybe it's to cut. And at the same time have remembered the size of the database in memory. If there is already a lot of data, and the caches are not large, the cutting of the disk will be.
by
0 like 0 dislike
what is VDS it is, really? If OpenVZ, then you specify the resources you will never get.
by
0 like 0 dislike
Tips from myself
1) to try to implement on the server node.js (single process like 800 connections per second holds)
2) heavy queries how I understand you when in the chat 30 people, one of them writes, do you keep in the database, then 29 people send a request to "update" a chat... performed on 29 samples from the database, in my opinion it's not quite right, try a little differently: when one user sends a message, of course once you save it for "history", but in the same memcache create for all the "interested" users turn updates no need to store a single message at all, it is necessary for each to store a complete list of what he is interested, and of course when 29 people to ask for updates, you generally will not apply to the database, and just take the data from the cache (and clean it of course)
by
0 like 0 dislike
I remember 10-15 years ago the chat rooms were very popular. In addition to chats and forums, nothing else was not invented. No blogs, no social networks, no Twitter or even ajax. And hang out in chat rooms thousands of people (you can open multiple channels). Or the weaving of people in the chat channels without.
The chats were on the frames every 5 seconds. frame with list of messages is updated FULLY (all the HTML with a whole bunch of messages).
Iron in those days was something like 300-400 MHz. Your VDS, I'm sure, would have broken the server.
Oddly now, in 2010, to hear that the chat does not stand more than 30 simultaneous users.
\r
Have you tried on another server? At least on the locale, how many holds?
by
0 like 0 dislike
You can write a daemon. Simple PHP daemon.
nanoserv phpDaemon or (on the latest information slipped on Habre, but he's for real kung fu pandas :)).
Conventional asynchronous socket connection, why do you need database? Why are you such a sample? You want to store the chat history?
If only the public-private messages — brainer when a message arrives a demon or gives it a specific socket or all brutkasten. No "queries once a second."
On the client side, the flash drive holding the socket and receiving/giving messages.
by
0 like 0 dislike
Was the task to rewrite the chat to a minimum brake (the server was so overloaded) I have used APC (BD threw only sent messages "history") can, in principle, to recommend such an approach...
by
0 like 0 dislike
Yes there is and asks key-value NoSQL storage + polling to fix after plugging in came the plugging of the scripts on the web server.
by

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
7 answers
0 like 0 dislike
7 answers
0 like 0 dislike
4 answers
asked Mar 23, 2019 by Ura78
110,608 questions
257,186 answers
0 comments
28,881 users