Mysql

當模式名稱中有連字元（“-”）時，MySQL 5.5.9 查詢記憶體不起作用

  • March 13, 2011

我正在執行從 mysql.com 下載的 MySQL 5.5.9 x86_64 RPM。在 CentOS 5.5 Xen DomU 上執行。

我已經啟用了 Query_cache 但是 MySQL 從不使用它。我所有的表都是 InnoDB。為什麼 Qcache 從未命中？

更新 2：我發現這僅限於名稱中帶有 - 的模式。創建新模式，例如 new-db，查詢記憶體失敗。不幸的是，我有 148 個現有的 Schama，它們的名稱中都帶有“-”。

更新這似乎僅限於從以前版本的 MySQL (5.0.32) 轉儲和導入的模式創建新模式並在此查詢記憶體中查詢表按預期工作。

這是我的 Qc 工作和不工作的設置和範例。

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)


mysql> USE `existing-schema`;
Database changed

mysql> CREATE TABLE test (
   ->  `uid` INT AUTO_INCREMENT PRIMARY KEY,
   ->  `str` VARCHAR(255) NOT NULL
       -> ) ENGINE=InnoDB;
   Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `str` varchar(255) NOT NULL,
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 536852824 |
| Qcache_hits             | 0         |
| Qcache_inserts          | 0         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 56725     |
| Qcache_queries_in_cache | 0         |
| Qcache_total_blocks     | 1         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test;
+-----+-------+
| uid | str   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
|   4 | four  |
+-----+-------+
4 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 536852824 |
| Qcache_hits             | 0         |
| Qcache_inserts          | 0         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 89824     |
| Qcache_queries_in_cache | 0         |
| Qcache_total_blocks     | 1         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> CREATE DATABASE new;
Query OK, 1 row affected (0.00 sec)

mysql> USE new;
Database changed
mysql> CREATE TABLE test (
   ->  `uid` INT AUTO_INCREMENT PRIMARY KEY,
   ->  `str` VARCHAR(255) NOT NULL
   -> ) ENGINE=InnoDB;
   Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `str` varchar(255) NOT NULL,
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


   mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four');
   Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 536852824 |
| Qcache_hits             | 0         |
| Qcache_inserts          | 0         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 89824     |
| Qcache_queries_in_cache | 0         |
| Qcache_total_blocks     | 1         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test;
+-----+-------+
| uid | str   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
|   4 | four  |
+-----+-------+
4 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 536851288 |
| Qcache_hits             | 0         |
| Qcache_inserts          | 1         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 109528    |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 4         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test;
+-----+-------+
| uid | str   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
|   4 | four  |
+-----+-------+
4 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 536851288 |
| Qcache_hits             | 1         |
| Qcache_inserts          | 1         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 126100    |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 4         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> SHOW CREATE DATABASE new;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| new      | CREATE DATABASE `new` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE DATABASE `existing-schema`;
+------------------+---------------------------------------------------------------------------+
| Database         | Create Database                                                           |
+------------------+---------------------------------------------------------------------------+
| ezlead-live-data | CREATE DATABASE `existing-schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[REMOVED]' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

這是 MySQL 中的一個錯誤。我想目前唯一的解決方案是重命名我的數據庫模式，以便它們不包含連字元。

您的查詢記憶體設置看起來不錯。有關不會記憶體哪些類型的 SELECT 查詢的詳細資訊，請參閱**查詢記憶體如何操作，並查看是否有任何適用於您的情況。**還要檢查 MySQL 錯誤日誌以獲取任何相關消息。

您還可以做的是發出一個非常簡單的 SELECT 查詢，您知道該查詢應該被記憶體，Qcache_hits並在查詢之前和之後檢查。嘗試創建一個小型測試數據庫/表以排除現有表的任何可能問題。如果這不起作用，您就知道 MySQL 有一些更微妙的問題。

引用自：https://serverfault.com/questions/246735

相關問答

Mysql

是否啟用 MySQL query_cache_size Innodb Lock Safe？

  • June 13, 2014
檢視問答
Mysql

MySQL 無法啟動一世nnoDBRepair一世nn這D乙R和p一種一世rInnoDB Repair

  • January 26, 2022
檢視問答
Mysql

在 MySQL/InnoDB 中為事務設置時間限制

  • November 10, 2021
檢視問答
Ubuntu

從 *.sql 導入 mysql… IBD 文件失去

  • October 5, 2021
檢視問答
Mysql

專用伺服器上大型 Magento 站點的最佳 MySQL my.cnf 設置

  • January 18, 2021
檢視問答
Mysql

innodb ALTER TABLE 需要多少磁碟空間

  • December 4, 2020
檢視問答