MyISAM加锁分析

为什么加锁

你正在读着你喜欢的女孩递给你的信,看到一半的时候,她的好闺蜜过来瞄了一眼(假设她会隐身术,你看不到她),她想把“我很喜欢你”改成“我不喜欢你”,刚把“很”字擦掉,“不”字还没写完,只写了一横一撇,这时候你正读到这个字,她怕你察觉到也就没继续往下写了,这时候你读到的这句话就是“我丆喜欢你”,这是什么鬼?!这位闺蜜乐了:没错,确实是鬼在整蛊你呢,嘿嘿!

数据库也会闹鬼吗?很有可能!假设会话1正在读取表里的一条记录(还没读取完),另一个会话2突然插队过来更新表里的同一条记录(还没更新完),那么会话1拿到的数据就可能是错误的(还没更新完的内容和原内容混在一起,造成乱码,就像上面的“我丆喜欢你”)。

怎么避免这种情况呢?加锁,当有一个人在读的时候,别人能读不能写,当有一个人在写的时候,别人不能读和写。

所以,加锁是为了在并发操作的时候,能够确保数据的完整性和一致性。

加锁的规则

MyISAM锁的粒度是表级锁,在执行查询(SELECT)之前,尝试在表上面加读锁,在执行更新(UPDATE,DELETE,INSERT)之前,尝试在表上面加写锁。

加写锁:

如果在表上没有锁(读锁和写锁),在它上面放一个写锁。
否则,把锁定请求放在写锁定队列中。

加读锁:

如果在表上没有写锁定,把一个读锁定放在它上面。
否则,把锁定请求放在读锁定队列中。

优先级:

当一个锁定被释放时,锁定优先被写锁定队列中的线程得到,然后是读锁定队列中的线程。这意味着如果有大量的写操作,读操作将会一直等待,直到写完成。可以通过以下命令看到加锁的情况:

SHOW STATUS LIKE 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 42    |
| Table_locks_waited    | 3     |
+-----------------------+-------+

Table_locks_immediate是加锁立刻执行成功的次数,Table_locks_waited是造成等待的加锁次数。另外,可以通过LOW_PRIORITY来改变优先级

实例分析

开一个会话窗口1,输入下面的语句执行:

CREATE TABLE `users`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='用户';

INSERT INTO `users` VALUES (null, 'pigfly'),(null,'zhupp');

为了模拟,我们手动执行LOCK TABLES语句把表锁住:

LOCK TABLES `users` READ LOCAL;
SELECT * FROM `users`;
UPDATE `users` SET name='aa' where id=1;

SELECT正常返回,UPDATE报错了,原因是当前表加了读锁,则当前会话只能执行读操作,不能执行更新操作。

新开一个会话窗口2:

INSERT INTO `users` VALUES (null, 'zhupp');
UPDATE `users` SET name='xxx' where id=1;

可以看到插入执行成功,但是UPDATE操作被窗口1加的读锁阻塞了,我们回到窗口1执行:

UNLOCK TABLES;

这时候窗口2的更新语句马上返回更新成功了。

为什么插入不会被读锁阻塞呢?原因是当表加了读锁并且表不存在空闲块的时候(删除或者更新表中间的记录会导致空闲块,OPTIMIZE TABLE可以清除空闲块),MYISAM默认允许其他线程从表尾插入。可以通过改变系统变量concurrent_insert(并发插入)的值来控制并发插入的行为。

SHOW VARIABLES LIKE 'concurrent%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+

Value的值:

  • NEVER(0): 不允许并发插入
  • AUTO(1): 表里没有空行时允许从表尾插入(默认)
  • ALWAYS(2): 任何时候都允许并发插入

注意:锁表的时候加了LOCAL关键字表示允许走并发插入的逻辑,具体是否可以并发插入还需要看是否满足concurrent_insert指定的条件,只有手动锁表的时候才需要指定LOCAL关键字。

测试一下当表里有空闲块的情况,窗口1执行:

DELETE FROM `users` WHERE id=1;
LOCK TABLES `users` READ LOCAL;

然后在窗口2执行:

INSERT INTO `users` VALUES (null, 't1');

果然被阻塞了。我们把并发插入的值改成2试试,在窗口1执行:

UNLOCK TABLES;
SET GLOBAL concurrent_insert=2;
DELETE FROM `users` WHERE id=2;
LOCK TABLES `users` READ LOCAL;

然后在窗口2执行:

INSERT INTO `users` VALUES (null, 't2');
SELECT * FROM `users`;

这一次没有被阻塞,插入成功了。

