Hi,
I compiled from source on Ubuntu server 64 bit, the new 5.1.18, using the
my.cnf from 5.1.17 to run it.
In the error log file now I see the following warnings:
070516 10:51:08 [Warning] Server variable data_file_path of plugin InnoDB was
forced to be read-only: string variable without upda
Dear all,
Two questions about server variables tuning.
1) key_buffer_size stores MyISAM indexes; innodb_buffer_pool_size
stores InnoDB indexes and rows. So the innoDB buffer would need more
space in general. It sounds like a good idea, if the number and size
of MyISAM and InnoDB tables is
Based on the size (and # of colums) of your result set, I'm not sure
any amount of RAM would allow 360,000 records to be stored and sorted
entirely in memory. After some point, mysql just decideds to use a
temp table.
That's the limit of my tweaking skills, so someone else is going to
have to hel
Thanks James. I'll definately give that a try. My test server has 1 gig of
ram. In the my-huge.cnf example it says that it's mainly for servers that
have mysql as the main process. On my production server, I have 1 gig of ram,
but it also runs apache, mutliple webstites, mysql, DNS, ftp
The reason I suggested that you just use the alternative my-huge.cnf file is
because that is a ready-prepared config file optimised for systems with lots of
RAM for MySQL to use. You don't need to know which variable to change - it's
already done for you. You may want/need to tweak stuff later,
Thanks Hank. I'll try to split up the query into 2 separate queries. I think
that the reason the original query is so slow is that I don't have enough RAM
allocated to mysql. When the original query takes place, I see a process
"Copying to tmp table on disk". I believe it's writing all t
> Now that I know what's causing the slow queries, what can I do to fix it?
The only thing I can suggest is breaking up the query into two parts -
the first part to retrieve just the product codes and salesrank, and
sort and limit that.. save in a temp table or use application code to
retrieve and
OK,
I tried the following queries:
1. Use the ORDER BY and not the LIMIT <-- still slow
2. Use the LIMIT and not the ORDER BY <-- fast
So it looks like it's building a whole temp table with all the items and then
doing the ORDER BY.
Just as a test, I removed the INDEX fr
It'll be a few hours before I get home and can do this query with the
variations you mentioned. I don't know if I need the separate index key on
`salesrank` or not. I thought it would speed up my query be having it.
In the previous email I included the results from doing an EXPLAIN. It
Ok, so the next step would be to try the original query with just the
LIMIT clause, and then just the ORDER BY (but not both).
The results of "select count(*)" query would be helpful to know just
how many records mysql is trying to sort and limit.
And do you really need a separte index key on `sa
OK guys,
Here is are my tables and test SELECT queries. Before every query I did a
FLUSH TABLES command. Before starting these test, I did and ANALYIZE and
OPTIMIZE on all the tables. Hopefully the formatting of this email isn't too
bad.
product table:
show create table pn
Hank <[EMAIL PROTECTED]> wrote on 12/21/2005 09:39:50 AM:
> Grant,
>
> You can just to a "desc pn_pricecompare_catprod" and "desc
> pn_pricecompare_product" and post the results. The CREATE TABLE
> statements would be OK, but the describes are better.
>
> The flush the query cache, I think if
Grant,
You can just to a "desc pn_pricecompare_catprod" and "desc
pn_pricecompare_product" and post the results. The CREATE TABLE
statements would be OK, but the describes are better.
The flush the query cache, I think if you do a "flush tables".
-Hank
--
MySQL General Mailing List
For list
That's why I suggested simply using the supplied my-huge.cnf config file,
because I assume it was contructed by some MySQL uber-guru who does know how
much RAM to allocate to each variable!
James Harvard
> Like I mentioned before, I am tweaking the .cnf files by blind trial and
> error. I w
Thank Hank. I will try this. When you say the table descriptions, do you
mean for me to post my CREATE TABLE syntax of how I created the table?
Thanks,
Grant
Hank <[EMAIL PROTECTED]> wrote: I don't think the problem is going to be
solved with the my.cnf file.
Here's what I would try
I don't think the problem is going to be solved with the my.cnf file.
Here's what I would try..
1- run and time the original query without the ORDER BY or LIMIT clauses
2- run and time the following breakdown queries, to see if the
indexes are at least working correctly:
-- test catprod
SELECT
r variable.
Here is a link to my current server variables:
http://retailretreat.com/mysql/server_variables.php.htm
Here is a link to my current my.cnf file:
http://retailretreat.com/mysql/my.cnf.txt
Here is a link to my proposed my.cnf file. I'm not sure if there are
any errors
I've recently been doing some big table query optimisation, but after getting
the query tweaked to hit the right index the query time is under very
livable-with, despite the fact that MySQL seems to be examining more rows for
my query than for yours. However the 'rows' column of thhe explain out
er than
trial and error, I really don't know what I'm doing.
Here is a link to my current server variables:
http://retailretreat.com/mysql/server_variables.php.htm
Here is a link to my current my.cnf file:
http://retailretreat.com/mysql/my.cnf.txt
Here is a link to
LIKE '%...%', most of them also having an ORDER BY. Both
MyISAM and InnoDB tables. What would be the best server variables to
tune in that case?
I can think of these: sort_buffer, tmp_table_size, perhaps
read_rnd_buffer_size? Do these make sense? Any other ideas?
Thanks,
Jan Pieter Kunst
--
MySQL
Greetings,
Suppose I have a MySQL server which processes lots of queries that
cannot use indexes on VARCHAR fields because they involve REGEXP
clauses and LIKE '%...%', most of them also having an ORDER BY. Both
MyISAM and InnoDB tables. What would be the best server variables to
tu
hello list,
i have high performance dell server. i want to
increase Read_Buffer_Size server variable to 5 MB.
i have found docs on this in mysql site. like this
mysqld_safe --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M
&
where i type that
This may be a PHP rather than MySQL issue
I'm using 4.0.13 on Windows XP and Apache 1.3.
My phpinfo() shows (among others) QUERY_STRING and REQUEST_URI in the
Apache environment and _SERVER["QUERY_STRING"] with _SERVER["REQUEST_URI"]
under the PHP variables.
On my ISP, who is running W2K/I
hello,
is it possible to set server varibales through SQL and if so
what user-rights do i need?
thanx in advance
regards peet
24 matches
Mail list logo
