Tag Info

Hot answers tagged

9

"Can support" != "optimal throughput". You can use lots of connections, but it's slower. If you use fewer connections and queue work, you get the same amount of work done in a smaller time. Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well? ...


8

Here's another alternative formula in sproc form: DELIMITER // CREATE PROCEDURE sproc_show_max_memory ( OUT max_memory DECIMAL(7,4)) BEGIN SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + ...


8

Going to post this as an answer, with the relevant information. The basic formulas are: Available RAM = Global Buffers + (Thread Buffers x max_connections) max_connections = (Available RAM - Global Buffers) / Thread Buffers To get the list of buffers and their values: SHOW VARIABLES LIKE '%buffer%'; Here's a list of the buffers and whether ...


6

Please notice the makeup of mysql.user: mysql> show create table mysql.user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Password` char(41) CHARACTER SET latin1 ...


6

This limit applies to the total TCP socket connections and Unix socket connections, combined, so connecting via the Unix socket won't help you if your connection count reaches the configured max. However, the slot reserved for a user with SUPER privilege is not a single slot in the way your question implies. Any time the server's current client thread ...


5

I wrote about six potential issues with parallelism here: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx That was three years ago, and I'm sure other equally dangerous bugs have come up since I wrote that. I haven't gone through and validated any that have been reported as fixed, but the ...


3

Being able to commit more CPU cores to the processing of a request is a good thing, until the time it takes to reassemble the results exceeds the gain obtained from distributing the workload. Check to see if you have high CXPACKET wait time. If you don't, then no worries. That article gives you some things to try if you want to gain more understanding on ...


3

It doesn't come down because it isn't supposed to come down. Max_used_connections is a status variable, and is defined as "the maximum number of connections that have been in use simultaneously since the server started." It turns out, you can also reset the value to the current number of connections using FLUSH STATUS. But that isn't a "partial ...


2

2000 connections in TIME_WAIT state does not look that much. Compare this to the output of: # cat /proc/sys/net/ipv4/ip_local_port_range which would typically be 32768 61000 and can be enlarged if necessary. It would be a problem if you exhausted that range, though. If you're worried about that, you could set /proc/sys/net/ipv4/tcp_tw_reuse to 1, which ...


2

1500 to 2200 concurrent connections are only useful when you need them. Otherwise, bring the total number of open connections down and start using a connection pool like pgBouncer or PgPool-II. See Number of Connections (on the wiki). We do about 2500 transactions per second on 150 connections. Don't forget, each connection uses RAM, RAM that could be used ...


2

The best way to see current threads (including all foreground and background threads) is now with performance_schema. For example: mysql 5.7.3> select thread_id, name, type, PROCESSLIST_ID from performance_schema.threads; +-----------+----------------------------------------+------------+----------------+ | thread_id | name ...


2

A lot of applications have poor connection discipline, keeping connections open even when they are not being used. Setting a high connection limit is cheap insurance against these applications. Until something changes and the applications decide to actively use all of those connections, then the insurance becomes pretty expensive.


2

If 'host_name' is blocked because of many connection errors. then to Unblock only options **mysqladmin flush-hosts** unblock link


2

According to MySQL documentation the Connections status variable shows "The number of connection attempts (successful or not) to the MySQL server." This means that it does not represent current open connections, but all connection attempts since the server has been started. The variable showing number of current open connections, again according to MySQ ...


1

Before deciding on value for max_connections I suggest that you look at WHY it reached the max_connection condition. A bunch of sleeping/idle connection due to loose coding is very common cause. Check if you have proper value for wait_timeout and adjust if this is your symptom. Besides that, I would check for INSERT/UPDATE query that holds table locks for ...


1

What do you consider to be high-traffic? Turn off Named Pipes and turn on TCPIP It's lighter in it's communication. In my SQL installations, I never use Named Pipes except if the Application is on the same machine as the SQL service, and that's really ... never ... I always separate the application and SQL Server because monitoring and performance measures ...


1

You can enable "general query log", which will log each executed query, along with its session/connection ID, so if you grep the connection ID, you will have all the queries on one session. HOWEVER, 'sleep'ing connections are not the cause of max connection error, but a result of it. They are not harmful to stay there. In fact, they are established ...


1

Seems like we can do it : To configure the user connections option In Object Explorer, right-click a server and click Properties. Click the Connections node. Under Connections, in the Max number of concurrent connections box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect ...


1

Max_connections is The total number of concurrent connections allowed by the MySQL server. If you are getting Too many connections' error, it means that you have reached this limit. Also, You should observe threads_running variable value for a period of time to determine exact value for 'max_connections'. You can consider max_connection value as ...


1

On Linux for example, whichever is smaller among: grep -F 'Max process' /proc/$(/sbin/pidof mysqld)/limits|awk '{print $3}' and: mysql -e'show global variables like "max_connections"' UPDATE: As you correctly pointed out, we should add at least the background threads to the list. More information below. As far as your last update is concerned, for ...


1

You can see that command = 'Sleep' these indicates that your server's connection thread are idle and is waiting for the client to send a new statement to it. You do not have to worry about it. Make sure that max_connections value is more than default and your application closes connection after it's internal operation.


1

You will need to look into how your connection pool is configured in your PHP web app. Even a fully functional connection pool will often keep many connections to a database open even when they are not in use. Opening connections to the database can be very expensive for a web app and it makes sense to have some unused ones sitting around. Most connection ...


1

Yes, pgbouncer creates a separate pool for each database. You really don't want a pool size as big as 250 in a scenario like this - because that is 250 connections from pgbouncer to the postgres server for each database. The max of 480 is the total number of connections from clients into pgbouncer, regardless of which database they go into.


1

Three points: I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate. I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.." You haven't listed the ...


1

I once wrote about the kind of things to tweak when seeing output from mysqltuner.pl : Understanding mysqltuner Recomendations w/ Unused Catalogs Here is the kind of output it generates >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with ...


1

SET @@GLOBAL.wait_timeout = 30; Have your users disconnect when they are through. Do not have more than 20 for MaxClients in Apache.


1

Try increasing max_allowed_packet size you may get rid of these errors. Please refer these links may be helpful in debugging this issue: http://www.webyog.com/blog/2009/08/10/mysql-server-has-gone-away-part-1-max_allowed_packet/ http://www.webyog.com/blog/2009/09/02/%E2%80%9Cmysql-server-has-gone-away%E2%80%9D-part-2-session-timeout/



Only top voted, non community-wiki answers of a minimum length are eligible