表级锁的特点

开销小、加锁快、不会产生死锁,锁定力度大,发生锁冲突的概率最高,不适合高并发场景。

性能优化

  1. 对于并发插入,一般默认配置AUTO就可以了,如果有大量插入操作,可以把concurrent_insert设置为2,然后定期在流量低峰期执行OPTIMIZE TABLE来清除空闲块。
  2. 调整优先级。
  3. 在大量更新操作前手动锁表,这样锁表只执行了一次,不然每执行一次更新就锁一次表。
  4. 存在大量更新操作造成等待,又要兼顾查询的时候,给max_write_lock_count设置一个低值,在写锁达到一定数量时允许执行挂起的读请求。

Amazon Comprehend 自然语言处理,PHP使用教程,调用示例

Amazon Comprehend 是一项自然语言处理 (NLP) 服务,使用机器学习来发现文本中的洞察信息。Amazon Comprehend 提供关键词提取、情绪分析、实体识别、主题建模和语言检测 API,因此您可以轻松地将自然语言处理集成到您的应用程序中。您只需在应用程序中调用相应 Amazon Comprehend API 并提供源文档或文本的位置即可。这些 API 将以 JSON 格式输出实体、关键词、情绪和语言,以供您在应用程序中使用。

一、通过Composer安装SDK(需要安装aws/aws-sdk-php软件包)

composer require aws/aws-sdk-php

https://packagist.org/packages/aws/aws-sdk-php

或下载独立的AWS来开始使用。 zip 或aws.phar文件。
https://docs.aws.amazon.com/aws-sdk-php/v3/download/aws.zip
二、非框架安装需要引用/vendor/autoload.php
use Aws\Comprehend\ComprehendClient;
use Aws\Exception\AwsException;

完整demo

<?php
namespace app\controller;

