Hell guys,

I have a logserver which need access the database to show me
informations on the screen. The problem is that it is slow because the
table is very big (around 25.000.000 of rows). When I do a search by
software it takes a long time to return me the result (around 3 or 5
minutes). The Mysql is in a virtual server and the Virtual Machine and
has 6GB of ram, /var/lib/mysql is in a different partition of S.O
(with ext3) and the disks of my storage are 15k. I don't have raid in
this server. I would like to know how I can improve the time of
querys. My my.conf is this:


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
expire_logs_days = 2
back_log = 50
max_connections = 20
max_connect_errors = 10
table_cache = 256
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 384M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 1M
ft_min_word_len = 4
default_table_type = InnoDB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 384M
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 512M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-federated
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 3G
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192


[]'s
Renato

On 12/29/2011 6:08 AM, renator wrote:
To start with, have you EXPLAINed your query?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

renator wrote:
<snip>

What is the data and what is the query?

indexing can make a huge difference IF you are selecting a subset based
on a field.

On 29 dez, 14:43, Michael Vilain <vil... (AT) NOspamcop (DOT) net> wrote:
Haaa, all index of all tables this database are the Primary Keys.
Doesn't have other index, just the Primary Keys.

[]'s
Renato

On 12/30/2011 10:47 AM, renator wrote:
Once again - did you EXPLAIN your query? What is the query and what is
the output from EXPLAIN?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 30 dez, 13:49, Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:
Hi Jerry,

I didn't find the query at the moment of the select happening. This
software wich I using wasn't writed by me or my company.

[]'s
Renato

On 12/30/2011 10:54 AM, renator wrote:
Then I would suggest you contact whomever wrote it. Without knowing the
query, ANYTHING is a guess.

Sure, you can randomly add indexes - but every index you add will slow
down INSERT and DELETE statements, as well as UPDATE statements which
affect indexed columns.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

renator <renato.rudnicki (AT) gmail (DOT) com> wrote:

[general query log snipped]

This is nothing that you normally want to expose. Much more interesting
would be the slow query log because it contains (by definition!) only
the slow queries along with information about execution time, number of
scanned rows etc.

If you find your problem query there, run it through EXPLAIN and show
us the result. Also add SHOW CREATE TABLE and SHOW INDEX FROM TABLE for
all tables involved by the query.


XL

On 30 dez 2011, 21:10, Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:
Hi guys and happy new year :-)

I would like to thanks for help. I got "solved" the problem change
some parameters on my.cnf and create some indexes.

Regards,
Renato Rudnicki