E23 wrote:
Can you elaborate on how performance problems caused by slow sockets may manifest themselves? I executed the following query, which extract 17 MB of blobs in 1.5 seconds (on a slow computer). Are you perhaps referring to the general slowness added by retrieving data over network connections instead of using local files?
$ time echo "SELECT cur_text FROM cur"|mysql wikidb >/dev/null real 0m1.449s user 0m0.990s sys 0m0.130s
Here, "time" measures "echo", you should rather "echo | time mysql", but I think the resulting "real" (wall-clock) time is the same. If you pipe the output to "wc -c", you will make sure all data is retrieved and not just an error message.
These numbers look really good. But you should include PHP's client interface to the database in your tests. Write a little PHP script that makes repeated SQL statements to retrieve a total of 100 MB of blob data, call that script once with wget, and measure the response time. Note that the 100 MB data shouldn't be returned over HTTP (we're not measuring Apache's or wget's performance), only be retrieved through the PHP--MySQL connection.
You could preload your database with blob data and design the test so that you can vary the number of SQL calls and the number of records retrieved per call (1000 calls x 0.1 MB, or 10 calls x 10 MB) and see if that has any impact. Running truss (Solaris) or strace (Linux) on the Apache/PHP process during the call can also reveal what is going on under the hood, especially the timing of the read(2) calls on the database socket.
Whether the database is on the same server or accessed over a local network doesn't necessarily have any impact on the performance or bandwidth. Of course, you will never see more than 12.5 MB/s over a 100 Mbit/s Fast Ethernet. Disk I/O speeds (typically 40 MB/s) isn't necessarily a limitation if the database server already caches all the selected blob data in RAM.