Hi Paul, Thanks for your valuable explanation. Of course that I will apply all recommendation first, but, I will take a deep look into the like you sent me. As soon as I get a chance today I will apply the adjustments, and make it visible here. Thanks, Alex Em 05/05/2010, às 07:45, Paul McCullagh escreveu:
Hi Alexandre,
As Arjen says, PBXT is unconventional, and sometimes requires tuning, as I explained in my last conference talk: http://www.primebase.org/download/pbxt-uc-2010.pdf .
While tuning may be necessary, it may be worth it for the results we can achieve.
But I don't expect you to read that now, instead I will help you through the first steps.
I think there are 2 problems:
(1) Your index cache may be too low: pbxt_index_cache_size.
Set this to about half of what you set the innodb buffer pool. (e.g. if innodb_buffer_pool_size=500M, then set pbxt_index_cache_size=250M).
You can also set pbxt_record_cache_size=250M (the other half of memory), and I recommend pbxt_log_cache_size=128M (a fixed value, not dependent on the amount of memory on your machine).
(2) The PBXT estimate of the average row size of telpbxt is probably too high. The result is that the table is growing too big with 10m rows.
In order to correct this problem you need to set AVG_ROW_LENGTH for the table. For example:
CREATE TABLE telpbxt ( cpf bigint(20) DEFAULT NULL, ... ) ENGINE=PBXT DEFAULT AVG_ROW_LENGTH=250 CHARSET=latin1;
Basically you should make an estimate of the average byte length of your rows, and set AVG_ROW_LENGTH for the table accordingly.
In addition, if 90% of your rows have a maximum row length which is only 10-20% greater than the average row length, then I recommend setting the AVG_ROW_LENGTH to this value.
PBXT can help you out with this. Do the following:
(1) Load the telpbxt table with a few 1000 rows (or use telpbxt2, since telpbxt already has too many rows in it). (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this on a table with millions of rows. It will take very long to complete). (3) Check the MySQL error log, you should see something like the output below.
CHECK TABLE: ./test/telpbxt Record buffer size = 1024 Fixed length rec. len. = 1020 Handle data record size = 638 Min/max header size = 14/26 Min/avg/max record size = 19/624/1019 Avg row len set for tab = not specified Rows fixed length = NO Maximum fixed size = 16384 ...
Paste the output in an e-mail, and I will show you what to use for the AVG_ROW_LENGTH.
Best regards,
Paul
On May 4, 2010, at 8:55 PM, Alexandre Almeida wrote:
Hi Guys,
I am setting an enviroment with MariaDB + PBXT, but, I am pretty much disappointed.
Do you know if this time (see below) makes sense?
Five hours to create a PBXT table with 10mio records... It's too much!!! See my steps:
create table telpbxt like telinno; Query OK, 0 rows affected (0,50 sec)
fis-> alter table telpbxt engine = pbxts; Query OK, 0 rows affected, 1 warning (0,07 sec) Records: 0 Duplicates: 0 Warnings: 0
fis-> alter table telpbxt engine = pbxt; Query OK, 0 rows affected (0,26 sec) Records: 0 Duplicates: 0 Warnings: 0
fis-> show create table telpbxt; +--------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | telpbxt | CREATE TABLE `telpbxt` ( `cpf` bigint(20) DEFAULT NULL, `nome` varchar(100) DEFAULT NULL, `log` varchar(100) DEFAULT NULL, `endereco` varchar(100) DEFAULT NULL, `comp1` varchar(100) DEFAULT NULL, `comp2` varchar(100) DEFAULT NULL, `bairro` varchar(100) DEFAULT NULL, `cidade` varchar(100) DEFAULT NULL, `cep` varchar(100) DEFAULT NULL, `ddd` varchar(100) DEFAULT NULL, `telefone` varchar(100) DEFAULT NULL, KEY `idx2` (`nome`(10)) ) ENGINE=PBXT DEFAULT CHARSET=latin1 | +--------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec)
fis-> insert into telpbxt select * from tel; Query OK, 10262048 rows affected (5 hours 2 min 22,14 sec) Records: 10262048 Duplicates: 0 Warnings: 0 <alexandre(jpg).jpg> _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
