Using a command
Option 1
show status where variable_name = 'threads_connected';
Columns
- Variable_name - Name of the variable shown
- Value - Number of active connections
Rows
- One row: Only one row is displayed
Sample results
Option 2
show processlist;
Columns
- Id - The connection identifier
- User - The MariaDB user who issued the statement
- Host - Host name and client port of the client issuing the statement
- db - The default database (schema), if one is selected, otherwise NULL
- Command - The type of command the thread is executing
- Time - The time in seconds that the thread has been in its current state
- State - An action, event, or state that indicates what the thread is doing
- Info - The statement the thread is executing, or NULL if it is not executing any statement
- Progress - The total progress of the process (0-100%)
Rows
- One row: represents one active connection
- Scope of rows: total of active connections
Sample results
Using a query
Option 3
select id, user, host, db, command, time, state,
info, progress from information_schema.processlist;
Columns
- Id - The connection identifier
- User - The MariaDB user who issued the statement
- Host - Host name and client port of the client issuing the statement
- db - The default database (schema), if one is selected, otherwise NULL
- Command - The type of command the thread is executing
- Time - The time in seconds that the thread has been in its current state
- State - An action, event, or state that indicates what the thread is doing
- Info - The statement the thread is executing, or NULL if it is not executing any statement
- Progress - The total progress of the process (0-100%)
- memory_used - Amount of memory used by the active connection
Rows
- One row: represents one active connection
- Scope of rows: total of active connections
Sample results
Using the GUI
Option 4
Click on the Client Connections option of the Management tab (left navigation pane)
This action will show the Client Connections screen containing the current active connections
Database Profiling
MongoDB Profiler is a db profiling system that can help identify inefficient
or slow queries and operations.
Levels of profiles available are:
Level | Setting |
0 | Off. & No profiling |
1 | On & only includes slow operations |
2 | On & Includes all operations |
We can enable it by setting the Profile level value using the following
command in mongo shell :
"db.setProfilingLevel(1)"
By default, mongod records slow queries to its log, as defined by slowOpThresholdMs.
NOTE
Enabling database profiler puts negative impact on MongoDB’s performance.
It’s better to enable it for specific intervals & minimal on Production Servers.
We can enable profiling on a mongod basis but This setting will not propagate
across a replica set and sharded cluster.
We can view the output in the system.profile collection in mongo shell using show profile command, or using following:
db.system.profile.find( { millis : { $gt : 200 } } )
Command returns operations that took longer than 200 ms. Similarly we
can change the values as per our need.
Enabling profile for an entire mongod instance.
For the purpose of development in testing, we can enable database profiling/settings for an
entire mongod instance. The profiling level will be applied to all databases.
NOTE:
We can't enable the profiling settings on a mongos instance. To enable the profiling in
shard clusters, we have to enable/start profiling for each mongod instance in cluster.
Query for the recent 10 entries
db.system.profile.find().limit(10).sort( { ts : 1 } ).pretty()
Collection with the slowest queries(No. Of queries)
db.system.profile.group({key: {ns: true}, initial: {count: 0}, reduce: function(obj,prev){ prev.count++;}})
Collection with the slowest queries(No. Of millis spent)
db.system.profile.group({key: {ns: true}, initial: {millis: 0}, reduce: function(obj, prev){ prev.millis += obj.millis;}})
Most recent slow query
db.system.profile.find().sort({$natural: -1}).limit(1)
Single slowest query(Right now)
db.system.profile.find().sort({millis: -1}).limit(1)
１．echo "mount -t nfs -o nolock ${IP}:${remote_dir} ${local_dir}" >> /etc/rc.local
２．echo "${IP}:/home/logs /home/logs nfs defaults 0 0" >> /etc/fstab
關(guān)于/etc/rc.local
rc.local也是我經(jīng)常使用的一個(gè)腳本。該腳本是在系統(tǒng)初始化級(jí)別腳本運(yùn)行之后再執(zhí)行的，因此可以安全地在里面添加你想在系統(tǒng)啟動(dòng)之后執(zhí)行的腳本。常見(jiàn)的情況是你可以再里面添加nfs掛載/mount腳本。此外，你也可以在里面添加一些調(diào)試用的腳本命令。例如，我就碰到過(guò)這種情況：samba服務(wù)總是無(wú)法正常運(yùn)行，而檢查發(fā)現(xiàn)，samba是在系統(tǒng)啟動(dòng)過(guò)程中就該啟動(dòng)執(zhí)行的，也就是說(shuō)，samba守護(hù)程序配置保證了這種功能本應(yīng)該正確執(zhí)行。碰到這種類(lèi)似情況，一般我也懶得花大量時(shí)間去查為什么，我只需要簡(jiǎn)單的在/etc/rc.local腳本里加上這么一行：
/etc/init.d/samba start
這樣就成功的解決了samba服務(wù)異常的問(wèn)題。
使用自定義CONNECTION FACTORY，這樣會(huì)覆蓋SPRING 的AUTO CONFIGURATION。
ActiveMQConnectionFactoryFactory.java
import java.lang.reflect.InvocationTargetException;
import java.util.Collections;
import java.util.List;
import org.apache.activemq.ActiveMQConnectionFactory;
import org.springframework.boot.autoconfigure.jms.activemq.ActiveMQConnectionFactoryCustomizer;
import org.springframework.boot.autoconfigure.jms.activemq.ActiveMQProperties;
import org.springframework.boot.autoconfigure.jms.activemq.ActiveMQProperties.Packages;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;
/**
* Factory to create a {@link ActiveMQConnectionFactory} instance from properties defined
* in {@link SecondaryActiveMQProperties}.
*
* @author Phillip Webb
* @author Venil Noronha
*/class ActiveMQConnectionFactoryFactory {
private static final String DEFAULT_EMBEDDED_BROKER_URL = "vm://localhost?broker.persistent=false";
private static final String DEFAULT_NETWORK_BROKER_URL = "tcp://localhost:61616";
private final ActiveMQProperties properties;
private final List<ActiveMQConnectionFactoryCustomizer> factoryCustomizers;
ActiveMQConnectionFactoryFactory(ActiveMQProperties properties,
List<ActiveMQConnectionFactoryCustomizer> factoryCustomizers) {
Assert.notNull(properties, "Properties must not be null");
this.properties = properties;
this.factoryCustomizers = (factoryCustomizers !=
null) ? factoryCustomizers : Collections.emptyList();
}
public <T
extends ActiveMQConnectionFactory> T createConnectionFactory(Class<T> factoryClass) {
try {
return doCreateConnectionFactory(factoryClass);
}
catch (Exception ex) {
throw new IllegalStateException("Unable to create " + "ActiveMQConnectionFactory", ex);
}
}
private <T
extends ActiveMQConnectionFactory> T doCreateConnectionFactory(Class<T> factoryClass)
throws Exception {
T factory = createConnectionFactoryInstance(factoryClass);
if (
this.properties.getCloseTimeout() !=
null) {
factory.setCloseTimeout((
int)
this.properties.getCloseTimeout().toMillis());
}
factory.setNonBlockingRedelivery(
this.properties.isNonBlockingRedelivery());
if (
this.properties.getSendTimeout() !=
null) {
factory.setSendTimeout((
int)
this.properties.getSendTimeout().toMillis());
}
Packages packages =
this.properties.getPackages();
if (packages.getTrustAll() !=
null) {
factory.setTrustAllPackages(packages.getTrustAll());
}
if (!packages.getTrusted().isEmpty()) {
factory.setTrustedPackages(packages.getTrusted());
}
customize(factory);
return factory;
}
private <T
extends ActiveMQConnectionFactory> T createConnectionFactoryInstance(Class<T> factoryClass)
throws InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
String brokerUrl = determineBrokerUrl();
String user =
this.properties.getUser();
String password =
this.properties.getPassword();
if (StringUtils.hasLength(user) && StringUtils.hasLength(password)) {
return factoryClass.getConstructor(String.
class, String.
class, String.
class).newInstance(user, password,
brokerUrl);
}
return factoryClass.getConstructor(String.
class).newInstance(brokerUrl);
}
private void customize(ActiveMQConnectionFactory connectionFactory) {
for (ActiveMQConnectionFactoryCustomizer factoryCustomizer :
this.factoryCustomizers) {
factoryCustomizer.customize(connectionFactory);
}
}
String determineBrokerUrl() {
if (
this.properties.getBrokerUrl() !=
null) {
return this.properties.getBrokerUrl();
}
if (
this.properties.isInMemory()) {
return DEFAULT_EMBEDDED_BROKER_URL;
}
return DEFAULT_NETWORK_BROKER_URL;
}
}
TwinJmsConnectionFactoryConfiguration.java
import java.util.stream.Collectors;
import org.apache.activemq.ActiveMQConnectionFactory;
import org.messaginghub.pooled.jms.JmsPoolConnectionFactory;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jms.JmsPoolConnectionFactoryFactory;
import org.springframework.boot.autoconfigure.jms.activemq.ActiveMQConnectionFactoryCustomizer;
import org.springframework.boot.autoconfigure.jms.activemq.ActiveMQProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.Profile;
@Configuration
@Profile({"local"})
public class TwinJmsConnectionFactoryConfiguration {
@Bean
@ConfigurationProperties(prefix = "spring.activemq.primary")
public ActiveMQProperties primaryActiveMQProperties() {
return new ActiveMQProperties();
}
@Bean(destroyMethod = "stop")
@Primary
@ConditionalOnProperty(prefix = "spring.activemq.pool", name = "enabled", havingValue = "true")
public JmsPoolConnectionFactory connectionFactory(ActiveMQProperties primaryActiveMQProperties,
ObjectProvider<ActiveMQConnectionFactoryCustomizer> factoryCustomizers) {
ActiveMQConnectionFactory connectionFactory =
new ActiveMQConnectionFactoryFactory(primaryActiveMQProperties,
factoryCustomizers.orderedStream().collect(Collectors.toList()))
.createConnectionFactory(ActiveMQConnectionFactory.
class);
return new JmsPoolConnectionFactoryFactory(primaryActiveMQProperties.getPool())
.createPooledConnectionFactory(connectionFactory);
}
////////////////////////////////////////////////////////////////////////////////
@Bean
@ConfigurationProperties(prefix = "spring.activemq.sescond")
public ActiveMQProperties sescondActiveMQProperties() {
return new ActiveMQProperties();
}
@Bean(destroyMethod = "stop")
@ConditionalOnProperty(prefix = "spring.activemq.pool", name = "enabled", havingValue = "true")
public JmsPoolConnectionFactory sescondPooledJmsConnectionFactory(ActiveMQProperties sescondActiveMQProperties,
ObjectProvider<ActiveMQConnectionFactoryCustomizer> factoryCustomizers) {
ActiveMQConnectionFactory connectionFactory =
new ActiveMQConnectionFactoryFactory(sescondActiveMQProperties,
factoryCustomizers.orderedStream().collect(Collectors.toList()))
.createConnectionFactory(ActiveMQConnectionFactory.
class);
return new JmsPoolConnectionFactoryFactory(sescondActiveMQProperties.getPool())
.createPooledConnectionFactory(connectionFactory);
}
}
這時(shí)發(fā)現(xiàn)如果處理總數(shù)足夠大時(shí)，被處理的ITEMS總數(shù)會(huì)少于應(yīng)該處理的總數(shù)。
+------------+--------------+-------------+-----------------+------------------+--------------------+----------------+-----------+-------------------------
| READ_COUNT | FILTER_COUNT | WRITE_COUNT | READ_SKIP_COUNT | WRITE_SKIP_COUNT | PROCESS_SKIP_COUNT | ROLLBACK_COUNT | EXIT_CODE | EXIT_MESSAGE
-+------------+--------------+-------------+-----------------+------------------+--------------------+----------------+-----------+-------------------------
| 1 | 0 | 1 | 0 | 0 | 0 | 0 | COMPLETED |
| 30006 | 0 | 29947 | 0 | 59 | 0 | 61 | COMPLETED | Waited for 101 results.
To see how many connections are configured for your DB to use:
select @@max_connections;
To change it:
set global max_connections = 200;
To see how many are connected at the current time:
show processlist;
vi /etc/my.cnf
[mysqld]
max_connections = 500