• ↓
  • ↑
  • ⇑
 
Записи с темой: mysql (список заголовков)
00:50 

MySQL concurrency with GET_LOCK

Vi Veri Veniversum Vivus Vici
I trying to use GET_LOCK for application-level blocking in my application. So I research how to do it in propert way. As I can see, IS_FREE_LOCK function is non-atomic, but GET_LOCK with second parameter (timeout) equal to 0 is works very good. So we should to use two checking steps: IS_FREE_LOCK as first and then GET_LOCK($lockId, 0) === 1.

First, we need to create code like this (its not ideal, and hasnt error handling: its a just proof-o-concept):

  1. <?php
  2. $db = new mysqli('localhost', 'root', '');
  3. if ($db->connect_errno) {
  4. printf("Не удалось подключиться: %s\n", $db->connect_error);
  5. exit();
  6. }
  7. function get_named_lock($lockname, $i) {
  8. global $db;
  9. $rs = $db->query("SELECT IS_FREE_LOCK('$lockname';) AS isfree");
  10. $result = $rs->fetch_array();
  11. if ($result['isfree']) {
  12. printf("Is free! PID = %s I = %d\n", getmypid(), $i);
  13. $rs = $db->query("SELECT GET_LOCK('$lockname', 0) AS locked");
  14. $result = $rs->fetch_array();
  15. $locked = $result['locked'];
  16. printf("%s Locked! PID = %s I = %d\n", $locked ? 'IS' : 'NOT', getmypid(), $i);
  17. return $locked;
  18. } else {
  19. return false;
  20. }
  21. }
  22. function release_named_lock($lockname) {
  23. $db->query("DO RELEASE_LOCK('$lockname';)");
  24. }
  25. printf("Started %s\n", getmypid());
  26. for ($i = 0; $i < 10000; $i++) {
  27. get_named_lock('mylock', $i);
  28. usleep(10);
  29. }
  30. printf("Release %s\n", getmypid());


Lets save it in file lock_test.php and then write small Bash sсript (run.sh):

  1. #!/bin/bash
  2. for i in {1..10}
  3. do
  4. `php lock_test.php >> /tmp/out.log` &
  5. done


Then you can to run it and see into logs:

$ ./run.sh
$ tail -f /tmp/out.log
Started 29376
Is free! PID = 29376 I = 0
IS Locked! PID = 29376 I = 0
Started 29369
Started 29377
Started 29368
Started 29370
Started 29373
Started 29374
Started 29372
Started 29378
Started 29379
Release 29373
Release 29370
Release 29376
Is free! PID = 29378 I = 9844
Is free! PID = 29369 I = 9964
Is free! PID = 29374 I = 9989
IS Locked! PID = 29369 I = 9964
Is free! PID = 29377 I = 9852
NOT Locked! PID = 29374 I = 9989
NOT Locked! PID = 29378 I = 9844
NOT Locked! PID = 29377 I = 9852
Release 29374
Release 29369
Release 29372
Is free! PID = 29377 I = 9876
Is free! PID = 29368 I = 9881
Is free! PID = 29379 I = 9906
Is free! PID = 29378 I = 9876
IS Locked! PID = 29379 I = 9906
NOT Locked! PID = 29378 I = 9876
NOT Locked! PID = 29368 I = 9881
NOT Locked! PID = 29377 I = 9876
Release 29379
Is free! PID = 29378 I = 9973
IS Locked! PID = 29378 I = 9973
Release 29368
Release 29378
Release 29377



As you can see, lot of sсripts can to catch lock as "free" at same time, but only one can actually catch it with GET_LOCK. Anyway, a lot of iterations are skipped by IS_FREE_LOCK check step - its fast and enough to skip non-concurrency scripts.

@темы: concurrency, PHP, MySQL, GET_LOCK, Bash, mutex

00:03 

Difference between same table groups

Vi Veri Veniversum Vivus Vici
For example, we have some table for categories:

  1. CREATE TABLE `p1` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `title` varchar(20) DEFAULT NULL,
  4. PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
We have some categories:

  1. mysql> select * from p1;
  2. +----+-------+
  3. | id | title |
  4. +----+-------+
  5. | 1 | cat1 |
  6. | 2 | cat2 |
  7. | 3 | cat3 |
  8. | 4 | cat4 |
  9. +----+-------+
  10. 4 rows in set (0.00 sec)
Then, we have table for properties:

  1. CREATE TABLE `p2` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `title` varchar(20) DEFAULT NULL,
  4. `cat_id` int(11) NOT NULL,
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

There are list of properties:

  1. mysql> select * from p2;
  2. +----+--------+--------+
  3. | id | title | cat_id |
  4. +----+--------+--------+
  5. | 1 | prop11 | 1 |
  6. | 2 | prop12 | 1 |
  7. | 3 | prop21 | 2 |
  8. | 4 | prop22 | 2 |
  9. | 5 | prop23 | 2 |
  10. | 6 | prop31 | 3 |
  11. | 7 | prop32 | 3 |
  12. | 8 | prop33 | 3 |
  13. | 9 | prop41 | 4 |
  14. +----+--------+--------+
  15. 9 rows in set (0.00 sec)

