[Maria-discuss] PBXT issues
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
Hi Alexandre On 05/05/2010, at 4:55 AM, Alexandre Almeida wrote:
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:
PBXT, like any other storage engine, requires some tuning for production performance. Regards, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
Hi Arjen, Thanks for your intervention. I don't doubt that. But, 5 hours? This load in a innodb takes no more than 40 min. Can I assume that default is useless? So, I going to download docs and try to adjust buffers, this & that to get better performance. Alex Em 04/05/2010, às 20:30, Arjen Lentz escreveu:
Hi Alexandre
On 05/05/2010, at 4:55 AM, Alexandre Almeida wrote:
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:
PBXT, like any other storage engine, requires some tuning for production performance.
Regards, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget.
Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
Hi Alexandre On 05/05/2010, at 1:15 PM, Alexandre Almeida wrote:
Thanks for your intervention.
I don't doubt that. But, 5 hours? This load in a innodb takes no more than 40 min. Can I assume that default is useless?
Not necessarily, depends on actual vs predicted row size, and other factors. For anything production/performance, technically any default config is useless, except for MySQL in general and MyISAM they work remarkably well for a good long time - even InnoDB. We have clients that run on InnoDB with default buffer pool size etc.... I think we can agree that's no good.
So, I going to download docs and try to adjust buffers, this & that to get better performance.
Excellent. It is a *very* different engine, so presuming anything is not wise (or fair). Regards, Arjen.
Em 04/05/2010, às 20:30, Arjen Lentz escreveu:
Hi Alexandre
On 05/05/2010, at 4:55 AM, Alexandre Almeida wrote:
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:
PBXT, like any other storage engine, requires some tuning for production performance.
Regards, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget.
Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
-- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
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
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
Hi!
"Paul" == Paul McCullagh <paul.mccullagh@primebase.org> writes:
<cut> Paul> PBXT can help you out with this. Do the following: Paul> (1) Load the telpbxt table with a few 1000 rows (or use telpbxt2, Paul> since telpbxt already has too many rows in it). Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this Paul> on a table with millions of rows. It will take very long to complete). Paul> (3) Check the MySQL error log, you should see something like the Paul> output below. Paul> CHECK TABLE: ./test/telpbxt Paul> Record buffer size = 1024 Paul> Fixed length rec. len. = 1020 Paul> Handle data record size = 638 Paul> Min/max header size = 14/26 Paul> Min/avg/max record size = 19/624/1019 Paul> Avg row len set for tab = not specified Paul> Rows fixed length = NO Paul> Maximum fixed size = 16384 Paul> ... Paul> Paste the output in an e-mail, and I will show you what to use for the Paul> AVG_ROW_LENGTH. Would it be hard for PBXT to as part of the CHECK TABLE add: Optimial create AVG_ROW_LENGTH: ? Regards, Monty
Hi Monty, On May 5, 2010, at 7:02 PM, Michael Widenius wrote:
"Paul" == Paul McCullagh <paul.mccullagh@primebase.org> writes:
<cut>
Paul> PBXT can help you out with this. Do the following:
Paul> (1) Load the telpbxt table with a few 1000 rows (or use telpbxt2, Paul> since telpbxt already has too many rows in it). Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this Paul> on a table with millions of rows. It will take very long to complete). Paul> (3) Check the MySQL error log, you should see something like the Paul> output below.
Paul> CHECK TABLE: ./test/telpbxt Paul> Record buffer size = 1024 Paul> Fixed length rec. len. = 1020 Paul> Handle data record size = 638 Paul> Min/max header size = 14/26 Paul> Min/avg/max record size = 19/624/1019 Paul> Avg row len set for tab = not specified Paul> Rows fixed length = NO Paul> Maximum fixed size = 16384 Paul> ...
Paul> Paste the output in an e-mail, and I will show you what to use for the Paul> AVG_ROW_LENGTH.
Would it be hard for PBXT to as part of the CHECK TABLE add:
Optimial create AVG_ROW_LENGTH: ?
Currently CHECK TABLE prints: Average comp. rec. len. = 200 This is the closest to your suggestion of an "Optimal create AVG_ROW_LENGTH", there is at the moment. But, this value is not necessarily optimal. Exactly what is optimal depends on a few additional factors, like whether you want to optimize for size or caching behavior. What I can do is add more information to help in this decision making process, and output a "Recommended AVG_ROW_LENGTH". Then I can change OPTIMIZE TABLE to automatically set this value, if no explicit AVG_ROW_LENGTH has been set yet. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
Paul, I was wondering. Can you say that PBXT works much better with fixed length rows? Alex Em 06/05/2010, às 03:32, Paul McCullagh escreveu:
Hi Monty,
On May 5, 2010, at 7:02 PM, Michael Widenius wrote:
> "Paul" == Paul McCullagh <paul.mccullagh@primebase.org> writes:
<cut>
Paul> PBXT can help you out with this. Do the following:
Paul> (1) Load the telpbxt table with a few 1000 rows (or use telpbxt2, Paul> since telpbxt already has too many rows in it). Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this Paul> on a table with millions of rows. It will take very long to complete). Paul> (3) Check the MySQL error log, you should see something like the Paul> output below.
Paul> CHECK TABLE: ./test/telpbxt Paul> Record buffer size = 1024 Paul> Fixed length rec. len. = 1020 Paul> Handle data record size = 638 Paul> Min/max header size = 14/26 Paul> Min/avg/max record size = 19/624/1019 Paul> Avg row len set for tab = not specified Paul> Rows fixed length = NO Paul> Maximum fixed size = 16384 Paul> ...
Paul> Paste the output in an e-mail, and I will show you what to use for the Paul> AVG_ROW_LENGTH.
Would it be hard for PBXT to as part of the CHECK TABLE add:
Optimial create AVG_ROW_LENGTH: ?
Currently CHECK TABLE prints:
Average comp. rec. len. = 200
This is the closest to your suggestion of an "Optimal create AVG_ROW_LENGTH", there is at the moment.
But, this value is not necessarily optimal.
Exactly what is optimal depends on a few additional factors, like whether you want to optimize for size or caching behavior.
What I can do is add more information to help in this decision making process, and output a "Recommended AVG_ROW_LENGTH".
Then I can change OPTIMIZE TABLE to automatically set this value, if no explicit AVG_ROW_LENGTH has been set yet.
Best regards,
Paul
-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
On May 6, 2010, at 1:25 PM, Alexandre Almeida wrote:
Paul,
I was wondering. Can you say that PBXT works much better with fixed length rows?
PBXT works well with both fixed and variable length rows. But fix length records will not generally not require any tuning.
Alex
<alexandre(jpg).jpg>
Em 06/05/2010, às 03:32, Paul McCullagh escreveu:
Hi Monty,
On May 5, 2010, at 7:02 PM, Michael Widenius wrote:
>> "Paul" == Paul McCullagh <paul.mccullagh@primebase.org> writes:
<cut>
Paul> PBXT can help you out with this. Do the following:
Paul> (1) Load the telpbxt table with a few 1000 rows (or use telpbxt2, Paul> since telpbxt already has too many rows in it). Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this Paul> on a table with millions of rows. It will take very long to complete). Paul> (3) Check the MySQL error log, you should see something like the Paul> output below.
Paul> CHECK TABLE: ./test/telpbxt Paul> Record buffer size = 1024 Paul> Fixed length rec. len. = 1020 Paul> Handle data record size = 638 Paul> Min/max header size = 14/26 Paul> Min/avg/max record size = 19/624/1019 Paul> Avg row len set for tab = not specified Paul> Rows fixed length = NO Paul> Maximum fixed size = 16384 Paul> ...
Paul> Paste the output in an e-mail, and I will show you what to use for the Paul> AVG_ROW_LENGTH.
Would it be hard for PBXT to as part of the CHECK TABLE add:
Optimial create AVG_ROW_LENGTH: ?
Currently CHECK TABLE prints:
Average comp. rec. len. = 200
This is the closest to your suggestion of an "Optimal create AVG_ROW_LENGTH", there is at the moment.
But, this value is not necessarily optimal.
Exactly what is optimal depends on a few additional factors, like whether you want to optimize for size or caching behavior.
What I can do is add more information to help in this decision making process, and output a "Recommended AVG_ROW_LENGTH".
Then I can change OPTIMIZE TABLE to automatically set this value, if no explicit AVG_ROW_LENGTH has been set yet.
Best regards,
Paul
-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
Paul, Do you any clue why I get this during select? | 69305170 | 1 | | 69306030 | 2 | | 69306290 | 1 | | 69306620 | 1 | | 69307020 | 1 | | 69307040 | 1 | | 69307170 | 1 | | ITAPETININGA | 1 | | R | 1 | | RAUL MOREIRA OLIVEIRA | 1 | | VL DALILA | 1 | | ¥j§„«m¦‚£l¢€—m‰|ˆhˆ|~_žƒ²[—†Œd“‰ƒjtŒ ¤më‡úrøøtö{õlò{òdõzómåwärÏq¨x‘sœ‚³xµ€³y¿v fis-> fis-> fis-> The statement was: select cep, count(0) from telpbxt group by cep; Please, be noticed the query are showing wrong data. The first column is ZIP Code (only numbers) and it's showing city name, customer name and bourough... These columns are not referenced in this simple query. Another weird things is: the query just stopped without error nor presenting the time, rows, etc. Ooops, the table structure is: CREATE TABLE `telpbxt` ( `cpf` bigint(20) DEFAULT NULL, `nome` tinytext, `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 ) ENGINE=PBXT DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=94 I have 10.3 mio rows. I know I know this varchar(100) sucks... a lot! Thanks for you help. Alex Em 06/05/2010, às 03:32, Paul McCullagh escreveu:
Hi Monty,
On May 5, 2010, at 7:02 PM, Michael Widenius wrote:
> "Paul" == Paul McCullagh <paul.mccullagh@primebase.org> writes:
<cut>
Paul> PBXT can help you out with this. Do the following:
Paul> (1) Load the telpbxt table with a few 1000 rows (or use telpbxt2, Paul> since telpbxt already has too many rows in it). Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this Paul> on a table with millions of rows. It will take very long to complete). Paul> (3) Check the MySQL error log, you should see something like the Paul> output below.
Paul> CHECK TABLE: ./test/telpbxt Paul> Record buffer size = 1024 Paul> Fixed length rec. len. = 1020 Paul> Handle data record size = 638 Paul> Min/max header size = 14/26 Paul> Min/avg/max record size = 19/624/1019 Paul> Avg row len set for tab = not specified Paul> Rows fixed length = NO Paul> Maximum fixed size = 16384 Paul> ...
Paul> Paste the output in an e-mail, and I will show you what to use for the Paul> AVG_ROW_LENGTH.
Would it be hard for PBXT to as part of the CHECK TABLE add:
Optimial create AVG_ROW_LENGTH: ?
Currently CHECK TABLE prints:
Average comp. rec. len. = 200
This is the closest to your suggestion of an "Optimal create AVG_ROW_LENGTH", there is at the moment.
But, this value is not necessarily optimal.
Exactly what is optimal depends on a few additional factors, like whether you want to optimize for size or caching behavior.
What I can do is add more information to help in this decision making process, and output a "Recommended AVG_ROW_LENGTH".
Then I can change OPTIMIZE TABLE to automatically set this value, if no explicit AVG_ROW_LENGTH has been set yet.
Best regards,
Paul
-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
Hi Alex, No idea what is happening here. I would have to investigate to find out. Please contact me directly. Maybe it would be possible for me to get access to your test machine. Then I could take a look. Best regards, Paul On May 6, 2010, at 8:52 PM, Alexandre Almeida wrote:
Paul,
Do you any clue why I get this during select?
| 69305170 | 1 | | 69306030 | 2 | | 69306290 | 1 | | 69306620 | 1 | | 69307020 | 1 | | 69307040 | 1 | | 69307170 | 1 | | ITAPETININGA | 1 | | R | 1 | | RAUL MOREIRA OLIVEIRA | 1 | | VL DALILA | 1 | | ¥j§„«m¦‚£l¢€—m‰|ˆhˆ|~_žƒ²[—†Œd“‰ƒjtŒ ¤më‡úrøøtö{õlò{òdõzómåwärÏq¨x‘sœ‚³xµ€³y¿v fis-> fis-> fis->
The statement was: select cep, count(0) from telpbxt group by cep;
Please, be noticed the query are showing wrong data. The first column is ZIP Code (only numbers) and it's showing city name, customer name and bourough... These columns are not referenced in this simple query.
Another weird things is: the query just stopped without error nor presenting the time, rows, etc.
Ooops, the table structure is:
CREATE TABLE `telpbxt` ( `cpf` bigint(20) DEFAULT NULL, `nome` tinytext, `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 ) ENGINE=PBXT DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=94
I have 10.3 mio rows. I know I know this varchar(100) sucks... a lot!
Thanks for you help.
Alex
<alexandre(jpg).jpg>
Em 06/05/2010, às 03:32, Paul McCullagh escreveu:
Hi Monty,
On May 5, 2010, at 7:02 PM, Michael Widenius wrote:
>> "Paul" == Paul McCullagh <paul.mccullagh@primebase.org> writes:
<cut>
Paul> PBXT can help you out with this. Do the following:
Paul> (1) Load the telpbxt table with a few 1000 rows (or use telpbxt2, Paul> since telpbxt already has too many rows in it). Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this Paul> on a table with millions of rows. It will take very long to complete). Paul> (3) Check the MySQL error log, you should see something like the Paul> output below.
Paul> CHECK TABLE: ./test/telpbxt Paul> Record buffer size = 1024 Paul> Fixed length rec. len. = 1020 Paul> Handle data record size = 638 Paul> Min/max header size = 14/26 Paul> Min/avg/max record size = 19/624/1019 Paul> Avg row len set for tab = not specified Paul> Rows fixed length = NO Paul> Maximum fixed size = 16384 Paul> ...
Paul> Paste the output in an e-mail, and I will show you what to use for the Paul> AVG_ROW_LENGTH.
Would it be hard for PBXT to as part of the CHECK TABLE add:
Optimial create AVG_ROW_LENGTH: ?
Currently CHECK TABLE prints:
Average comp. rec. len. = 200
This is the closest to your suggestion of an "Optimal create AVG_ROW_LENGTH", there is at the moment.
But, this value is not necessarily optimal.
Exactly what is optimal depends on a few additional factors, like whether you want to optimize for size or caching behavior.
What I can do is add more information to help in this decision making process, and output a "Recommended AVG_ROW_LENGTH".
Then I can change OPTIMIZE TABLE to automatically set this value, if no explicit AVG_ROW_LENGTH has been set yet.
Best regards,
Paul
-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com
participants (4)
-
Alexandre Almeida -
Arjen Lentz -
Michael Widenius -
Paul McCullagh