CODEWITHSUNDEEP
mysqlconcurrencydatabase-connectionconnection-poolingxml-rpc
user1769562
user1769562

Reputation: 13

Concurrent database connections- how is it counted

Our mysql hoster has a limit of concurrent db connections. As it is rather pricey to expand that limit the following question came up:

Info: I have a web app (been developed by an external coder- in case you might wonder about this question).

The web app is distributed and installed on many servers (every user installs it on their pc). These satellites are sending data into a mysql db. Right now the satellites are posting into the db directly.To improve security and error handling i would like to have the satellites posting to a XML-rpc (wordpress api) which then further posts into the db.

Question: would such api reduce number of concurrent connections or not? (right now as every satellite connects directly. It is like 1 user = 1 connection)

If 10 satellites are posting to one file, this file then processes the data and posts them into the db -> has this been one connection? (or as many connections as different data sets have been processed.)

What if the api throttles a little bit, so as there is only posting at a time. Would this lead to just one connection or not?

Any pointers are well appreciated! Thank you in advance!

Upvotes: 0

Views: 3920

Answers (2)

classicjonesynz
classicjonesynz

Reputation: 4042

If you want to improve concurrent connections to the database (because the fact is, creating a connection to the database is "expensive"). You should look into using a ConnectionPool (example with Java).

How are concurrent database connections counted?

concurrency
(source: iforce.co.nz)

A connectionless server

  • Uses a connectionless IPC API (e.g., connectionless datagram socket)
  • Sessions with concurrent clients can be interleaved.

A connection-oriented server

  • Uses a connection-oriented IPC API (e.g. stream-mode socket )
  • Sessions with concurrent clients can only be sequential unless the server is threaded.

(Client-server distributed computing paradigm, N.A.)

Design and Performance Issues

  • Database connections can become a bottleneck. This can be addressed by using connection pools.

  • Compiled SQL statements can be re-used by using PreparedStatements instead of statements. These statements can be parameterized.

  • Connections are usually not created directly by the servlet but either created using a factory (DataSource) or obtained from a naming service (JNDI).

  • It is important to release connections (close them or return them to the connection pool). This should be done in a finally clause (so that it is done in any case). Note that close() also throws an exception!

        try
    {
        Console.WriteLine("Executing the try statement.");
        throw new NullReferenceException();
    }

    catch (NullReferenceException e)
    {
        Console.WriteLine("{0} Caught exception #1.", e);
    }

    catch
    {
        Console.WriteLine("Caught exception #2.");
    }

    finally
    {
        Console.WriteLine("Executing finally block.");
    }

  • There are various problems when developing interfaces between OO and RDBMS. This is called the “paradigm mismatch”. The main problem is that databases use reference by value while OO languages use reference by address. So-called middleware/ object persistency framework software tries to ease this.

Dietrich, R. (2012). Web Application Architecture, Server Side Scripting Servlets. Palmerston North: Massey University.

Upvotes: 1

jagra
jagra

Reputation: 553

It depends on the way you implement the centralized service.

If the service after receiving a request immediatly posts the data to mysql, you may have many connections if there are simultaneous requests. But using connection pooling you can control precisely how many open connections you will have. In the limit, you can have just one connection open. This might cause contention if there are many concurrent requests as each request has to wait for the connection to be released.

If the service receives requests, store them in some place (other then the database), and processes them in chunks, you can also have just one connection. But this case is more complex to implement because you have to control the access (reading and writing) to the temporary data buffer.

Upvotes: 1

Related Questions