use app\BaseController;
use Aws\Comprehend\ComprehendClient;
use Aws\Textract\TextractClient;
use Aws\Exception\AwsException;
class Index extends BaseController
{
	/**
	 * doc:https://docs.aws.amazon.com/aws-sdk-php/v3/api/api-comprehend-2017-11-27.html#detectsentiment
	 */
    public function aws(){
	    $client=new ComprehendClient([
		    'version' => 'latest',
            //'version' => '2018-06-27',
		    'region' => 'us-west-2',
		    'credentials' => [
			    'key' => 'AKIAQHKSE4HRGDDDDDDD', //IAM user key
			    'secret' => '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', //IAM user secret
		    ]]);

	    /**
	     * 情绪分析
	     * 情绪分析 API 会返回文本的整体情绪(正面、负面、中性或混合)。
	     */

	    $result = $client->detectSentiment([
//	    'LanguageCode'=>'en | es | fr | de | it | pt | ar | hi | ja | ko | zh | zh-TW',//必填
	    'LanguageCode'=>'en',//必填
        'Text'=>'Your service attitude is really poor',//必填
		]);
	    print_r($result->toArray());
	    /*
		Array
		(
		    [Sentiment] => NEGATIVE
		    [SentimentScore] => Array
		        (
		            [Positive] => 6.2582264945377E-5
		            [Negative] => 0.99910479784012
		            [Neutral] => 0.00083179638022557
		            [Mixed] => 8.1932506645899E-7
		        )

		    [@metadata] => Array
		        (
		            [statusCode] => 200
		            [effectiveUri] => https://comprehend.us-west-2.amazonaws.com
		            [headers] => Array
		                (
		                    [x-amzn-requestid] => 7436bbaf-73c1-4462-a814-d29efe81522f
		                    [content-type] => application/x-amz-json-1.1
		                    [content-length] => 164
		                    [date] => Thu, 21 May 2020 08:46:28 GMT
		                )

		            [transferStats] => Array
		                (
		                    [http] => Array
		                        (
		                            [0] => Array
		                                (
		                                )

		                        )

		                )

		        )

		)
	    */
	    /**
	     * 语言检测
	     * 语言检测 API 可自动识别出 100 多种语言编写的文本,并返回主导语言以及证明其占据主导地位的置信度得分。
	     */

	    $result =$client->detectDominantLanguage([
		    'Text'=>'Your service attitude is really poor',//必填
	    ]);
	    print_r($result->toArray());
	    /*
	    Array
	    (
		    [Languages] => Array
		    (
			    [0] => Array
			    (
				    [LanguageCode] => en
				    [Score] => 0.98973816633224
                )

            )

		    [@metadata] => Array
			    (
				    [statusCode] => 200
		            [effectiveUri] => https://comprehend.us-west-2.amazonaws.com
		            [headers] => Array
			    (
				    [x-amzn-requestid] => e4a6f3cd-5678-46b7-abd3-51d81e28b69f
			    [content-type] => application/x-amz-json-1.1
		                    [content-length] => 64
		                    [date] => Thu, 21 May 2020 08:42:48 GMT
		                )

		            [transferStats] => Array
			    (
				    [http] => Array
				    (
					    [0] => Array
					    (
					    )

				    )

			    )

		        )

		)
	     */
	    /**
	     * 关键词提取
	     * 关键词提取 API 会返回关键词或谈话要点以及证明此为关键词的置信度。
	     */

	    $result =$client->detectKeyPhrases([
		    'LanguageCode'=>'en',//必填
		    'Text'=>'Your service attitude is really poor',//必填
	    ]);
	    print_r($result->toArray());
        /*
	    Array
	    (
		    [KeyPhrases] => Array
		    (
			    [0] => Array
			    (
				    [Score] => 1
                    [Text] => Your service attitude
	    [BeginOffset] => 0
                    [EndOffset] => 21
                )

        )

		    [@metadata] => Array
			    (
				    [statusCode] => 200
		            [effectiveUri] => https://comprehend.us-west-2.amazonaws.com
		            [headers] => Array
			    (
				    [x-amzn-requestid] => 18657c2a-28d9-432c-9e4b-b403ac597299
			    [content-type] => application/x-amz-json-1.1
		                    [content-length] => 92
		                    [date] => Thu, 21 May 2020 09:01:37 GMT
		                )

		            [transferStats] => Array
			    (
				    [http] => Array
				    (
					    [0] => Array
					    (
					    )

				    )

			    )

		        )

		)
        */


	    /**
	     * 实体识别
	     * 实体识别 API 会返回根据提供的文本自动分类的命名实体(“人物”、“地点”和“位置”等)。
	     */

	    $result =$client->detectEntities([
		    'LanguageCode'=>'en',//必填
		    'Text'=>'Your service attitude is really poor',//必填
	    ]);
	    print_r($result->toArray());
	    /*
	    Array
	    (
		    [Entities] => Array
		    (
		    )

		    [@metadata] => Array
			    (
				    [statusCode] => 200
		            [effectiveUri] => https://comprehend.us-west-2.amazonaws.com
		            [headers] => Array
			    (
				    [x-amzn-requestid] => 78db83bb-7bca-4381-82f8-06deb3e18546
			    [content-type] => application/x-amz-json-1.1
		                    [content-length] => 15
		                    [date] => Thu, 21 May 2020 09:04:33 GMT
		                )

		            [transferStats] => Array
			    (
				    [http] => Array
				    (
					    [0] => Array
					    (
					    )

				    )

			    )

		        )

		)
	     */

	    /**
	     * 语法分析
	     * 借助 Amazon Comprehend Syntax API,客户能够使用分词断句和词性 (PoS) 分析文本,识别文本中的名词和形容词等单词边界和标签。
	     */
	    $result =$client->detectSyntax([
		    'LanguageCode'=>'en',//必填
		    'Text'=>'Your service attitude is really poor',//必填
	    ]);
	    print_r($result->toArray());

	    /*
	    Array
	    (
		    [SyntaxTokens] => Array
		    (
			    [0] => Array
			    (
				    [TokenId] => 1
                    [Text] => Your
	    [BeginOffset] => 0
                    [EndOffset] => 4
                    [PartOfSpeech] => Array
				    (
					    [Tag] => PRON
					    [Score] => 0.99996387958527
			                        )

			                )

			            [1] => Array
				    (
					    [TokenId] => 2
			                    [Text] => service
				    [BeginOffset] => 5
			                    [EndOffset] => 12
			                    [PartOfSpeech] => Array
				    (
					    [Tag] => NOUN
					    [Score] => 0.9985853433609
			                        )

			                )

			            [2] => Array
				    (
					    [TokenId] => 3
			                    [Text] => attitude
				    [BeginOffset] => 13
			                    [EndOffset] => 21
			                    [PartOfSpeech] => Array
				    (
					    [Tag] => NOUN
					    [Score] => 0.99978679418564
			                        )

			                )

			            [3] => Array
				    (
					    [TokenId] => 4
			                    [Text] => is
				    [BeginOffset] => 22
			                    [EndOffset] => 24
			                    [PartOfSpeech] => Array
				    (
					    [Tag] => VERB
					    [Score] => 0.99935394525528
			                        )

			                )

			            [4] => Array
				    (
					    [TokenId] => 5
			                    [Text] => really
				    [BeginOffset] => 25
			                    [EndOffset] => 31
			                    [PartOfSpeech] => Array
				    (
					    [Tag] => ADV
					    [Score] => 0.9999988079071
			                        )

			                )

			            [5] => Array
				    (
					    [TokenId] => 6
			                    [Text] => poor
				    [BeginOffset] => 32
			                    [EndOffset] => 36
			                    [PartOfSpeech] => Array
				    (
					    [Tag] => ADJ
					    [Score] => 0.9997541308403
			                        )

			                )

			        )

			    [@metadata] => Array
				    (
					    [statusCode] => 200
			            [effectiveUri] => https://comprehend.us-west-2.amazonaws.com
			            [headers] => Array
				    (
					    [x-amzn-requestid] => 0eb35b66-d36a-43e8-94ee-b5b0c3e786eb
				    [content-type] => application/x-amz-json-1.1
			                    [content-length] => 722
			                    [date] => Thu, 21 May 2020 09:08:51 GMT
			                )

			            [transferStats] => Array
				    (
					    [http] => Array
					    (
						    [0] => Array
						    (
						    )

					    )

				    )

			        )

			)
	    */

    }
}

