Hi Peter,
If I change my innodb_buffer_pool_size to anything above 2000M I cant get mysql to restart. SO I have kept it at 2000M.
Im not sure how to tell how much of the 8G RAM is available to mysql ??
I ran your query using the 5 tables in question....
select engine,data,indexes,total
from (
select
ifnull(engine,'TOTALS') as engine,
concat(data,' GB') as data,
concat(indexes,' GB') as indexes,
concat(tot,' GB') as total,
if(engine is null,-1,tot) as ord
from (
select
engine,
round( sum(data_length)/1024/1024/1024, 2 ) as data,
round( sum(index_length)/1024/1024/1024, 2 ) as indexes,
round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
from information_schema.tables
where table_schema= "sst" and table_name in("salesledger", "receipts", "users", "countries", "groups")
group by engine with rollup
) sums
) list
order by list.ord desc;
'InnoDB', '0.12 GB', '0.05 GB', '0.17 GB'
'TOTALS', '0.12 GB', '0.05 GB', '0.17 GB'
Here are the Create Tables from mySQl CLient...
CREATE TABLE `salesledger` (
`accountManagerID` varchar(3) NOT NULL DEFAULT '',
`AOLP` decimal(14,2) NOT NULL DEFAULT '0.00',
`balance` decimal(14,2) NOT NULL DEFAULT '0.00',
`bankCharges` decimal(12,2) NOT NULL DEFAULT '0.00',
`cash` decimal(14,2) NOT NULL DEFAULT '0.00',
`CCmanagerID` varchar(3) NOT NULL DEFAULT '',
`conversionRate` decimal(10,3) NOT NULL DEFAULT '0.000',
`costcentre` varchar(1) NOT NULL DEFAULT '',
`countryID` varchar(2) NOT NULL DEFAULT ' ',
`creditDaysTaken` int(11) NOT NULL DEFAULT '0',
`customerID` varchar(7) NOT NULL DEFAULT ' ',
`dateExported` date NOT NULL DEFAULT '0000-00-00',
`daybook` enum('T','F') NOT NULL DEFAULT 'F',
`docType` varchar(3) NOT NULL DEFAULT '',
`discount` decimal(12,2) NOT NULL DEFAULT '0.00',
`DOLP` date NOT NULL DEFAULT '0000-00-00',
`dueDate` date NOT NULL DEFAULT '0000-00-00',
`exportedToSage` enum('T','F') NOT NULL DEFAULT 'F',
`groupID` varchar(3) NOT NULL DEFAULT '',
`invNum` int(11) NOT NULL DEFAULT '0',
`invoiceCurrency` varchar(3) NOT NULL DEFAULT ' ',
`invoiceDate` date NOT NULL DEFAULT '0000-00-00',
`net` decimal(14,2) NOT NULL DEFAULT '0.00',
`netWeight` decimal(14,3) NOT NULL DEFAULT '0.000',
`orderref` int(11) NOT NULL DEFAULT '0',
`payable` decimal(14,2) NOT NULL DEFAULT '0.00',
`paymentStatus` varchar(1) NOT NULL DEFAULT ' ',
`paidToDate` decimal(14,2) NOT NULL DEFAULT '0.00',
`PDF` varchar(100) NOT NULL DEFAULT '',
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`receiptNo` int(11) NOT NULL DEFAULT '0',
`taxCode` varchar(2) NOT NULL DEFAULT '',
`transactionType` varchar(2) NOT NULL DEFAULT ' ',
`userID` varchar(3) NOT NULL DEFAULT '',
`vat` decimal(13,2) NOT NULL DEFAULT '0.00',
`lastUpdated` date NOT NULL DEFAULT '0001-01-01',
`boxes` int(11) NOT NULL DEFAULT '0',
`freshFrozen` varchar(1) NOT NULL DEFAULT 'F',
`monthPeriodEndFlag` enum('F','T') NOT NULL DEFAULT 'F',
`comment` varchar(250) NOT NULL DEFAULT '',
`invoiceLogFlag` enum('F','T') NOT NULL DEFAULT 'F',
`agentID` varchar(3) NOT NULL DEFAULT 'NON',
`accountID` varchar(10) NOT NULL DEFAULT '',
`daybookNo` int(11) NOT NULL DEFAULT '0',
`paymentNo` int(12) NOT NULL DEFAULT '0',
`balAccType` varchar(10) NOT NULL DEFAULT '',
`ediSent` date NOT NULL DEFAULT '0001-01-01',
`ediFlag` enum('T','F') NOT NULL DEFAULT 'F',
`dayBookNo2` int(11) NOT NULL DEFAULT '0',
`paidInFullDate` date NOT NULL DEFAULT '0001-01-01',
`businessGroup` varchar(4) NOT NULL DEFAULT '',
`vatCode` varchar(10) NOT NULL DEFAULT '',
`nomCode` varchar(4) NOT NULL DEFAULT '',
`vatReportFlag` enum('T','F') NOT NULL DEFAULT 'F',
`vatReportDate` date NOT NULL DEFAULT '0001-01-01',
`bankAmount` decimal(12,2) NOT NULL DEFAULT '0.00',
`entryDate` date NOT NULL DEFAULT '0001-01-01',
`unallocatedNo` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`pkey`),
KEY `countryID` (`countryID`) USING BTREE,
KEY `invNum` (`invNum`) USING BTREE,
KEY `customerID` (`customerID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3207448 DEFAULT CHARSET=latin1
CREATE TABLE `users` (
`username` varchar(15) NOT NULL DEFAULT ' ',
`password` varchar(15) NOT NULL DEFAULT ' ',
`Name` varchar(30) NOT NULL DEFAULT ' ',
`countryID` varchar(2) NOT NULL DEFAULT ' ',
`entries` int(11) NOT NULL DEFAULT '0',
`userID` varchar(3) NOT NULL DEFAULT ' ',
`Gender` varchar(1) NOT NULL DEFAULT ' ',
`loggedIn` enum('T','F') NOT NULL DEFAULT 'F',
`accountManager` enum('T','F') NOT NULL DEFAULT 'F',
`ccmanager` enum('T','F') NOT NULL DEFAULT 'F',
`labelPrinterName` varchar(50) NOT NULL DEFAULT ' ',
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`overrideFileMaintenance` enum('T','F') DEFAULT 'F',
`presentationNames` enum('T','F') DEFAULT 'T',
`gradeNames` enum('T','F') DEFAULT 'F',
`customerNames` enum('T','F') DEFAULT 'F',
`modifyOrder` enum('T','F') DEFAULT 'F',
`lastCustomerDownload` date DEFAULT NULL,
`emailAddress` varchar(45) DEFAULT ' ',
`emailpassword` varchar(45) DEFAULT ' ',
`outgoingSMTP` varchar(100) DEFAULT ' ',
`emailPort` int(11) DEFAULT '25',
`lastSalesLedgerDownload` date DEFAULT NULL,
`lastBankDownload` date DEFAULT NULL,
`copyLocalFiles` enum('T','F') DEFAULT 'T',
`clearProductLine` enum('T','F') DEFAULT 'F',
`customerBoardNo` int(11) DEFAULT '0',
`currentCustomerBoard` varchar(30) DEFAULT NULL,
`PL` enum('T','F') DEFAULT 'F',
`UKSales` enum('T','F') NOT NULL DEFAULT 'F',
`location` varchar(2) NOT NULL DEFAULT '',
`generalPostingFromDate` date NOT NULL DEFAULT '0001-01-01
`generalPostingToDate` date NOT NULL DEFAULT '0001-01-01',
PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=latin1
CREATE TABLE `countries` (
`countryID` varchar(2) NOT NULL DEFAULT ' ',
`country` varchar(20) NOT NULL DEFAULT ' ',
`czechName` varchar(20) NOT NULL DEFAULT ' ',
`EUMember` enum('T','F') NOT NULL DEFAULT 'F',
`EUTaxCode` varchar(2) NOT NULL DEFAULT ' ',
`FrenchName` varchar(20) NOT NULL DEFAULT ' ',
`GermanName` varchar(20) NOT NULL DEFAULT ' ',
`italianName` varchar(20) NOT NULL DEFAULT ' ',
`localTaxCode` varchar(2) NOT NULL DEFAULT ' ',
`taxCode` varchar(1) NOT NULL DEFAULT ' ',
`freeFormatVatTaxCode` varchar(2) NOT NULL DEFAULT '',
`name` varchar(30) NOT NULL DEFAULT ' ',
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`productSource` enum('T','F') NOT NULL DEFAULT 'F',
`SlovakianName` varchar(20) NOT NULL DEFAULT ' ',
`SpanishName` varchar(20) NOT NULL DEFAULT ' ',
`TransportTaxCode` varchar(2) NOT NULL DEFAULT ' ',
`lastUsedBy` varchar(45) DEFAULT ' ',
`inUse` varchar(45) DEFAULT ' ',
`customerBoardNo` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=latin1
Table: customers
Create Table: CREATE TABLE `customers` (
`customerID` varchar(7) NOT NULL DEFAULT ' ',
`accountManagerID` varchar(3) NOT NULL DEFAULT ' ',
`address1` varchar(35) NOT NULL DEFAULT ' ',
`accountsEmail` varchar(150) NOT NULL DEFAULT ' ',
`address2` varchar(35) NOT NULL DEFAULT ' ',
`address3` varchar(35) NOT NULL DEFAULT ' ',
`address4` varchar(35) NOT NULL DEFAULT ' ',
`AccountSetUp` date NOT NULL DEFAULT '0000-00-00',
`agentFlag` enum('T','F') NOT NULL DEFAULT 'F',
`areaID` varchar(2) NOT NULL DEFAULT ' ',
`accountsContact` varchar(35) NOT NULL DEFAULT ' ',
`balance` decimal(14,2) NOT NULL DEFAULT '0.00',
`bankID` varchar(10) NOT NULL DEFAULT ' ',
`bankAccount` varchar(20) NOT NULL DEFAULT ' ',
`baseRatePence` decimal(8,2) NOT NULL DEFAULT '0.00',
`baseRateCents` decimal(8,2) NOT NULL DEFAULT '0.00',
`baseRatePC` decimal(5,2) NOT NULL DEFAULT '0.00',
`creditLimit` int(11) NOT NULL DEFAULT '0',
`classification` varchar(1) NOT NULL DEFAULT ' ',
`CCmanagerID` varchar(3) NOT NULL DEFAULT ' ',
`creditreference` varchar(10) NOT NULL DEFAULT ' ',
`creditInfo` varchar(50) NOT NULL DEFAULT ' ',
`creditLimitCurrency` varchar(3) NOT NULL DEFAULT ' ',
`creditFlag` varchar(1) NOT NULL DEFAULT ' ',
`countryID` varchar(2) NOT NULL DEFAULT ' ',
`deliveryAddressFlag` enum('T','F') NOT NULL DEFAULT 'F',
`delTime` varchar(10) NOT NULL DEFAULT ' ',
`deliveryTerms` varchar(3) NOT NULL DEFAULT ' ',
`discountPC` decimal(5,2) NOT NULL DEFAULT '0.00',
`discount2PC` decimal(5,2) NOT NULL DEFAULT '0.00',
`discountPCExtra` decimal(5,2) NOT NULL DEFAULT '0.00',
`DOLO` date NOT NULL DEFAULT '0000-00-00',
`EDI` enum('T','F') NOT NULL DEFAULT 'F',
`euroCreditLimit` decimal(13,2) NOT NULL DEFAULT '0.00',
`expiryDate` date NOT NULL DEFAULT '0000-00-00',
`Excess` decimal(10,2) NOT NULL DEFAULT '0.00',
`fax` varchar(20) NOT NULL DEFAULT ' ',
`frozen` enum('T','F') NOT NULL DEFAULT 'F',
`groupID` varchar(3) NOT NULL DEFAULT ' ',
`invoiceComm` varchar(1) NOT NULL DEFAULT ' ',
`invoiceLang` varchar(2) NOT NULL DEFAULT ' ',
`InvoiceCurrency` varchar(3) NOT NULL DEFAULT ' ',
`invoiceEmail` varchar(250) NOT NULL DEFAULT ' ',
`invoiceEmail2` varchar(250) NOT NULL DEFAULT ' ',
`invoiceText` varchar(250) NOT NULL DEFAULT '',
`invoiceText2` varchar(250) NOT NULL DEFAULT '',
`labelFormat` varchar(1) NOT NULL DEFAULT ' ',
`labelType` int(11) NOT NULL DEFAULT '0',
`number` varchar(4) NOT NULL DEFAULT ' ',
`name` varchar(100) NOT NULL DEFAULT ' ',
`name2` varchar(50) NOT NULL DEFAULT '',
`quotationComm` varchar(1) NOT NULL DEFAULT ' ',
`quotationLang` varchar(2) NOT NULL DEFAULT ' ',
`quotationEmail` varchar(254) NOT NULL DEFAULT ' ',
`quotationEmailFrozen` varchar(254) NOT NULL DEFAULT '',
`salesContact` varchar(35) NOT NULL DEFAULT ' ',
`salesContactEmail` varchar(150) NOT NULL DEFAULT '',
`searchName` varchar(50) NOT NULL DEFAULT ' ',
`shortName` varchar(30) NOT NULL DEFAULT ' ',
`salesTemp` decimal(13,2) NOT NULL DEFAULT '0.00',
`telephone` varchar(20) NOT NULL DEFAULT ' ',
`tradingTermsID` varchar(1) NOT NULL DEFAULT ' ',
`transact_userID` varchar(3) NOT NULL DEFAULT '',
`transportUKID` varchar(2) NOT NULL DEFAULT ' ',
`transportEUID1` varchar(3) NOT NULL DEFAULT ' ',
`transportUKIDfri` varchar(2) NOT NULL DEFAULT '',
`transportEUIDfri` varchar(3) NOT NULL DEFAULT '',
`transportEUID2` varchar(3) NOT NULL DEFAULT ' ',
`transportRateEU` decimal(5,2) NOT NULL DEFAULT '0.00',
`transportRateEUExtra` decimal(5,2) NOT NULL DEFAULT '0.00'
`TermsDays` int(11) NOT NULL DEFAULT '0',
`transportPencePerKG` decimal(5,2) NOT NULL DEFAULT '0.00',
`terms` varchar(3) NOT NULL DEFAULT ' ',
`VATreg` varchar(15) NOT NULL DEFAULT '',
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`warehouseID` varchar(1) NOT NULL DEFAULT '',
`inUse` varchar(25) NOT NULL DEFAULT ' ',
`lastUsedBy` varchar(25) NOT NULL DEFAULT ' ',
`town` varchar(45) NOT NULL DEFAULT ' ',
`postcode` varchar(12) NOT NULL DEFAULT ' ',
`blocked` enum('T','F') NOT NULL DEFAULT 'F',
`customerRefFlag` enum('T','F') NOT NULL DEFAULT 'F',
`insuranceExcluded` enum('T','F') NOT NULL DEFAULT 'F',
`printBonDeLivraison` enum('T','F') NOT NULL DEFAULT 'T',
`printBonDeTransport` enum('T','F') NOT NULL DEFAULT 'T',
`lastUpdated` date NOT NULL DEFAULT '0001-01-01',
`kebabBestBeforeDays` int(11) NOT NULL DEFAULT '0',
`smokedBestBeforeDays` int(11) NOT NULL DEFAULT '0',
`termsDaysFrozen` int(11) NOT NULL DEFAULT '0',
`agentID` varchar(8) NOT NULL DEFAULT 'NON',
`quotationFaxNo` varchar(50) NOT NULL DEFAULT '',
`quotation_Mon` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Tue` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Wed` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Thu` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Fri` enum('T','F') NOT NULL DEFAULT 'T',
`copyOver` enum('T','F') NOT NULL DEFAULT 'F',
`bar01` enum('T','F') NOT NULL DEFAULT 'T',
`bar3103` enum('T','F') NOT NULL DEFAULT 'F',
`bar10` enum('T','F') NOT NULL DEFAULT 'F',
`bar15` enum('T','F') NOT NULL DEFAULT 'F',
`frozenQuotationCode` varchar(1) NOT NULL DEFAULT '',
`GLN` varchar(20) NOT NULL DEFAULT '',
`EDIEmail` varchar(50) NOT NULL DEFAULT '',
`auxID` varchar(25) NOT NULL DEFAULT '',
`quotationEUFreshCode` varchar(3) NOT NULL DEFAULT '',
`printEUFresh` varchar(1) NOT NULL DEFAULT 'F',
`quotationEmailText` varchar(254) NOT NULL DEFAULT '',
`quotationEmailText2` varchar(254) NOT NULL DEFAULT '',
`quotationEmailText3` varchar(254) NOT NULL DEFAULT '',
`agentEmail` varchar(254) NOT NULL DEFAULT '',
`printHardCopy` enum('T','F') NOT NULL DEFAULT 'F',
`printSpainFresh` varchar(1) NOT NULL DEFAULT '',
`IC_baseRatePence` decimal(11,2) NOT NULL DEFAULT '0.00',
`IC_baseRatePC` decimal(11,2) NOT NULL DEFAULT '0.00',
`hasOrderNum` enum('T','F') NOT NULL DEFAULT 'F' COMMENT 'c
al order number (NOT cust reference)',
`showBestBeforeOnInvoice` enum('T','F') NOT NULL DEFAULT 'F
`invoiceFaxNo` varchar(20) NOT NULL DEFAULT '',
`invoiceFaxNo2` varchar(20) NOT NULL DEFAULT '',
`quotationNewlynEmail` varchar(250) NOT NULL DEFAULT '',
`newlynBaseRatePence` decimal(11,2) NOT NULL DEFAULT '0.00'
`newlynBaseRatePC` decimal(11,2) NOT NULL DEFAULT '0.00',
`newlynBaseRateCents` decimal(11,2) NOT NULL DEFAULT '0.00'
`quotationNewlynFreshCode` varchar(3) NOT NULL DEFAULT '',
`printNewlynFresh` varchar(1) NOT NULL DEFAULT '',
`splitPDFInvoice` enum('T','F') NOT NULL DEFAULT 'F',
`transportCompany` varchar(30) NOT NULL DEFAULT '',
`transportCompanyCode` varchar(20) NOT NULL DEFAULT '',
`hasOverDueBalance` enum('T','F') NOT NULL DEFAULT 'F',
`lastStatementDate` date NOT NULL DEFAULT '0000-00-00',
`GLNEDIIV` varchar(20) NOT NULL DEFAULT '',
`GLNEDIPR` varchar(20) NOT NULL DEFAULT '',
`statementEmailAddress` varchar(250) NOT NULL DEFAULT '',
`bestBeforeDateFormat` varchar(10) NOT NULL DEFAULT '',
`quotationIrishFreshCode` varchar(4) NOT NULL DEFAULT '',
`isInternal` enum('T','F') NOT NULL DEFAULT 'F',
`isCustomerRef` enum('T','F') NOT NULL DEFAULT 'F',
`excludeFromMailings` enum('T','F') NOT NULL DEFAULT 'F',
PRIMARY KEY (`pkey`),
KEY `customerID` (`customerID`) USING BTREE,
KEY `countryID` (`countryID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=573721 DEFAULT CHARSET=latin1
CREATE TABLE `groups` (
`ID` varchar(3) NOT NULL DEFAULT ' ',
`name` varchar(30) NOT NULL DEFAULT ' ',
`invInstructions` varchar(100) NOT NULL DEFAULT ' ',
`inUse` varchar(25) DEFAULT ' ',
`lastUsedBy` varchar(25) DEFAULT ' ',
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`creditReference` varchar(10) DEFAULT NULL,
`creditLimit` decimal(12,2) DEFAULT '0.00',
PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=1986 DEFAULT CHARSET=latin1
The customers table does has many fields and could be redesigned but it is something that I have inherited. Changing it just is out of the question. Too many other programs rely on it the way it is Im afraid.
Thanks again. Much appreciated.