Next, we need to have list of properties values, which may be contained in "groups":

  1. CREATE TABLE `p3` (
  2. `gid` int(11) NOT NULL,
  3. `prop_id` int(11) NOT NULL,
  4. `value` varchar(20) DEFAULT NULL,
  5. PRIMARY KEY (`gid`,`prop_id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

with the following content:

  1. mysql> select * from p3;
  2. +-----+---------+------------+
  3. | gid | prop_id | value |
  4. +-----+---------+------------+
  5. | 1 | 1 | prop 1 |
  6. | 1 | 2 | prop 2 |
  7. | 1 | 4 | diff value |
  8. | 1 | 5 | same value |
  9. | 1 | 6 | prop 6 |
  10. | 2 | 1 | prop 1 |
  11. | 2 | 2 | prop 2 |
  12. | 2 | 3 | prop 3 |
  13. | 2 | 4 | prop 4 |
  14. | 2 | 5 | same value |
  15. | 2 | 7 | prop 7 |
  16. +-----+---------+------------+
  17. 11 rows in set (0.00 sec)

For example, I want to know, which values of second group are missed or have different value in the first group. And properties in this dataset must be included in categories 1 and 2.


  1. mysql> select *,
  2. (select b.prop_id
  3. from p3 as b
  4. where gid = 1
  5. and a.prop_id = b.prop_id
  6. ) as is_exists
  7. from p3 as a
  8. join p2
  9. on p2.id = a.prop_id
  10. where gid = 2
  11. and cat_id in (1, 2)
  12. and (prop_id, value) not in
  13. (select prop_id, value
  14. from p3
  15. where gid = 1
  16. and prop_id = a.prop_id
  17. );
  18. +-----+---------+--------+----+--------+--------+-----------+
  19. | gid | prop_id | value | id | title | cat_id | is_exists |
  20. +-----+---------+--------+----+--------+--------+-----------+
  21. | 2 | 3 | prop 3 | 3 | prop21 | 2 | NULL |
  22. | 2 | 4 | prop 4 | 4 | prop22 | 2 | 4 |
  23. +-----+---------+--------+----+--------+--------+-----------+
  24. 2 rows in set (0.00 sec)

@темы: MySQL

14:31 

my.cnf for developing

Vi Veri Veniversum Vivus Vici
  1. #
  2. # The MySQL database server configuration file.
  3. #
  4. # You can copy this to one of:
  5. # - "/etc/mysql/my.cnf" to set global options,
  6. # - "~/.my.cnf" to set user-specific options.
  7. #
  8. # One can use all long options that the program supports.
  9. # Run program with --help to get a list of available options and with
  10. # --print-defaults to see which it would actually understand and use.
  11. #
  12. # For explanations see
  13.  
  14. # This will be passed to all mysql clients
  15. # It has been reported that passwords should be enclosed with ticks/quotes
  16. # escpecially if they contain "#" chars...
  17. # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
  18. [client]
  19. port = 3306
  20. socket = /var/run/mysqld/mysqld.sock
  21.  
  22. # Here is entries for some specific programs
  23. # The following values assume you have at least 32M ram
  24.  
  25. # This was formally known as [safe_mysqld]. Both versions are currently parsed.
  26. [mysqld_safe]
  27. socket = /var/run/mysqld/mysqld.sock
  28. nice = 0
  29.  
  30. [mysqld]
  31. #
  32. # * Basic Settings
  33. #
  34. user = mysql
  35. pid-file = /var/run/mysqld/mysqld.pid
  36. socket = /var/run/mysqld/mysqld.sock
  37. port = 3306
  38. basedir = /usr
  39. datadir = /home/mysql
  40. tmpdir = /tmp
  41. lc-messages-dir = /usr/share/mysql
  42. skip-external-locking
  43. #
  44. # Instead of skip-networking the default is now to listen only on
  45. # localhost which is more compatible and is not less secure.
  46. bind-address = 127.0.0.1
  47. #
  48. # * Fine Tuning
  49. #
  50. key_buffer = 32M
  51. max_allowed_packet = 32M
  52. thread_stack = 192K
  53. thread_cache_size = 16
  54. # This replaces the startup sсript and checks MyISAM tables if needed
  55. # the first time they are touched
  56. myisam-recover = BACKUP
  57. max_connections = 64
  58. table_cache = 1000
  59. #thread_concurrency = 10
  60. #
  61. # * Query Cache Configuration
  62. #
  63. query_cache_limit = 16M
  64. query_cache_size = 64M
  65. #
  66. # * Logging and Replication
  67. #
  68. # Both location gets rotated by the cronjob.
  69. # Be aware that this log type is a performance killer.
  70. # As of 5.1 you can enable the log at runtime!
  71. #general_log_file = /var/log/mysql/mysql.log
  72. #general_log = 1
  73. #
  74. # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
  75. #
  76. # Here you can see queries with especially long duration
  77. log_slow_queries = /var/log/mysql/mysql-slow.log
  78. long_query_time = 0
  79. slow_query_log = 1
  80. #log-queries-not-using-indexes
  81. #
  82. # The following can be used as easy to replay backup logs or for replication.
  83. # note: if you are setting up a replication slave, see README.Debian about
  84. # other settings you may need to change.
  85. #server-id = 1
  86. #log_bin = /var/log/mysql/mysql-bin.log
  87. expire_logs_days = 10
  88. max_binlog_size = 100M
  89. #binlog_do_db = include_database_name
  90. #binlog_ignore_db = include_database_name
  91. #
  92. # * InnoDB
  93. #
  94. # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
  95. # Read the manual for more InnoDB related options. There are many!
  96. #
  97. # * Security Features
  98. #
  99. # Read the manual, too, if you want chroot!
  100. # chroot = /var/lib/mysql/
  101. #
  102. # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
  103. #
  104. # ssl-ca=/etc/mysql/cacert.pem
  105. # ssl-cert=/etc/mysql/server-cert.pem
  106. # ssl-key=/etc/mysql/server-key.pem
  107.  
  108.  
  109. innodb_buffer_pool_size = 256M
  110. innodb_additional_mem_pool_size = 16M
  111. innodb_log_file_size = 128M
  112. innodb_log_buffer_size = 8M
  113. innodb_flush_log_at_trx_commit = 2
  114. innodb_thread_concurrency = 8
  115. innodb_flush_method = O_DIRECT
  116.  
  117. innodb_checksums = 0
  118. innodb_file_format_check = 0
  119. innodb_stats_on_metadata=0
  120.  
  121. [mysqldump]
  122. quick
  123. quote-names
  124. max_allowed_packet = 16M
  125.  
  126. [mysql]
  127. #no-auto-rehash # faster start of mysql but no tab completition
  128.  
  129. [isamchk]
  130. key_buffer = 16M
  131.  
  132. #
  133. # * IMPORTANT: Additional settings that can override those from this file!
  134. # The files must end with '.cnf', otherwise they'll be ignored.
  135. #
  136. !includedir /etc/mysql/conf.d/
  137.  

@темы: MySQL

16:59 

MySQL settings for big dumps

Vi Veri Veniversum Vivus Vici
  1. innodb_buffer_pool_size = 2048M
  2. innodb_additional_mem_pool_size = 64M
  3. innodb_flush_log_at_trx_commit = 0
  4. innodb_thread_concurrency = 8
  5. innodb_flush_method = O_DIRECT
  6. innodb_checksums = 0
  7. innodb_file_format_check = 0
  8. innodb_stats_on_metadata=0

@темы: MySQL

14:31 

Изменение AUTO_INCREMENT колонки в MySQL на DEFAULT NULL

Vi Veri Veniversum Vivus Vici
mysql> show create table test;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'hahaha',
`name` varchar(255) NOT NULL COMMENT 'Название',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table test change column id id int default null;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL COMMENT 'Название',
PRIMARY KEY (`id1c`),
UNIQUE KEY `uk__name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


Таким образом, нужно вначале менять PK.

@темы: MySQL

11:27 

Добавил в mysqldiff работу с вшнешними ключами

Vi Veri Veniversum Vivus Vici
Форк на гитхабе: github.com/GuyFawkes/mysqldiff

@темы: MySQL

11:11 

Best practices миграций в MySQL

Vi Veri Veniversum Vivus Vici
12:22 

Как посмотреть таблицы, связанные с данной, в MySQL?

Vi Veri Veniversum Vivus Vici
Нечто вроде такого запроса:

SELECT table_name, column_name, referenced_table_name, referenced_column_name, constraint_name FROM `information_schema`.`KEY_COLUMN_USAGE`
where table_schema='schematest' and (referenced_table_name = 'sotm_product' or table_name = 'sotm_product') and referenced_table_name is not null;

Выполняются такие запросы очень медленно:


mysql> explain SELECT table_name, column_name, referenced_table_name, referenced_column_name, constraint_name FROM `information_schema`.`KEY_COLUMN_USAGE`
where table_schema='schematest' and (referenced_table_name = 'sotm_product' or table_name = 'sotm_product') and referenced_table_name is not null;
+----+-------------+------------------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
| 1 | SIMPLE | KEY_COLUMN_USAGE | ALL | NULL | TABLE_SCHEMA | NULL | NULL | NULL | Using where; Open_full_table; Scanned 1 database |
+----+-------------+------------------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
1 row in set (0.04 sec)

@темы: MySQL

12:30 

Отключить проверку внешних ключей в MySQL

Vi Veri Veniversum Vivus Vici
SET foreign_key_checks = 0;

@темы: MySQL

12:42 

MySQL - Can't create table (errno: 121)

Vi Veri Veniversum Vivus Vici
Возникла такая ошибка, когда создаете таблицу? Смотрим:


Как же так? Ответ в этом посте: thenoyes.com/littlenoise/?p=81

Если вкратце, то имя связи по вторичному ключу, которое вы задавали ручками, уже существует.

@темы: MySQL

Small Coder Blog

главная