存档

文章标签 ‘mysql’

mariabdb 修改数据库目录

2018年1月22日 没有评论

1、停止mysql
2、mv /var/lib/mysql/ /home/data
3、chown mysql:mysql /home/data/mysql/ -R
4、chmod 777 /home/data/mysql/ -r
5、修改nano /etc/mysql/mariadb.conf.d/50-server.cnf 中datadir
6、修改/lib/systemd/system/mariadb.service protecthome=false

分类: 技术 标签: ,

What to tune in MySQL Server after installation

2007年2月10日 没有评论

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

[b]key_buffer_size[/b] – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

[b]innodb_buffer_pool_size[/b] This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

[b]innodb_additional_pool_size[/b] This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

[b]innodb_log_file_size[/b] Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

[b]innodb_log_buffer_size[/b] Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

[b]innodb_flush_logs_at_trx_commit[/b] Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

[b]table_cache[/b] – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

[b]thread_cache[/b] Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

[b]query_cache[/b] If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.

分类: 未分类 标签: , ,

mysql工具

2006年11月14日 没有评论

http://www.ozerov.de/bigdump.php

分类: 未分类 标签: , , ,

MYSQL字符串处理函数

2006年11月13日 没有评论
注意:如果结果的长度大于服务器参数max_allowed_packet,字符串值函数返回NULL
  对于针对字符串位置的操作,第一个位置被标记为1。
  ASCII(str) 
  返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。 
  mysql> select ASCII(‘2’); 
          -> 50 
  mysql> select ASCII(2); 
          -> 50 
  mysql> select ASCII(‘dx’); 
          -> 100 
  也可参见ORD()函数。
  ORD(str) 
  如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code…]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。 
  mysql> select ORD(‘2’); 
          -> 50
  CONV(N,from_base,to_base) 
  在不同的数字基之间变换数字。返回数字N的字符串数字,从from_base基变换为to_base基,如果任何参数是NULL,返回NULL。参数N解释为一个整数,但是可以指定为一个整数或一个字符串。最小基是2且最大的基是36。如果to_base是一个负数,N被认为是一个有符号数,否则,N被当作无符号数。 CONV以64位点精度工作。 
  mysql> select CONV(“a”,16,2); 
          -> ‘1010’ 
  mysql> select CONV(“6E”,18,8); 
          -> ‘172’ 
  mysql> select CONV(-17,10,-18); 
          -> ‘-H’ 
  mysql> select CONV(10+”10″+’10’+0xa,10,10); 
          -> ’40’
  BIN(N) 
  返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2)。如果N是NULL,返回NULL。 
  mysql> select BIN(12); 
          -> ‘1100’
  OCT(N) 
  返回八进制值N的一个字符串的表示,在此N是一个长整型数字,这等价于CONV(N,10,8)。如果N是NULL,返回NULL。 
  mysql> select OCT(12); 
          -> ’14’
  HEX(N) 
  返回十六进制值N一个字符串的表示,在此N是一个长整型(BIGINT)数字,这等价于CONV(N,10,16)。如果N是NULL,返回NULL。 
  mysql> select HEX(255); 
          -> ‘FF’
  CHAR(N,…) 
  CHAR()将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串。NULL值被跳过。 
  mysql> select CHAR(77,121,83,81,’76’); 
          -> ‘MySQL’ 
  mysql> select CHAR(77,77.3,’77.3′); 
          -> ‘MMM’
  CONCAT(str1,str2,…) 
  返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。 
  mysql> select CONCAT(‘My’, ‘S’, ‘QL’); 
          -> ‘MySQL’ 
  mysql> select CONCAT(‘My’, NULL, ‘QL’); 
          -> NULL 
  mysql> select CONCAT(14.3); 
          -> ‘14.3’
  LENGTH(str) 
    
  OCTET_LENGTH(str) 
    
  CHAR_LENGTH(str) 
    
  CHARACTER_LENGTH(str) 
  返回字符串str的长度。 
  mysql> select LENGTH(‘text’); 
          -> 4 
  mysql> select OCTET_LENGTH(‘text’); 
          -> 4
  注意,对于多字节字符,其CHAR_LENGTH()仅计算一次。
  LOCATE(substr,str) 
    
  POSITION(substr IN str) 
  返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0. 
  mysql> select LOCATE(‘bar’, ‘foobarbar’); 
          -> 4 
  mysql> select LOCATE(‘xbar’, ‘foobar’); 
          -> 0
  该函数是多字节可靠的。   
  LOCATE(substr,str,pos) 
  返回子串substr在字符串str第一个出现的位置,从位置pos开始。如果substr不是在str里面,返回0。 
  mysql> select LOCATE(‘bar’, ‘foobarbar’,5); 
          -> 7
  这函数是多字节可靠的。
  INSTR(str,substr) 
  返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同,除了参数被颠倒。 
  mysql> select INSTR(‘foobarbar’, ‘bar’); 
          -> 4 
  mysql> select INSTR(‘xbar’, ‘foobar’); 
          -> 0
  这函数是多字节可靠的。
  LPAD(str,len,padstr) 
  返回字符串str,左面用字符串padstr填补直到str是len个字符长。 
  mysql> select LPAD(‘hi’,4,’??’); 
          -> ‘??hi’
  RPAD(str,len,padstr) 
  返回字符串str,右面用字符串padstr填补直到str是len个字符长。   
  mysql> select RPAD(‘hi’,5,’?’); 
          -> ‘hi???’
  LEFT(str,len) 
  返回字符串str的最左面len个字符。 
  mysql> select LEFT(‘foobarbar’, 5); 
          -> ‘fooba’
  该函数是多字节可靠的。
  RIGHT(str,len) 
  返回字符串str的最右面len个字符。 
  mysql> select RIGHT(‘foobarbar’, 4); 
          -> ‘rbar’
  该函数是多字节可靠的。
  SUBSTRING(str,pos,len) 
    
  SUBSTRING(str FROM pos FOR len) 
    
  MID(str,pos,len) 
  从字符串str返回一个len个字符的子串,从位置pos开始。使用FROM的变种形式是ANSI SQL92语法。 
  mysql> select SUBSTRING(‘Quadratically’,5,6); 
          -> ‘ratica’
  该函数是多字节可靠的。
  SUBSTRING(str,pos) 
    
  SUBSTRING(str FROM pos) 
  从字符串str的起始位置pos返回一个子串。 
  mysql> select SUBSTRING(‘Quadratically’,5); 
          -> ‘ratically’ 
  mysql> select SUBSTRING(‘foobarbar’ FROM 4); 
          -> ‘barbar’
  该函数是多字节可靠的。
  SUBSTRING_INDEX(str,delim,count) 
  返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数,返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数,返回最后的分隔符到右边的所有字符(从右边数)。 
  mysql> select SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2); 
          -> ‘www.mysql’ 
  mysql> select SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2); 
          -> ‘mysql.com’
  该函数对多字节是可靠的。
  LTRIM(str) 
  返回删除了其前置空格字符的字符串str。 
  mysql> select LTRIM(‘  barbar’); 
          -> ‘barbar’
  RTRIM(str) 
  返回删除了其拖后空格字符的字符串str。 
  mysql> select RTRIM(‘barbar   ‘); 
          -> ‘barbar’
  该函数对多字节是可靠的。   
  TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) 
  返回字符串str,其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出,BOTH被假定。如果remstr没被指定,空格被删除。 
  mysql> select TRIM(‘  bar   ‘); 
          -> ‘bar’ 
  mysql> select TRIM(LEADING ‘x’ FROM ‘xxxbarxxx’); 
          -> ‘barxxx’ 
  mysql> select TRIM(BOTH ‘x’ FROM ‘xxxbarxxx’); 
          -> ‘bar’ 
  mysql> select TRIM(TRAILING ‘xyz’ FROM ‘barxxyz’); 
          -> ‘barx’
  该函数对多字节是可靠的。
  SOUNDEX(str) 
  返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符,但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的 同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。 
  mysql> select SOUNDEX(‘Hello’); 
          -> ‘H400’ 
  mysql> select SOUNDEX(‘Quadratically’); 
          -> ‘Q36324’
  SPACE(N) 
  返回由N个空格字符组成的一个字符串。 
  mysql> select SPACE(6); 
          -> ‘      ‘
  REPLACE(str,from_str,to_str) 
  返回字符串str,其字符串from_str的所有出现由字符串to_str代替。 
  mysql> select REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’); 
          -> ‘WwWwWw.mysql.com’
  该函数对多字节是可靠的。
  REPEAT(str,count) 
  返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果str或count是NULL,返回NULL。 
  mysql> select REPEAT(‘MySQL’, 3); 
          -> ‘MySQLMySQLMySQL’
  REVERSE(str) 
  返回颠倒字符顺序的字符串str。 
  mysql> select REVERSE(‘abc’); 
          -> ‘cba’
  该函数对多字节可靠的。
  INSERT(str,pos,len,newstr) 
  返回字符串str,在位置pos起始的子串且len个字符长得子串由字符串newstr代替。 
  mysql> select INSERT(‘Quadratic’, 3, 4, ‘What’); 
          -> ‘QuWhattic’
  该函数对多字节是可靠的。
  ELT(N,str1,str2,str3,…) 
  如果N= 1,返回str1,如果N= 2,返回str2,等等。如果N小于1或大于参数个数,返回NULL。ELT()是FIELD()反运算。 
  mysql> select ELT(1, ‘ej’, ‘Heja’, ‘hej’, ‘foo’); 
          -> ‘ej’ 
  mysql> select ELT(4, ‘ej’, ‘Heja’, ‘hej’, ‘foo’); 
          -> ‘foo’
  FIELD(str,str1,str2,str3,…) 
  返回str在str1, str2, str3, …清单的索引。如果str没找到,返回0。FIELD()是ELT()反运算。
  mysql> select FIELD(‘ej’, ‘Hej’, ‘ej’, ‘Heja’, ‘hej’, ‘foo’); 
          -> 2 
  mysql> select FIELD(‘fo’, ‘Hej’, ‘ej’, ‘Heja’, ‘hej’, ‘foo’); 
          -> 0
  FIND_IN_SET(str,strlist) 
  如果字符串str在由N子串组成的表strlist之中,返回一个1到N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列,FIND_IN_SET()函数被优化而使用位运算!如果str不是在strlist里面或如果strlist是空字符串,返回0。如果任何一个参数是NULL,返回NULL。如果第一个参数包含一个“,”,该函数将工作不正常。 
  mysql> SELECT FIND_IN_SET(‘b’,’a,b,c,d’); 
          -> 2
  MAKE_SET(bits,str1,str2,…) 
  返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串),由相应的位在bits集合中的的字符串组成。str1对应于位0,str2对应位1,等等。在str1, str2, …中的NULL串不添加到结果中。 
  mysql> SELECT MAKE_SET(1,’a’,’b’,’c’); 
          -> ‘a’ 
  mysql> SELECT MAKE_SET(1 | 4,’hello’,’nice’,’world’); 
          -> ‘hello,world’ 
  mysql> SELECT MAKE_SET(0,’a’,’b’,’c’); 
          -> ”
  EXPORT_SET(bits,on,off,[separator,[number_of_bits]]) 
  返回一个字符串,在这里对于在“bits”中设定每一位,你得到一个“on”字符串,并且对于每个复位(reset)的位,你得到一个“off”字符串。每个字符串用“separator”分隔(缺省“,”),并且只有“bits”的“number_of_bits” (缺省64)位被使用。 
  mysql> select EXPORT_SET(5,’Y’,’N’,’,’,4) 
          -> Y,N,Y,N
  LCASE(str) 
    
  LOWER(str) 
  返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。 
  mysql> select LCASE(‘QUADRATICALLY’); 
          -> ‘quadratically’
  UCASE(str) 
    
  UPPER(str) 
  返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的。 
  mysql> select UCASE(‘Hej’); 
          -> ‘HEJ’
  该函数对多字节是可靠的。
  LOAD_FILE(file_name) 
  读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出,函数返回NULL。 
  mysql> UPDATE table_name 
             SET blob_column=LOAD_FILE(“/tmp/picture”) 
             WHERE id=1;
  MySQL必要时自动变换数字为字符串,并且反过来也如此:
  mysql> SELECT 1+”1″; 
          -> 2 
  mysql> SELECT CONCAT(2,’ test’); 
          -> ‘2 test’
  如果你想要明确地变换一个数字到一个字符串,把它作为参数传递到CONCAT()。
  如果字符串函数提供一个二进制字符串作为参数,结果字符串也是一个二进制字符串。被变换到一个字符串的数字被当作是一个二进制字符串。这仅影响比较。
分类: 未分类 标签: , , ,