https://docs.aws.amazon.com/comprehend/latest/dg/comprehend-general.html

https://aws.amazon.com/cn/comprehend/features/

https://docs.aws.amazon.com/general/latest/gr/rande.html

https://docs.aws.amazon.com/comprehend/latest/dg/supported-languages.html

linux(centos)安装python

下载pthon安装包
https://www.python.org/downloads/source/

wget https://www.python.org/ftp/python/3.8.3/Python-3.8.3.tgz

创建安装目录

mkdir -p /usr/local/python3.8.3

解压

tar -zxvf Python-3.8.3.tgz

 编译安装 首先需要gcc环境和zlib库为了方向键等不出现乱码还需要 readline-devel 包

yum -y install gcc
yum -y install zlib*
yum install readline-devel
yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel libffi-devel make
cd Python-3.8.3
./configure --prefix=/usr/local/python3.8.3 --enable-optimizations --enable-shared --with-ssl

关于./configure --prefix=/usr/local/python3.8.3 --enable-optimizations --enable-shared --with-ssl 命令说明

--prefix 指定安装的路径,不指定的话,安装过程中可能软件所需要的文件复制到其他不同目录,删除软件很不方便,复制软件也不方便.
--enable-optimizations 可以提高python10%-20%代码运行速度.
--enable-shared 是为了安装Vim的插件:YouCompleteMe ,不通过此参数编译的话,后面安装该插件会出错
--with-ssl 是为了安装pip需要用到ssl,后面报错会有提到.
#编译安装
make && make install
建立软链接
ln -s /usr/local/python3.8.3/bin/python3.8 /usr/bin/python
ln -s /usr/local/python3.8.3/bin/pip3.8 /usr/bin/pip
[root@fd07bda1fc63 bin]# python --version
Python 3.8.3

坑一

[root@198e6d7f2d48 Python-3.8.3]# python --version
python: error while loading shared libraries: libpython3.8.so.1.0: cannot open shared object file: No such file or directory

原因是因为python运行时没有加载到libpython3.6m.so.1.0 这个库文件,将其复制到响应目录OK,输入下列命令即可
解决办法:

 cp /usr/local/python3.8.3/lib/libpython3.8.so.1.0 /usr/lib64/ 

坑二

WARNING: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.
Collecting Scrapy
  WARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/scrapy/
  WARNING: Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/scrapy/
  WARNING: Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/scrapy/
  WARNING: Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/scrapy/
  WARNING: Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/scrapy/
  Could not fetch URL https://pypi.org/simple/scrapy/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/scrapy/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping
  ERROR: Could not find a version that satisfies the requirement Scrapy (from versions: none)
ERROR: No matching distribution found for Scrapy
WARNING: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.
Could not fetch URL https://pypi.org/simple/pip/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/pip/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping

解决办法

./configure –prefix=/usr/local/python3.8.3 –enable-optimizations –enable-shared –with-ssl


–with-ssl 是为了安装pip需要用到ssl,后面报错会有提到.

pip install --upgrade pip  -i https://pypi.tuna.tsinghua.edu.cn/simple --trusted-host pypi.tuna.tsinghua.edu.cn
pip install scrapy    -i https://pypi.tuna.tsinghua.edu.cn/simple --trusted-host pypi.tuna.tsinghua.edu.cn