Tag Info

Hot answers tagged

7

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 + ...


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 ...


6

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 ...


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

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

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

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 ...


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

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

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

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