Slow innodb replication
Mysqlians, Very Simple, Slave A is all MyISAM tables and replicates ~15writes/sec for ~200k/s with a top load of 0.00. I change 1 table(100mb data 1.5writes/sec) to innodb and the top load changes to .3 . I have set innodb_trx_commit=0 and set noatime on the filesystem ( ext3 redhat linux 9.0 x86 xeon 2.4). I understand innodb writes more data but something is amiss. The load pattern drops slowly and the jumps every 30 seconds or so. So it seems something is getting flushed to disk but I can not accept it is this much worse then MyISAM. Once I replicate the entire database my load is 0.5 add all I am doing is replication. Any advice/suggestions/stabs in the dark is much appreciated. Trevor
Loading the .myd into memory
Mysqlians, Greetings. Besides the query cache is their a buffer which holds the data portion of MyISAM tables. All the buffers seem to hold key information or query processing information. Thanks, Trevor
Table lock statistics by TABLE not just server,db
Mysqlians, I am trying to determine in some of my database tables should be converted from MyISAM to Innodb. I have read that a collusion on 10-20% is the threshold at which one should convert( current # around .3%). However the table_locks_waited and table_locks_immediate counters only give information on a server level. I want to examine this data per table. Does anyone know how to do this? Many Thanks, Trevor
Mysql on Solaris 8/9 with ultrasparc
Mysqlians, Greetings .. I am at my wits end trying to find a performance problem with 4.0.16 on solaris 9 with 4 ultrasparc III process (sunfire v880). I would be much obliged if the Mame's and Sir's out their with this setup(or similar) would share with me their show variables or any kernel or filesystem changes they made. In my particular, case single-threaded tests (sql-bench) compare similar for the sunfire against dual proc 2.4 xeon systems (redhat 9) but under site load the sunfire slows to a crawl. Happy Holidays, Trevor
Cardinality Bug ?
Hello, I was able to duplicate the following sequence in both 4.0.14-max and 4.0.16. It happens in all my tables. I do not understand why the cardinality for the date_created field does not = 223284. mysql select count(distinct date_created) from POSTING ; +--+ | count(distinct date_created) | +--+ | 223284 | +--+ 1 row in set (2.48 sec) mysql analyze table POSTING ; +---+-+--+--+ | Table | Op | Msg_type | Msg_text | +---+-+--+--+ | TRIBE.POSTING | analyze | status | OK | +---+-+--+--+ 1 row in set (16.74 sec) mysql show index from POSTING ; +-+++--+ +---+-+--++--++- + | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-+++--+ +---+-+--++--++- + | POSTING | 0 | PRIMARY|1 | ID | A | 228383 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_id_idx |1 | ID | A | 228383 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_owner_id_idx |1 | OWNER_ID | A |6010 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_parent_id_idx |1 | PARENT_ID | A | 114191 | NULL | NULL | YES | BTREE | | | POSTING | 1 | posting_thread_id_idx |1 | THREAD_ID | A | 45676 | NULL | NULL | YES | BTREE | | | POSTING | 1 | posting_from_person_id_idx |1 | FROM_PERSON_ID | A |9929 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_date_created |1 | DATE_CREATED | A | 228383 | NULL | NULL | YES | BTREE | | | POSTING | 1 | posting_is_deleted_idx |1 | IS_DELETED | A | 1 | NULL | NULL | | BTREE | | +-+++--+ +---+-+--++--++- + 8 rows in set (0.00 sec) mysql select count(distinct date_created) from POSTING ; +--+ | count(distinct date_created) | +--+ | 223284 | Thanks, Trevor
2 Fulltext index's are better than 1? No
Hello,
I recently ugraded to 4-14-Max from 4.13-Max to take advantage
of the optimizer fulltext bug fix. However a certain query using the
fulltext indexies is MUCH SLOWER then using no idex. Here is the query
select distinct p.USER_CREATED, p.ID, p.DATE_CREATED, p.LAST_CLICK, p.ZIP
from PERSON p
join INTEREST i on p.ID=i.PERSON_ID
join INTEREST i0 on p.ID=i0.PERSON_ID
join INTEREST_TYPE it0 on i0.INTEREST_ID=it0.ID
where p.FIRST_NAME!='Unsubscribed' and match(i.COMMENT) against('+games ' in
boolean mode) and (it0.NAME='occupation' and match(i0.COMMENT)
against('+software ' in boolean mode));
the explain on 4.0.13-Max
+---++--
---+---+-+-++---
---+
| table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+---++--
---+---+-+-++---
---+
| it0 | ALL| PRIMARY
| NULL |NULL | NULL| 30 | Using where; Using temporary
|
| i | ALL|
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx | NULL |
NULL | NULL| 550709 | Using where |
| p | eq_ref | PRIMARY,person_id_base_idx
| PRIMARY | 40 | i.PERSON_ID | 1 | Using where
|
| i0| eq_ref |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx | PERSON_ID |
80 | p.ID,it0.ID | 1 | Using where; Distinct|
Query time: 15 seconds
Notice that it does not use the fulltext index (COMMENT) in the interest
tables I,i0
The explain on 4-14-Max
+---+--+
-+-+-++--+--
+
| table | type | possible_keys
| key | key_len | ref| rows | Extra|
+---+--+
-+-+-++--+--
+
| i | fulltext |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx,COMMENT |
COMMENT | 0 ||1 | Using where; Using temporary |
| p | eq_ref | PRIMARY,person_id_base_idx
| PRIMARY | 40 | i.PERSON_ID|1 | Using where |
| i0| fulltext |
PERSON_ID,interest_person_id_idx,interest_person_id_two_idx,COMMENT |
COMMENT | 0 ||1 | Using where; Distinct|
| it0 | eq_ref | PRIMARY
| PRIMARY | 40 | i0.INTEREST_ID |1 | Using where; Distinct|
Query time : 146 seconds, ( slow querylog lists rows examined as 3.6 million
)
Notice the different join order and use of the fulltext indexies
With a single join of the INTEREST table the index is used correctly and the
query is quick. If anyone has any idea why USING a fulltext index is slower
please share your knowledge. Below is the show create table output.
Thanks,
Trevor
| INTEREST | CREATE TABLE `INTEREST` (
`ID` varchar(40) NOT NULL default '',
`PERSON_ID` varchar(40) NOT NULL default '',
`COMMENT` mediumtext,
`DATE_CREATED` timestamp(14) NOT NULL,
`INTEREST_ID` varchar(40) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `PERSON_ID` (`PERSON_ID`,`INTEREST_ID`),
KEY `interest_person_id_idx` (`PERSON_ID`),
KEY `interest_person_id_two_idx` (`PERSON_ID`,`COMMENT`(200)),
FULLTEXT KEY `COMMENT` (`COMMENT`)
) TYPE=MyISAM |
| INTEREST_TYPE | CREATE TABLE `INTEREST_TYPE` (
`ID` varchar(40) NOT NULL default '',
`NAME` varchar(100) default NULL,
`NICE_NAME` varchar(100) default NULL,
`TYPE` varchar(20) NOT NULL default '',
`DESCRIPTION` mediumtext,
`DATE_CREATED` timestamp(14) NOT NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM |
| PERSON |CREATE TABLE `PERSON` (
`ID` char(40) NOT NULL default '',
`PHOTO_ID` char(40) default NULL,
`EMAIL` char(100) NOT NULL default '',
`LAST_CLICK` timestamp(14) NOT NULL,
`FIRST_NAME` char(40) default NULL,
`LAST_NAME` char(40) default NULL,
`ZIP` char(15) NOT NULL default '',
`COUNTRY` char(40) default NULL,
`BIRTHDAY` datetime default NULL,
`GENDER` char(1) default NULL,
`STATUS` char(1) default NULL,
`SEND_EMAIL` tinyint(1) default NULL,
`IS_DELETED` char(1) NOT NULL default '',
`USER_CREATED` int(11) NOT NULL default '0',
`DATE_CREATED` timestamp(14) NOT NULL,
`USER_MODIFIED` int(11) default NULL,
`DATE_MODIFIED` timestamp(14) NOT NULL,
`HIDE_AGE` tinyint(1) default NULL,
`HIDE_GENDER` tinyint(1) default NULL,
`HIDE_LOCATION` tinyint(1) default NULL,
`HIDE_ONLINE` tinyint(1) default NULL,
`GENERATION` int(11) default '0',
`ALLOW_EMAIL_DEGREE` int(11) default '0',
`HAS_FILTER` tinyint(1) default '0',
Ugrade from 4.01.3-max to 4.0.16, now slower
Hello, I recently upgraded from 4.0.13-max to 4.0.16 and now the same queries/sec cause a higher load on the database server. I upgraded to 4.01.6 since the optimizer was not using some of my fulltext indicies. All our tables are MyIsam so I figure there is no reason to use the MAX version. In addition my Redhat 9.0 machine now lists all the mysld threads under top, whereas before top listied a single myslqd process. Any information is appreciated. Thanks, Trevor
Disk io wait during select,
Greetings.. Is their a way to list a time breakdown of a select? Amount of query waiting on disk-io, in memory, etc... I have tried the debug-info option with mysql client but it lists several memory settings. I have a 1.2 ultra-sparcIII that is twice as slow as a xeon 2.4 machine eventhough the sparc has superior memory and disks. Thanks, Trevor
MySQL on Solaris 9 sparc with Perl
Greetings, I can not get the DBD::mysql module for perl to install correctly. It continues to complain about the mysql.so file. What worries me is the solaris dist does not include a libmysqlclient.so file which I do believe the linux DBD:mysql needs to install. Any help is most welcome. Trevor
Fulltext index not being used
Fellow Mysqlians,
Can anyone tell me why mysql refuses to use the fulltext index
in the second query?
Query 1;
mysql explain select straight_join distinct i.ID from INTEREST i use index
(COMMENT)
inner join PERSON p on p.ID=i.PERSON_ID where match(i.COMMENT)
against('+todo ' in boolean mode);
+---+--++-+-+---
--+--+--+
| table | type | possible_keys | key | key_len | ref
| rows | Extra|
+---+--++-+-+---
--+--+--+
| i | fulltext | COMMENT| COMMENT | 0 |
|1 | Using where; Using temporary |
| p | eq_ref | PRIMARY,person_id_base_idx | PRIMARY | 40 |
i.PERSON_ID |1 | Using index; Distinct|
+---+--++-+-+---
--+--+--+
2 rows in set (0.00 sec)
Query2
mysql explain select straight_join distinct i.ID from INTEREST i use index
(COMMENT) inner join PERSON p on p.ID=i.PERSON_ID inner join TURBINE_USER t
on p.ID = t.LOGIN_NAME where match(i.COMMENT) against('+todo ' in boolean
mode);
+---+++---+-
+-++--+
| table | type | possible_keys | key |
key_len | ref | rows | Extra|
+---+++---+-
+-++--+
| i | ALL| NULL | NULL |
NULL | NULL| 511322 | Using where; Using temporary |
| p | eq_ref | PRIMARY,person_id_base_idx | PRIMARY |
40 | i.PERSON_ID | 1 | Using index; Distinct|
| t | ref| turbine_user_name_idx | turbine_user_name_idx |
40 | p.ID| 1 | Using index; Distinct|
The only difference between the 2 queries is the extra join. But with the
straight_join and use index I don't understand why the fulltext was
abandoned.
Table info
INTEREST
| INTEREST | CREATE TABLE `INTEREST` (
`ID` varchar(40) NOT NULL default '',
`PERSON_ID` varchar(40) NOT NULL default '',
`COMMENT` mediumtext,
`DATE_CREATED` timestamp(14) NOT NULL,
`INTEREST_ID` varchar(40) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `PERSON_ID` (`PERSON_ID`,`INTEREST_ID`),
KEY `interest_person_id_idx` (`PERSON_ID`),
KEY `interest_person_id_two_idx` (`PERSON_ID`,`COMMENT`(200)),
FULLTEXT KEY `COMMENT` (`COMMENT`)
) TYPE=MyISAM |
TURBINE_USER
TURBINE_USER | CREATE TABLE `TURBINE_USER` (
`USER_ID` int(11) NOT NULL auto_increment,
`LOGIN_NAME` varchar(40) NOT NULL default '',
`PASSWORD_VALUE` varchar(32) NOT NULL default '',
`FIRST_NAME` varchar(99) NOT NULL default '',
`LAST_NAME` varchar(99) NOT NULL default '',
`EMAIL` varchar(99) default NULL,
`CONFIRM_VALUE` varchar(99) default NULL,
`MODIFIED` timestamp(14) NOT NULL,
`CREATED` timestamp(14) NOT NULL,
`LAST_LOGIN` timestamp(14) NOT NULL,
`OBJECTDATA` mediumblob,
PRIMARY KEY (`USER_ID`),
UNIQUE KEY `EMAIL` (`EMAIL`),
KEY `turbine_user_name_idx` (`LOGIN_NAME`)
) TYPE=MyISAM |
PERSON
KEY `person_id_base_idx` (`ID`),
KEY `person_email_idx` (`EMAIL`),
KEY `person_zip_idx` (`ZIP`),
KEY `person_last_name_idx` (`LAST_NAME`),
KEY `person_first_name_idx` (`FIRST_NAME`),
KEY `person_generation_idx` (`GENERATION`),
KEY `person_birthday_idx` (`BIRTHDAY`),
KEY `person_user_created_idx` (`USER_CREATED`)
) TYPE=MyISAM |
Many Thanks,
Trevor
MyODBC errors
Mysqlians, I am getting the following errors in an application log for 4.0.16 mysql and 3.51 myODBC SQL Error: SQLSTATE=S0002 Native error=1146 '[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16-Max-log]Table 'TRIBE.NETWORK_VECTOR' doesn't exist' SQL Error: SQLSTATE=S1000 Native error=1136 '[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.16-Max-log]Column count doesn't match value count at row 1' I have no problems reading/writing when I connect with the same user from the command line or otherwise. The table does exist with correct permissions and is read by the application earlier. The failings all deal with write operations. Many Thanks, Trevor
Replication Error 1053
Mysqlians, The sql_thread on our slave slave server has been stopping with error 1053 ERROR: 1053 Server shutdown in progress 031203 16:05:01 Slave: error 'Server shutdown in progress' on query 'INSERT INTO INTEREST ( ID, PERSON_ID, COMMENT, DATE_CREATED, INTEREST_ID ) VALUES ( 'fffc5074-fe83-4f2e-9d4b-3d6a761c3f60', '49ea2258-3f60-4337-82be-cb15012636be', '', '2003-12-03 16:04:59', '32' )', error_code=1053 I have seen this half a dozen times and one time was in fact the master being restarted. However I have seen this several times without the master being restarted or any error in the master log at all. In addition this error has occurred on relatively low machine loads ( top/1.0 -2.0) and high (4.0-6.0). However the master server does between 1000 - 4000 questions/second. Is this a bug or something about the insert statement or something else. Thanks, Trevor
RAID Strip size
Greetings Mysqlians, Please comment on the validity of my logic: In setting the RAID(10/2disks) strip size everything I read says you must benchmark your particular system. Since that is not an option, my current logic is to have a large strip size (1024) with the reasoning that fewer writes/reads (yet longer writes) will be better in a database which has a large amount of disk access. The disk cache size is 1GB on our disk device but that is not quite enough to hold all the tables which get accessed(written to and read from) frequently. I figure setting a large stripe size is a conservative approach allowing for better scalability. Many Thanks, Trevor
Ignor writes no block them.
Greetings, In converting a master into a slave, I would like to IGNORE all writes instead of blocking them. If I block writes with LOCK TABLES then once the server is converted into a slave and the tables are unlocked all of the blocked writes in the interrum period will be written. The idea is to keep the (former master future slave ) server running so the slaves can be kept up to date. The only solution I can think of is to shutdown the slave server while the database is locked, I assume then, when the server is shutdown if will not write the blocked writes. Many Thanks, Trevor
Avg Queries per second...... per second
Greetings, From what I can gather the Queries per second average quoted by status is a pure division of Questions by Uptime in show status. Is there a way to flush these figures periodically? I want to be able to set the bin interval for this average, otherwise fluctuations get smoothed out. Many Thanks, Trevor
