StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>With the latest info and comments added, it all starts to make sense. </p> <p>Your insert statements lock the tables, causing a bottleneck, probably because of an index in your userlog table.</p> <p>So the (assumed) flow is </p> <ul> <li><p>user requests the page</p></li> <li><p>insert into userlog table needs x milliseconds, for that duration of x milliseconds the table (or index or autoincrement column) is locked, not allowing other insert statements on the same table</p></li> <li><p>next user comes before the previous insert has finished, so will have to wait before inserting to userlog table. with so many user, the queue grows rapidly giving very slow response times even though the server is just sitting.</p></li> </ul> <p><strong>Solutions(?):</strong></p> <p>This is a major issue with most database systems. I've had a similar issue sometime ago and solved it by creating a table with a single column (no index of course) in which I inserted the sql statement that I wanted to run. Then run a cronjob (every 5 minutes) which connects to Database and performs all the statements in a single transaction. My traffic was much lower and hardware by far inferior so you may need some finetuning with the above solution.</p> <p>Other things you could try involve:</p> <ul> <li><p>removing all indexes and autoincrements of your userlog table</p></li> <li><p>create a secondary table with the same values but no id, then insert to the real userlog table and remove from secondary table. (similar to the first)</p></li> <li><p>use a fast storage like memcached or redis for userlog, and run a daemon or cron to export statistics</p></li> <li><p>use asynchronous db insert, e.g. with a daemon that listens to a socket and inserts the data to userlog table. Then your script contacts the daemon with the data to insert, the daemon responds instantly, but append the insert command in a FIFO queue and insert when possible.</p></li> </ul> <p>PS. Insert and update statements always need special care when happening inside high traffic webpages. Select statements are much easier and you shouldn't worry about them as long as you have proper indexes</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload