Make a little progress every day ———— Muma architecture optimization and application optimization

First, Muma architecture optimization

The current Business Server answer can be divided into three categories:

symmetric multiprocessor Architecture (symmetric MULTI-PROCESSOR,SMP): A collection of processing areas (multiple CPUs) on a single computer, shared memory subsystems and bus structures between CPUs. All CPUs have equal access to memory, I/O, and peripherals. The way to extend an SMP server is to increase memory, use faster CPUs, increase CPU I/O, and add more disks.

non-uniform storage access structure (Non-uniform Memory Access,muma): divide a server into multiple nodes, each node has a high CPU, the node internal use of public memory for the current electricity all CPUs are the same , and it does not work for all CPUs in other nodes. Therefore, each CPU can access the entire system memory, but access to the current power of the fastest memory, access to non-current memory speed is slower (need to go through the Interconnect module), that is, CPU access to memory speed and node distance, the distance becomes node Distance. 1. Displays the current NUMA node condition.

[root@empbjdbservb~]# numactl--hardware

Available:8 nodes (0-7) —————— The current node has 8 nodes

Node 0 cpus:0 1 2 3 4 5 6 7 64 65 66 67 6869 70-71

Node 0 size:32743 MB —————— node0 memory is approximately 32G

Node 0 free:18572 MB

Node 1 Cpus:8 9 10 11 12 13 14 15 72 73 7475 76 77 78-79

Node 1 size:32768 MB

Node 1 free:23453 MB

Node 2 cpus:16 17 18 19 20 21 22 23 80 8182 83 84 85 86-87

Node 2 size:32768 MB

Node 2 free:23279 MB

Node 3 cpus:24 25 26 27 28 29 30 31 88 8990 91 92 93 94-95

Node 3 size:32768 MB

Node 3 free:22283 MB

Node 4 cpus:32 33 34 35 36 37 38 39 96 9798 99 100 101 102-103

Node 4 size:32768 MB

Node 4 free:23631 MB

Node 5 cpus:40 41 42 43 44 45 46 47 104105 106 107 108 109 110-111

Node 5 size:32768 MB

Node 5 free:23718 MB

Node 6 cpus:48 49 50 51 52 53 54 55 112113 114 115 116 117 118-119

Node 6 size:32768 MB

Node 6 free:22356 MB

Node 7 cpus:56 57 58 59 60 61 62 63 120121 122 123 124 125 126-127

Node 7 size:32768 MB

Node 7 free:22234 MB

Node Distances:

Node 0 1 2 3 4 5 6 7

0:10 12 11 11 11 12 12 12

1:12 10 11 11 12 12 11 12

2:11 11 10 12 12 11 12 12

3:11 11 12 10 12 12 12 11

4:11 12 12 12 10 12 11 11

5:12 12 11 12 12 10 11 11

6:12 11 12 12 11 11 10 12

7:12 12 12 11 11 11 12 10

The distance between nodes is a form of the cost of accessing the memory on the 0 node from the 1 node. 2. The Muma memory allocation strategy has the following 4 kinds

Default defaults: Always assigned on the local node (assigned to the node on which the current process is running)

Bind bind: Force assignment to the specified node

Cross-interleave: cross-allocate memory across all nodes or specified nodes

Priority preferred: Allocated on the specified node, failure is assigned 3 on the other node . Show current system NUMA policies

[root@empbjdbservb~]# numactl--show

Policy:default

Preferred Node:current

Physcpubind:0 1 2 3 4 5 6 7 8 9 10 11 1213 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31-32 33 34 35 36 37 0 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 6465 66 67 68 69 70 71 72 73 74 75 76 77 78 79 1 82 83 84 85 86 87 88 89 9091 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112113 114 115 116 117 118 119 120 121 122 123 124 125 126 127

Cpubind:0 1 2 3 4 5 6 7

Nodebind:0 1 2 3 4 5 6 7

Membind:0 1 2 3 4 5 6 7

Because the NUMA default memory allocation policy is that wired is allocated in the CPU's local memory by a process lock, the CPU nodes are unbalanced, and when a CPU node is out of memory, it causes swap to occur instead of allocating memory from the remote node, which is the swap insanity phenomenon.

Because MySQL is but process multi-threaded database, in order to prevent the swap insanity phenomenon, so if the stand-alone only run a MySQL instance, you can choose to close the Muma. There are two ways to turn off Muma: Modify the/etc/grub.conf file and append Numa=off to the kernel line

[root@localhost~]# more/etc/grub.conf

# grub.conf generated by Anaconda

#

# You don't have to rerun grubafter making changes to this file

# notice:you have a/boot partition. Thismeans that

# all kernel and INITRD paths are relative to/boot/, eg.

# root (hd0,0)

# kernel/vmlinuz-version RO root=/dev/mapper/volgroup-lv_root

# initrd/initrd-[generic-]version.img

#boot =/DEV/SDA

Default=0

Timeout=5

Splashimage= (hd0,0)/grub/splash.xpm.gz

Hiddenmenu

Title Red Hat Enterprise Linux (2.6.32-358.el6.x86_64)

Root (hd0,0)

kernel/vmlinuz-2.6.32-358.el6.x86_64 roroot=/dev/mapper/volgroup-lv_root Rd_no_luks RD_NO_MDRD_LVM_LV=VOLGROUP/LV _swap crashkernel=128m LANG=ZH_CN. UTF-8 RD_LVM

_lv=volgroup/lv_root elevator=deadline keyboardtype=pc keytable=usrd_no_dm rhgb quiet

Initrd/initramfs-2.6.32-358.el6.x86_64.img

or modify the NUMA memory allocation policy to interleave through the MUMACTL command.

Modify the Mysqld_safe startup script and add the following information after cmd= "$NOHUP _niceness"

Cmd= "/usr/bin/numactl--interleave all $cmd"

, the specified memory allocation policy is interleave when you start MySQL

massive parallel processing architecture (massive Parallel processing,mpp): solves the problem that the NUMA architecture increases the CPU and does not linearly improve performance, as MPP is entered by a number of SMP servers via a certain node internet Row connection, each node value access to a subset of local resources (memory, storage, etc.), not to ask other nodes resources, is a share nothing of the architecture, so theoretically can be extended wirelessly. MPP architecture, the CPU of each node can not access the memory of other nodes, the information exchange between nodes is realized through the network of nodes, this process is called data redistribution. But MPP servers require a complex mechanism to schedule and balance the load and parallel processing of each node. At present, the server based on MPP technology, such as database software and other system software to shield the underlying complexity. second, application optimization 1. Use connection pooling

For access to a database, the cost of a resume connection is more expensive, so it is necessary to establish a "connection pool" to provide performance for high-performance access. 2. Reduce access to MySQL avoid duplicate retrieval of the same data

It is necessary to clarify the access logic of the database in the application. The ability to remove all results at once can be done without two connections, which can greatly reduce unnecessary duplication of access to the database.

using Query caching

MySQL's query cache is a new feature after version 4.1, which stores the text of a select query and the corresponding results. If you then receive an identical query, the server will get the query results back from the query cache without the need to parse and execute the query.

The use object of the query cache is a table that is not updated frequently, and the related entries for the query cache value are emptied when the table is changed.

 

There are several main parameters related to query caching:

mysql> Show variables like '%query_cache% ';

+------------------------------+---------+

| variable_name | Value |

+------------------------------+---------+

| Have_query_cache | YES | --whether the cache is configured on the server when it is installed

| Query_cache_limit | 1048576 |

| Query_cache_min_res_unit | 4096 |

| Query_cache_size | 1048576 | |-Indicates cache size, MB

| Query_cache_type | Off |-| variable values from 0 to 2, meaning: 0 or off (cache shutdown), 1 or on (cache open, except select with Sql_no_cache hint), 2 or demand (only a SELECT statement with Sql_cache provides caching)

| Query_cache_wlock_invalidate | Off |

+------------------------------+---------+

6 rows in Set (0.00 sec)

performance monitoring parameters for MySQL query cache

Mysql> Show status like ' qcache% ';

+-------------------------+---------+

| variable_name | Value |

+-------------------------+---------+

| Qcache_free_blocks |                        1 | --Total free memory space for query cache

| Qcache_free_memory |                     1031368 | --The number of free memory queries cached

| Qcache_hits |                      0 | --Number of cached samples

| Qcache_inserts |                       0 | --Number of queries added to the cache

| Qcache_lowmem_prunes |                   0 | -The number of queries removed from the cache because of lack of memory

| qcache_not_cached |                     2 | --Number of queries not cached (cannot or due to Query_chache_type)

| Qcache_queries_in_cache |                          0 | --Number of queries registered in the cache

| Qcache_total_blocks |                         1 | --Total number of blocks in query cache

+-------------------------+---------+

8 rows in Set (0.00 sec) Add cache layer

In the application, we can reduce the burden of the database by adding Chache layer to the application end.