MySQL with bigdatabase

[northwest]

Hi,
I have this table:

Code: Select all

CREATE TABLE IF NOT EXISTS `cms_kategorie` (
  `bf_id` bigint(20) unsigned NOT NULL,
  `tytul` varchar(85) COLLATE utf8_unicode_ci DEFAULT NULL,
  `domena_url` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `enable` int(11) NOT NULL DEFAULT '0',
  `level` int(11) NOT NULL DEFAULT '0',
  `parent_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indeksy dla zrzutów tabel
--

--
-- Indexes for table `cms_kategorie`
--
ALTER TABLE `cms_kategorie`
  ADD PRIMARY KEY (`bf_id`),
  ADD UNIQUE KEY `bf_id` (`bf_id`),
  ADD KEY `bf_id_2` (`bf_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT dla tabeli `cms_kategorie`
--
ALTER TABLE `cms_kategorie`
  MODIFY `bf_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

MyServer is http://www.online.net/en/dedicated-server/dedibox-xc - with hdd sata3
This table has 1.5GB.


My /my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=600
max_user_connections=400
wait_timeout=30
interactive_timeout=50
long_query_time=5
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log

#innodb_use_native_aio = 0
innodb_file_per_table

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

thread_cache_size = 256
table_cache = 1024
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=64M


Unfortunately, web pages load me for a long time. Is it possible to somehow speed up?

[tjebbeke]

Are you sure it's mysql problem? Not network, ...? Are you using complex query's? Is there disk latency? You can try to tune your mysql with mysqltuner.com

My vps has less resources with larger database (+ 3GB data, several million records). And everything works fine :)

[northwest]

serwer is okey (hardware). It's nowe machine.


Maybe you see error i my mysql table?

[northwest]

can you show me your's my.cnf??

[tjebbeke]

my my.cnf optimized with mysqltuner

Code: Select all

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=500
max_user_connections=100
wait_timeout=30
interactive_timeout=50
long_query_time=5
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log

#innodb_use_native_aio = 0
innodb_file_per_table 
query_cache_size=8M
join_buffer_size=128K
tmp_table_size=16M
max_heap_table_size=16M
innodb_buffer_pool_size=768M


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[northwest]

thank you