← Back to team overview

maria-discuss team mailing list archive

Re: PBXT issues

 

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@xxxxxxxxxxxxxxxxxxx
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






Follow ups

References