Re: Optimizing InnoDB tables
Hi again, I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Thanks in advance. Regards, Antonio.
Re: Optimizing InnoDB tables
- Original Message - From: Antonio Fernández Pérez antoniofernan...@fabergames.com Subject: Re: Optimizing InnoDB tables I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Then all new tables will be created in their own tablespace now. It's easy to convert an existing table, too, simply do alter table yourtable engine=innodb - but that will of course take a while on large tables. The problem, however, is that there is no way to shrink the main tablespace afterwards. Your tables will all be in their own space, but the ibdata1 will still be humoungous, even though it's close to empty. Don't just delete it, btw, as it still contains metadata. The only way to get rid of those, is to export ALL innodb tables, shut down mysqld, delete all innodb files (iblog0/1, ibdata1 etc, but also db/*.ibd and the associated db/*.frm files; then start the server (it'll recreate ibdata1 as specified in your my.cnf, so shrink there, too, if required) and then import the lot again. Note that, if you have the space, you don't *have* to do that - the huge ibdata1 file doesn't do any harm; but do consider that as your dataset grows over the years, it'll become more and more of a bother to actually do it. Make sure you have backups when attempting :-) -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Problem with INSERT INTO and UPDATE queries
Hi list,
I have some problems with INSERT INTO and UPDATE queries on a big table.
Let me put the code and explain it ...
I have copied the create code of the table. This table has more than
1500 rows.
Create Table: CREATE TABLE `radacct` (
`RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT,
`AcctSessionId` varchar(32) NOT NULL DEFAULT '',
`AcctUniqueId` varchar(32) NOT NULL DEFAULT '',
`UserName` varchar(64) NOT NULL DEFAULT '',
`Realm` varchar(64) DEFAULT '',
`NASIPAddress` varchar(15) NOT NULL DEFAULT '',
`NASPortId` varchar(15) DEFAULT NULL,
`NASPortType` varchar(32) DEFAULT NULL,
`AcctStartTime` datetime NOT NULL DEFAULT '-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL DEFAULT '-00-00 00:00:00',
`AcctSessionTime` int(12) DEFAULT NULL,
`AcctAuthentic` varchar(32) DEFAULT NULL,
`ConnectInfo_start` varchar(50) DEFAULT NULL,
`ConnectInfo_stop` varchar(50) DEFAULT NULL,
`AcctInputOctets` bigint(20) DEFAULT NULL,
`AcctOutputOctets` bigint(20) DEFAULT NULL,
`CalledStationId` varchar(50) NOT NULL DEFAULT '',
`CallingStationId` varchar(50) NOT NULL DEFAULT '',
`AcctTerminateCause` varchar(32) NOT NULL DEFAULT '',
`ServiceType` varchar(32) DEFAULT NULL,
`FramedProtocol` varchar(32) DEFAULT NULL,
`FramedIPAddress` varchar(15) NOT NULL DEFAULT '',
`AcctStartDelay` int(12) DEFAULT NULL,
`AcctStopDelay` int(12) DEFAULT NULL,
`XAscendSessionSvrKey` varchar(10) DEFAULT NULL,
PRIMARY KEY (`RadAcctId`),
KEY `user_start` (`UserName`,`AcctStartTime`),
KEY `nasip_starttime` (`NASIPAddress`,`AcctStartTime`),
KEY `stop_nasip_start` (`AcctStopTime`,`NASIPAddress`,`AcctStartTime`),
KEY `acctsesid_user_nasip` (`UserName`,`AcctSessionId`,`NASIPAddress`),
KEY `user_stop` (`UserName`,`AcctStopTime`)
) ENGINE=InnoDB AUTO_INCREMENT=17694651 DEFAULT CHARSET=utf8
###
The next text shows the entries in mysql-slow.log.
###
# Time: 140625 9:37:45
# User@Host: radius[radius] @ [192.168.0.30]
# Thread_id: 94892163 Schema: radius Last_errno: 0 Killed: 0
# Query_time: 2.327159 Lock_time: 0.86 Rows_sent: 0 Rows_examined:
0 Rows_affected: 1 Rows_read: 0
# Bytes_sent: 19
use radius;
SET timestamp=1403681865;
INSERT INTO radacct (acctsessionid,acctuniqueid,
username, realm,nasipaddress,
nasportid, nasporttype, acctstarttime,
acctstoptime, acctsessiontime, acctau
thentic,connectinfo_start, connectinfo_stop,
acctinputoctets, acctoutputoctets, calledstationid,
callingstationid, acctterminatecause, servicetype,
framedprotocol, framedipaddress,
acctstartdelay, acctstopdelay,xascendsessionsvrkey)
VALUES ('80004ef0', '78d3fc2661258da5',
'zu629LAYUT', '', '178.136.71.251', '2147503856',
'Wireless-802.11', '2014
-06-25 09:37:26', '-00-00 00:00:00', '0', '',
'', '', '0', '0', 'tururu', '00-00-11-11-11-11',
'', '', '', '178.136.71.1', '0', '0', '');
# User@Host: radius[radius] @ [192.168.0.31]
# Thread_id: 97905294 Schema: radius Last_errno: 0 Killed: 0
# Query_time: 2.397604 Lock_time: 0.62 Rows_sent: 0 Rows_examined:
1 Rows_affected: 1 Rows_read: 1
# Bytes_sent: 52
SET timestamp=1403681865;
UPDATE radacct SET framedipaddress =
'182.138.214.240', acctsessiontime = '4199',
acctinputoctets = '0' 32 |
'12327909', acctoutputo
ctets= '0' 32 |
'294177486' WHERE acctsessionid = '805063b1' AND
username= 'fa239DADUX' AND nasipaddress=
'182.138.214.50';
###
The previous query is converted because I want to use EXPLAIN ...
###
SELECT framedipaddress = '172.21.13.152', acctsessiontime
= '4199', acctinputoctets = '0' 32
|'12327909', acctoutputo
ctets= '0' 32 |
'294177486'
FROM radacct
WHERE acctsessionid = '805063b1' AND username=
'fa239DADUX' AND nasipaddress= '192.168.254.10';
++-+-+--+---+--+-+---+--+-+
| id | select_type | table | type |
possible_keys |
key | key_len | ref | rows | Extra |
++-+-+--+---+--+-+---+--+-+
| 1 | SIMPLE | radacct |
Re: Optimizing InnoDB tables
Have a look at this: https://rtcamp.com/tutorials/mysql/enable-innodb-file-per-table/ -- Andre Matos andrema...@mineirinho.org On Jun 25, 2014, at 2:22 AM, Antonio Fernández Pérez antoniofernan...@fabergames.com wrote: Hi again, I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Thanks in advance. Regards, Antonio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error 29, file not found (errcode: 13)
I followed the manpage for mysqlimport: thufir@dur:~$ thufir@dur:~$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test ERROR 1045 (28000): Access denied for user 'thufir'@'localhost' (using password: NO) thufir@dur:~$ thufir@dur:~$ thufir@dur:~$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test -u root -p Enter password: ERROR 1049 (42000): Unknown database 'test' thufir@dur:~$ thufir@dur:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 54 Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql create database test; Query OK, 1 row affected (0.01 sec) mysql quit Bye thufir@dur:~$ thufir@dur:~$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test -u root -p Enter password: thufir@dur:~$ thufir@dur:~$ ed a 100 Max Sydow 101 Count Dracula . w imptest.txt 32 q thufir@dur:~$ thufir@dur:~$ od -c imptest.txt 000 1 0 0 \t M a x S y d o w \n 1 0 020 1 \t C o u n t D r a c u l a \n 040 thufir@dur:~$ thufir@dur:~$ mysqlimport --local test imptest.txt -u root -p Enter password: test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 thufir@dur:~$ thufir@dur:~$ mysql -e 'SELECT * FROM imptest' test -u root -p Enter password: +--+---+ | id | n | +--+---+ | 100 | Max Sydow | | 101 | Count Dracula | +--+---+ thufir@dur:~$ thufir@dur:~$ so this looks like the route to go. -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
