はてなブックマーク

If I go to mysql shell and type SELECT * FROM users I get - +--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+ | USERID | NAME | EMAILID | PASSWORD | USER_TYPE | CONTACT_ID_FKUSERS | COMPANY_ID_FKUSERS | +--------+----------------+---------------------------------+----------+-----------+--------------------+---------------

As described here in the MySQL docs: The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. Before MySQL 5.7.3, this column is displayed if you use EXPLAIN EXTENDED. As of MySQL 5.7.3, exten

ibdata1 The file ibdata1 is the system tablespace for the InnoDB infrastructure. It contains several classes for information vital for InnoDB Table Data Pages Table Index Pages Data Dictionary MVCC Control Data Undo Space Rollback Segments Double Write Buffer (Pages Written in the Background to avoid OS caching) Insert Buffer (Changes to Secondary Indexes) Click Here to see a Pictorial Representat

Stack Exchange Network Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Visit Stack Exchange

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, UPDATE and DELETE queries remaining in "Query end" state. This obviously is most unfortunate. The MySQL slow query log is logging even the most trivial queries with insane query times, hundreds of them wit

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` WHERE user_id = 9898 AND status IN (1,3,4) order by id limit 30; Here is the result of explain, as you can see the query is filesort id select_type table type possible_keys key key_len ref rows Extra 1

The Buffer pool size 393215 This is in pages not bytes. To see the Buffer Pool size in GB run this: SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM (SELECT variable_value BufferPoolPages FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_n

I have multiple PostgreSQL servers for a web application. Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication). I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432) connecting to database on localhost. I use transaction pool mode. In order to load-balance my read-only connections on slaves, I use HAProxy (v1.5) wi

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this query. create table ( start_date_time timestamp, end_date_time timestamp, id_phi integer, primary key(start_date_time, end_date_time,id_phi); This table contains

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production. So I would just like to make all collations which are utf8_bin to utf8mb4_bin. I believe there is no performance loss/gain with this change other than a bit of storage overhead.

Stack Exchange Network Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Visit Stack Exchange

I am importing a 7 GB foobar.sql to restore a table in a local database. $ mysql -h localhost -u root 'my_data' < foobar.sql $ mysql --version /usr/local/mysql/bin/mysql Ver 14.12 Distrib 5.0.96, for apple-darwin9.8.0 (i386) using readline 5.1 How can I monitor its progress?

Because of the following warning in mysqld.log: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. I want to switch the replication format to MIXED. But according to the MySQL document: Switching the replication forma